OpenRecordset fails on ODBC Linked table (SQL Server) (1 Viewer)

nuentes

New member
Local time
Today, 03:24
Joined
Dec 5, 2012
Messages
8
I'm having trouble opening recordsets with my linked tables. When everything was stored locally, all my queries worked fine. The problem arose when I migrated the back end to a SQL server. When I try to run the following, the code just kind of dead ends at the OpenRecordset line. I don't get an error message, and any code that follows does not run.

Code:
Sub expireRefs()
    Dim findExp As Recordset
    Set findExp = CurrentDb.OpenRecordset("SELECT * FROM RefGivenBatch WHERE Active=True and date()>DateAdd('m',60,CalDate)", dbOpenDynaset)
    With findExp
        MsgBox ""
        .Close
    End With
End Sub
Other iterations I've tried include:
CurrentDb.OpenRecordset("RefGivenBatch")
CurrentDb.OpenRecordset("dbo.RefGivenBatch")
CurrentDb.OpenRecordset("RefGivenBatch", dbOpenDynaset)
CurrentDb.OpenRecordset("dbo.RefGivenBatch", dbOpenDynaset)
CurrentDb.OpenRecordset("SELECT * FROM RefGivenBatch WHERE Active=-1 and getdate()>DateAdd(m,60,CalDate)", dbOpenDynaset)
CurrentDb.OpenRecordset("SELECT * FROM RefGivenBatch WHERE Active=-1 and getdate()>DateAdd(m,60,dbo.RefGivenBatch.CalDate)", dbOpenDynaset)

So I clearly have no idea what the problem is. The one in code breaks is what was working when the table was stored locally. It also worked when it was split to an mdb file. I am able to open the table and view the data manually. It's also worth mentioning that this is occurring with all of my openrecordsets, and is not limited to a single case.

Thanks in advance for any help
 

mdlueck

Sr. Application Developer
Local time
Today, 06:24
Joined
Jun 23, 2011
Messages
2,631
Misc points:

1) You should have posted to the SQL group, not Macros. Perhaps some moderator will move this thread.

2) You do not show code related to the Access --> SQL connection. I believe the way you are sending the SQL, it will be run in the context of Access, not Pass-Through to the SQL BE DB.

Here is a post that will hopefully help:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5
 

nuentes

New member
Local time
Today, 03:24
Joined
Dec 5, 2012
Messages
8
Ok, thanks for the reply. Sorry if this was in the wrong place, but I was working with VBA, so I figured Macros would be the right place.

You were correct. The where statement does need to be formatted for access, not SQL server. So that was half the problem.

The other half was discovering that, for some reason, I now need to open the database first (CurrentDb doesn't work for my linked tables)

Code:
Dim test As Database
Dim findExp As Recordset
Set test = OpenDatabase("C:\filename.accdb")

Thanks for your help!
 

nuentes

New member
Local time
Today, 03:24
Joined
Dec 5, 2012
Messages
8
Awesome. So this means I wont have to go through and add a line to every instance where I used CurrentDb.

Is this a run-once solution, or does it need to be run on DB open?

Thanks
 

mdlueck

Sr. Application Developer
Local time
Today, 06:24
Joined
Jun 23, 2011
Messages
2,631
I updated the referenced code to utilize Late Bindings and to NOT require the ADOX reference to be enabled any longer.

So this means I wont have to go through and add a line to every instance where I used CurrentDb.

With this code, once run, you do not need to authenticate when you utilize Linked Table objects. This ADOX code updates all of the Linked Tables to utilize the prepared ODBC connection string. You must have prepared that on your own for this code snippet to work.

Is this a run-once solution, or does it need to be run on DB open?

Must be run each time you start your DB application.
 

Users who are viewing this thread

Top Bottom