Pass-through SQL error :/

[namliam]

You cannot change it into an append query, because that is Access native only...

Keep the Pass through but make it Action query:
Query>Query Specific> Pass through
Now right click yuor title bar of the query (while still in design mode)
Find the property "Return records" which should be set to Yes and change it to No...

Now you have changed it to a PT-Action query which can be run Using either RunSQL or Currentdb.Execute.

 

[twoplustwo]

Hi Mailman,

I feel like an idiot but...

I worked through your steps...but it didn't return any records and thus wouldn't append to the table.

Using this: "CurrentDb.Execute "INSERT INTO tblFinalVolumes SELECT * FROM rsFinalVolumes"" works fine, but I'm still not sure on how to pass a variable to this.

I hope I'm not being too dense. I really do appreciate your time.

 

[namliam]

Taken from your earlier code:
INSERT INTO tblInitialVolumes
FROM FSD_SETTLEMENT_DETAILS

I get that you are trying to fetch data out of Oracle, into access... Correct?

If our Currentdb.Execute thing works... It is simply an Insert statement you are executing. You can manipulate the Insert into statement exactly the same as you were doing before... Should hardly be a chalange for you

 

[twoplustwo]

Ah that makes sense.

I assume I need to remove the date fields from my pass through query then build the statement in a similar fashion to before...

 

[namliam]

Something like that.

 

[twoplustwo]

Morning Mailman

I've tried the below method and got an error in the FROM clause:

pt query:

SELECT SETTLEMENT_DATE, CONSUMPTION01 AS P1, CONSUMPTION02 AS P2
      FROM FSD_SETTLEMENT_DETAILS

Manipulation of INSERT INTO query:

CurrentDb.Execute "INSERT INTO tblFinalVolumes SELECT * FROM rsFinalVolumes " & _
                  "where SETTLEMENT_DATE between TO_DATE('" & dtDateFrom & " 00:00:00 ', 'DD MON YYYY HH24:MI:SS') " & _
                  "and TO_DATE('" & dtDateTo & " 23:59:59', 'DD MON YYYY HH24:MI:SS') "

The passthrough/code runs fine in the database when the dates are hardcoded into the query

 

[namliam]

The PT query works right??

I notice that you have dtDateFrom and dtDateTo, those appear to be date formats and will display/format the date US format not the format you are using in the to_date.

Try this as the insert into:

CurrentDb.Execute "INSERT INTO tblFinalVolumes SELECT * FROM rsFinalVolumes " & _
                  "where SETTLEMENT_DATE between TO_DATE('" & Format(dtDateFrom,"YYYYMMDD") & " 00:00:00 ', 'YYYYMMDD HH24:MI:SS') " & _
                  "and TO_DATE('" & Format(dtDateTo,"YYYYMMDD") & " 23:59:59', 'YYYYMMDD HH24:MI:SS') "

 

[twoplustwo]

Ya the SQL I posted in post #26 works fine.

It breaks on the to_date stating: "Undefined function <name> in expression. (Error 3085)
You entered an SQL expression that includes a Function procedure name that cannot be recognized. Make sure the function exists, that it can be used in SQL expressions, or check the expression to make sure you entered the name correctly."

I'm not sure if Access is trying to execute the to_date rather than the Oracle back end? Sorry if this is frustrating Mailman.

 

[namliam]

I'm not sure if Access is trying to execute the to_date rather than the Oracle back end? Sorry if this is frustrating Mailman.

You hit the nail on the head...
Currentdb.Execute runs the query in Access not in Oracle, I totaly overlooked that.
The ODBC will translate it for you.

Try this:

CurrentDb.Execute "INSERT INTO tblFinalVolumes "  & _
                  "SELECT * FROM rsFinalVolumes " & _
                  "where SETTLEMENT_DATE between #" & Format(dtDateFrom,"mm/dd/yyyy") & " 00:00:00# " & _
                                            "and #" & Format(dtDateTo  ,"mm/dd/yyyy") & " 23:59:59# "

Disclaimer:
Sorry I am not at my best/sharpest edge today.

 

[twoplustwo]

Ah we seem to be working

Just a question if I may.

Will the initial pt query bring everything back locally first or will the date filter set in the VBA filter the data on the Oracle server?

I ask because we removed the dates from our pt query so they can be set in the code.

 

[namliam]

That will depend on the logic of the ODBC, I am not sure... but I think the date filter should be send to Oracle..

Or to make sure you can try something like this (going full circle ), assuming your PT query is called rsFinalVolumes:

Currentdb.Querydefs("rsFinalVolumes").sql = "SELECT SETTLEMENT_DATE, CONSUMPTION01 AS P1, CONSUMPTION02 AS P2 " & _
"FROM FSD_SETTLEMENT_DETAILS " & _
"where SETTLEMENT_DATE between TO_DATE('" & Format(dtDateFrom,"YYYYMMDD") & " 00:00:00 ', 'YYYYMMDD HH24:MI:SS') " & _
                  "and TO_DATE('" & Format(dtDateTo,"YYYYMMDD") & " 23:59:59', 'YYYYMMDD HH24:MI:SS') "
CurrentDb.Execute "INSERT INTO tblFinalVolumes "  & _
                  "SELECT * FROM rsFinalVolumes "

 

[twoplustwo]

That is sooooooo sick.

Literally, exactly exactly what I was trying to do.

And it's so quick.

Thanks Mailman, I owe you (another!) one.

 

[namliam]

ruoY yppah namliaM si syawla yppah ot pleh

I hope you indent it tho... cause I didnt