11

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.

Smandoli
  • 6,919
  • 3
  • 49
  • 83
Mike
  • 232
  • 2
  • 3
  • 10
  • 1
    "migrated our Access DB backend to SQL Server". My suggestion would now be to migrate from DAO to ADO recordsets, if you require queries in the familiar view then passthroughs are great, but if only needed in code then ADO is the way to go : ) – Matt Donnan Jun 21 '13 at 18:55
  • That's the eventual plan. Unfortunatly I'm in an environment where I'm the sole technical person managing a DB built by someone else 4 years ago and ramping up as I go. One step at a time, but the plan is to transition to ADO.... eventually! – Mike Jun 21 '13 at 19:28

3 Answers3

23

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Worked perfectly, thanks! I was missing the need for a QueryDef in order to work this as a passthrough! – Mike Jun 21 '13 at 19:26
0

Gord, could you provide the mods for the code to execute a proc with passed parameters and an insert statement? I've been working with it, but haven't been able to beat it. I have the base code working, so I'm talking to the server correctly, I'm just not getting the OpenRecordset correct.

Basically, I'm doing a three-step process: log the start of executing the SP, execute the SP, log the end. These are my SQL strings:

    beginsql = " INSERT INTO [dbo_zSprocExecuteLog]" _
        & " values (sysdatetime(), 'uspGradePromotionDistrict', " _
        & [SeekerUserID] & ", 'B', '(" & [ReportDistrict] & ")';"

    sqlstring = " EXEC uspGradePromotionDistrict" _
        & " @District = '" & [ReportDistrict] & "';"

    endsql = " INSERT INTO [dbo_zSprocExecuteLog]" _
        & " values (sysdatetime(), 'uspGradePromotionDistrict', " _
        & [SeekerUserID] & ", 'E', '(" & [ReportDistrict] & ")';"

I'd appreciate your input, this has been driving me nuts. And can you recommend a good book on Access/SQL programming? I've got gobs of experience on the SQL Server side, my Access Fu is a little weak.

SQLWayne
  • 33
  • 1
  • 6
  • For a simple input, then you don't need a pass-though. For the exec example, see my post here: http://stackoverflow.com/questions/28727560/use-function-call-in-passthrough-query/28735619#28735619 – Albert D. Kallal Apr 09 '15 at 23:30
0

You cannot do an INSERT INTO a local table with a passthrough query. Use the resultant query (that you generated with Querydef) as the source for the INSERT.

Bonnie
  • 1