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.
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
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
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