Passing CURRENT_TIMESTAMP through from an Access 2007 query?

[mdlueck]

I assume the fight that Access 2007 is giving me is related to Access not recognizing what CURRENT_TIMESTAMP is.

I have the following code which works in SQL Server Management Studio:

INSERT INTO projects ( authid, logtimestamp, title )
  VALUES (4, CURRENT_TIMESTAMP, 'This is a way cool test!');

When I try to run the same from an Access query, I get a popup prompt to provide CURRENT_TIMESTAMP.

I was hoping to simply capture the current server timestamp for logging and not rely on workstations having the correct date/time.

Suggestions on how to get CURRENT_TIMESTAMP to work from Access?

My connection to SQL Server is via the ODBC driver. I have the SQL Server Management Studio installed as well on my workstation, thus comparing results.

Thanks!

 

[mdlueck]

Aaahhh!!! I found an interactive way to make it work from Access: Pushing the "Pass-Through" button.

So I guess next to research how to define my programmed (VBA ADO) queries to be run in "Pass-Through" mode.

 

[Vassago]

Pass-through essentially sends the query to whatever system/data storage you are using and requests the results back. It's a very interactive way to recieve data better than using linked tables since they don't have to be converted for a query to run. You'll have fun with those.

 

[mdlueck]

I seem to be having some difficulity finding the correct way to specify passthrough to the ADO object.

I came across this document:

"How to use ADOX to create an SQL pass-through query in Access"
http://support.microsoft.com/kb/304323

Verified that indeed the attribute is a part of the ADORecordset object, so added the one line of code. However the query still insisted on the Access name of the table rather than the real name on the remote server.

The pass-through in the GUI query insisted I switch to the server table name. Thus I doubt pass-through took affect in my VBA code query.

Perhaps do I need to set more of the parameters? There were like 60 parameters... I did not look through all of them to see what juicy sounding titles/names I come across.

 

[mdlueck]

Turns out that there are two VBA ADO Properties which need to get set in order to support Pass-Through queries. They are as follows:

  rs.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
  rs.Properties("Jet OLEDB:Pass Through Query Connect String") = ObjAppSettings.getODBCConnectString

The second property, I built a class to look up the connect string arguments from the Windows Registry (in a key for the application I am developing), assemble them together into a valid connect string. The connect string Rx is as follows:

Public Function getODBCConnectString()
  getODBCConnectString = "ODBC;DSN=" + dbDSN + ";UID=" + dbUID + ";PWD=" + dbPWD + ";"
End Function