We've recently created and migrated our Access DB backend to SQL Server. I'm trying to, using VBA code, create a connection to the SQL Server backend and run a passthrough query with the results stored in a VB recordset. When I try this, the query is NOT passing through.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strConnect As String
strConnect = "DRIVER=SQL Server;SERVER=55.55.55.55 SQLExpress;UID=UserName;PWD=Password"
Set db = OpenDatabase("DBName", dbDriverNoPrompt, True, strConnect)
Set rs = db.OpenRecordset("SELECT GetDate() AS qryTest", dbOpenDynaset)
MsgBox rs!qryTest
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
The problem I'm getting is that the totally appropriate GetDate()
SQL Server function is returning Runtime Error 3085 "User Defined Function 'GetDate' in expression". If I create this same query as a passthrough in MS-Access Query Builder, outside of VBA code, it runs fine and returns the server date and time, only in code is it not passing through properly.
SQL pass-through queries are used to send commands directly to an ODBC database server. By using an SQL pass-through query, you work directly with the server tables instead of having the Microsoft Jet database engine process the data.
A dynaset-type Recordset object is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset-type Recordset object can contain fields from one or more tables in a database.
Sometimes you might want to list the records from one table or query with those from one or more other tables to form one set of records - a list with all the records from the two or more tables. This is the purpose of a union query in Access.
You need to use a QueryDef
object to create a Pass-Through query, then open the Recordset via the .OpenRecordset
method of the QueryDef. The following code works for me:
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC;Driver=SQL Server;Server=.\SQLEXPRESS;Trusted_Connection=Yes;"
qdf.SQL = "SELECT GetDate() AS qryTest"
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset
Debug.Print rst!qryTest
rst.Close
Set rst = Nothing
Set qdf = Nothing
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With