I have just started working with an Access 97 front end and SQL Server 7.0 back end. I have started to test out retreiving data from SQL Server and I've run into a problem. Here is a sample of my code:
strConnect = "ODBC;DSN=TestData;SERVER=abc;UID=123;PWD=456;"
Set dbsCurrent = CurrentDb
Set qdfSearchTable = dbsCurrent.CreateQueryDef("")
With qdfSearchTable
.Connect = strConnect
.SQL = "SELECT ObjectName, ObjectID FROM tblObject WHERE ParentID = NULL"
Set rstSearchTable = .OpenRecordset()
rstSearchTable.MoveFirst
...
...
End With
When the .MoveFirst line is executed I get a "No Current Record" error message.
I have tested my connect string by running a a make table query which executed properly and the new table appeared in the SQL Server database. What am I doing wrong?
strConnect = "ODBC;DSN=TestData;SERVER=abc;UID=123;PWD=456;"
Set dbsCurrent = CurrentDb
Set qdfSearchTable = dbsCurrent.CreateQueryDef("")
With qdfSearchTable
.Connect = strConnect
.SQL = "SELECT ObjectName, ObjectID FROM tblObject WHERE ParentID = NULL"
Set rstSearchTable = .OpenRecordset()
rstSearchTable.MoveFirst
...
...
End With
When the .MoveFirst line is executed I get a "No Current Record" error message.
I have tested my connect string by running a a make table query which executed properly and the new table appeared in the SQL Server database. What am I doing wrong?