Pass-through SQL error :/

[twoplustwo]

Hi guys,

I'm looking to run this pass through query and update a table with the values.

strSQL = "INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02)"
strSQL = strSQL & "SELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE, FSD_SETTLEMENT_DETAILS.CONSUMPTION01, FSD_SETTLEMENT_DETAILS.CONSUMPTION02 "
strSQL = strSQL & "FROM FSD_SETTLEMENT_DETAILS"
strSQL = strSQL & "where SETTLEMENT_DATE between TO_DATE('" & dtDateFrom & " 00:00:00 ', 'DD MM YYYY HH24:MI:SS') "
strSQL = strSQL & "and TO_DATE('" & dtDateTo & " 23:59:59', 'DD MM YYYY HH24:MI:SS') "
'strSQL = strSQL & "GROUP BY SETTLEMENT_DATE"
Debug.Print strSQL
DoCmd.RunSQL strSQL

I keep getting an error on the FROM clause.

Any ideas?

 

[namliam]

What does the Debug.print say??

I bet the problem is here:
FROM FSD_SETTLEMENT_DETAILSwhere SETTLEMENT_DATE

Notice the missing space.
For this specific reason I allways put a space on the beginning of the line as well as the end. That way I never run into this problem of forgetting a space by accedent...
if I do I quickly notice, because the beginning of the lines are out of synch...

For this same reason I would advice you to use something like so:

strSQL = "" 
strSQL = strSQL & "INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02)"
strSQL = strSQL & "SELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE, FSD_SETTLEMENT_DETAILS.CONSUMPTION01, FSD_SETTLEMENT_DETAILS.CONSUMPTION02 "
strSQL = strSQL & "FROM FSD_SETTLEMENT_DETAILS"

This lines up your SQL to be much more readable (IMHO)

 

[twoplustwo]

Hi Mailman.

Solid advice will def remember that in the future.

It's now saying that it can't find the input table FSD_SETTLEMENT_DETAILS and breaks on the RunSQL.

INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02)SELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE, FSD_SETTLEMENT_DETAILS.CONSUMPTION01, FSD_SETTLEMENT_DETAILS.CONSUMPTION02 FROM FSD_SETTLEMENT_DETAILS where SETTLEMENT_DATE between TO_DATE('01/07/2008 00:00:00 ', 'DD MM YYYY HH24:MI:SS') and TO_DATE('01/07/2008 23:59:59', 'DD MM YYYY HH24:MI:SS')

Above is the print.

 

[namliam]

Well that means that either:
1) the table doesnt excist
2) you dont have the rights to the table
3) You have to append the username or something to the table
4) RunSQL executes the query INSIDE your DB.
You are talking about PassThrough query, but that is for executing a query in another DB ( most likely Oracle or SQL Server ) In which case you cannot use RunSQL, but have to build a connection and use DAO or ADO to do so. You will also need user/PW to access said DB and have the privileges (see 2) to access the table.

 

[DJkarl]

Is this by chance an Oracle server you're trying to query?

If so try changing FSD_SETTLEMENT_DETAILS to FSD.SETTLEMENT_DETAILS

 

[twoplustwo]

Hi Nam,

I think I've set a connection to the relevant database.

Below is the entire code.

This is the first time I've ever done something like this :/

Thanks for helping.

Private Sub cmdImportVolumes_Click()
Dim adoConnectRDPS As ADODB.Connection
Dim dtDateFrom As Date
Dim dtDateTo As Date
Dim strConnectionString As String
Dim strMsg As String
Dim strSQL As String
 
'RDPS connection
strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=pinzon_rdps01;Uid=password;Pwd=password;"
Set adoConnectRDPS = New ADODB.Connection
adoConnectRDPS.Open strConnectionString
dtDateFrom = txtDateFrom
dtDateTo = txtDateTo

strSQL = "DELETE * from tblInitialVolumes "
DoCmd.SetWarnings False
Debug.Print strSQL
DoCmd.RunSQL strSQL
 
strSQL = ""
strSQL = strSQL & "INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02)"
strSQL = strSQL & "SELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE, FSD_SETTLEMENT_DETAILS.CONSUMPTION01, FSD_SETTLEMENT_DETAILS.CONSUMPTION02 "
strSQL = strSQL & "FROM FSD_SETTLEMENT_DETAILS "
strSQL = strSQL & "where SETTLEMENT_DATE between TO_DATE('" & dtDateFrom & " 00:00:00 ', 'DD MM YYYY HH24:MI:SS') "
strSQL = strSQL & "and TO_DATE('" & dtDateTo & " 23:59:59', 'DD MM YYYY HH24:MI:SS') "
'strSQL = strSQL & "GROUP BY SETTLEMENT_DATE"
Debug.Print strSQL
DoCmd.RunSQL strSQL
 
End Sub

 

[namliam]

Look at your code, I have highlighted some parts which are "less than perfect"

INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02[B][U])S[/U][/B]ELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE, FSD_SETTLEMENT_DETAILS.CONSUMPTION01, FSD_SETTLEMENT_DETAILS.CONSUMPTION02 FROM FSD_SETTLEMENT_DETAILS where SETTLEMENT_DATE between TO_DATE('01/07/2008 00:00:0[B][U]0 '[/U][/B], 'D[B][U]D MM Y[/U][/B]YYY HH24:MI:SS') and TO_DATE('01/07/2008 23:59:59', 'D[B][U]D MM Y[/U][/B]YYY HH24:MI:SS')

Also please do make SQL readable.... Also in coding... Your SQL is barely readable IMHO....
I.e. Above code re-writen to "more readable" standards:

INSERT INTO tblInitialVolumes(SETTLEMENT_DATE, CONSUMPTION01, CONSUMPTION02[B][U])
S[/U][/B]ELECT FSD_SETTLEMENT_DETAILS.SETTLEMENT_DATE
,      FSD_SETTLEMENT_DETAILS.CONSUMPTION01
,      FSD_SETTLEMENT_DETAILS.CONSUMPTION02 
FROM   FSD_SETTLEMENT_DETAILS 
where  SETTLEMENT_DATE between TO_DATE('01/07/2008 00:00:0[B][U]0 '[/U][/B], 'D[B][U]D MM Y[/U][/B]YYY HH24:MI:SS') 
                           and TO_DATE('01/07/2008 23:59:59', 'D[B][U]D MM Y[/U][/B]YYY HH24:MI:SS')

This also makes life and debugging much easier if your (SQL) code is readable.
For this same reason we indent If then else and Do While... etc... So do it to SQL as well...

 

[LPurvis]

Mailman's just summarised the crux of your issue at the end there.
You mention Passthrough in the question title (though not again since?)

To be using RunSQL (never a good choice anyway IMO) the data objects must be local (native or linked). You could have been using an ADP to execute this - but "TO_DATE" is no T-SQL function that I know of.
So we therefore have to surmise that you're wanting to link to another DBMS.
Presumably you've already done this - in a passthrough itself perhaps?
Passthroughs accept no parameters - but it is absolutely standard to alter their SQL definition at runtime to facilitate your desired request.
CurrentDb.QueryDefs("YourPTName").SQL = strSQL

Equally entirely building PT's at runtime is common.
If you're wanting to append the results into a local table then a passthrough is probably your best option. For example a solution using a DAO or ADO connection end up with a recordset which you have to iteratively parse into the local table. Jet can perform the insert en masse for you from a passthrough.

 

[namliam]

strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=pinzon_rdps01;Uid=password;Pwd=password;"
Set adoConnectRDPS = New ADODB.Connection
adoConnectRDPS.Open strConnectionString

I am not to hot on ADO but....

This "Uid=password;Pwd=password" I presume you quickly edited out to post your code... And hope this is not "real"

Do you have a connection in your "TNSNAMES.ORA" file for pinzon_rdps01 ??
Can you log on to Oracle using Password/Password and SQL Plus?
Does the UID have rights to the table you try to query??

If all above is true... then you probably need to add your user name to the table:
UserName.TableName

Then again RunSQL Does definatly run in Access. If you want to run your ADO query & Connect you have to do something like ADO.Execute or something alike (again I am not well versed in ADO) to actually execute your query in the Oracle DB.

 

[twoplustwo]

Ha they're not real, no. I'm not that daft

LP,

"Presumably you've already done this - in a passthrough itself perhaps?"

Yes, that's right. I built in Access and wanted to allow variables to be inserted from a form so I thought it's be easy enough to build it in VBA passing date ranges to it. Obviously not.

This is all really helpful stuff guys, thanks. I think I might have bitten off more than I can chew :/

 

[LPurvis]

Yes, that's right. I built in Access and wanted to allow variables to be inserted from a form so I thought it's be easy enough to build it in VBA passing date ranges to it. Obviously not.

It's as simple as that one line of code I exampled.
Passthroughs don't accept parameters - so you change the entire definition.

 

[namliam]

Not to much... if you have an excisting pass-trough query that works....

Simply use the code provided by Leigh:
CurrentDb.QueryDefs("YourPTName").SQL = strSQL
To store your SQL in the excisting pass-trough query and then you can "pop it up" / Execute it using NOT your RunSQL but rather OpenQuery, this is still native to access, but then so is your query, so it should work:
DoCmd.OpenQuery "YourPTName"

Edit:
LOL
XPost with Leigh

 

[DJkarl]

I will say to double check the table names. Oracle allows periods in the table names but when you link them to Access they are automatically changed to underscores. If you are running a Passthru query the table name needs to have the name as it is on the Oracle server not how it is linked.

You can verify what the table name is on the server by trying to manually connect a table and look at it in the list, or through code.

I've posted a quick example on how to print a list of table names.

Function GetTblList() 
Dim conStr As String
Dim adCON As ADODB.Connection
Dim adRS As ADODB.Recordset
Dim db As Database
 
conStr = "YourConnectionString"
 
Set adCON = New ADODB.Connection
 
adCON.ConnectionString = conStr
adCON.Open
 
Set adRS = adCON.OpenSchema(adSchemaTables)

adRS.MoveFirst

Do Until adRS.EOF
   debug.print adRS.Fields.item("TABLE_NAME")
   adRS.MoveNext
Loop

Set adRS = Nothing
 
Set adCON = Nothing
 
End Function

 

[twoplustwo]

So this will pass the SQL in my working passthrough query in Access to the strSQL, right?

"DoCmd.OpenQuery "rsFinalVolumes"" works but it just opens the query in query view.

 

[namliam]

Yes...That is what openquery does... Open the query in query view...

Ah, but you have an insert query offcourse... Sorry my bad...

Then you have to change the type of your pass thru to Action pass thru (find that in the menu when you have the PT query open in design view)
Then use RunSQL like you were allready doing My Bad....

 

[LPurvis]

The other way around - you overwrite your PT's definition with the contents of strSQL.

It then depends entirely upon what you want to do.
You mentioned inserting the results into a table. Is that table in the local database (the current MDB)?

CurrentDb.Execute "INSERT INTO tblLocalTable SELECT * FROM YourPTName"

I'd suggest if this isn't clear you stop trying methods for now and clearly state your overall aim.

 

[LPurvis]

OpenQuery will effectively execute an action query (Update, Insert, Delete) - just as it displays a SELECT query as if opened from in the database window.
(As it's an Access method it can be used as a hack to not explicitly providing parameter values from Forms - rather than the more formal evaluation of QueryDef parameters).

We need to know what you have - and what youwant to be doing.

 

[twoplustwo]

Thanks guys.

This: "CurrentDb.Execute "INSERT INTO tblInitialVolumes SELECT * FROM rsFinalVolumes" works fine but only with the prefixed dates. So I'd need to pass parameters from a form to this statement or use the QueryDefs doofer.

Essentially I'm pulling stuff using a PT query in a big ass Oracle back end and storing the values in a table in my db.

I'd like to be able to set dates in the PT query but obv you can't use [Parameter] like in a Select query.

Thanks for this guys.

Really interesting/helpful stuff.

 

[namliam]

So ... do what you did... see my post #15

 

[twoplustwo]

Yes...That is what openquery does... Open the query in query view...

Ah, but you have an insert query offcourse... Sorry my bad...

Then you have to change the type of your pass thru to Action pass thru (find that in the menu when you have the PT query open in design view)
Then use RunSQL like you were allready doing My Bad....

Hi Mailman,

I just have this at the minute:

"CurrentDb.Execute "INSERT INTO tblInitialVolumes SELECT * FROM rsFinalVolumes""

When I went to change the PT query to an append query it cleared the entire SQL/Design viwe grid of my query.

Can it be built from there or not?

Still not sure on working with the dates yet.