Recordsets of SQL Server Data

glynch

Registered User.
Local time
Today, 10:40
Joined
Dec 20, 2001
Messages
128
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?
 
The syntax of your SQL statement is incorrect so you are not retrieving any records -

.SQL = "SELECT ObjectName, ObjectID FROM tblObject WHERE ParentID Is NULL;"

You should also test for EOF before executing any Move method.

BTW it is more efficient to create stored querydefs rather than building them in code. You should only build them in code if there is no way to parameterize them.
 
Thanks Pat, although I'm not seeing where the syntax is incorrect, it runs when inserted as the SQL directly into a query. I know that syntax can be different between queries and code but that is usually the case where variables are included.

Another thing that I discovered is that I needed to check the field properties in my SQL Server tables very carefully before trying to interact with them from Access. The DTS process doesn't automatically carry over all of the properties from Access. I was getting hammered by some constraint violations but I was only getting an error message of "ODBC call failed".
 
Pat,

I have a question regarding your BTW from your last response. I am updating or appending records into a table (tblXXX) based on values from a recordset (rstZZZ) on another table. I step through the recordset and with each record in rstZZZ I append a value into tblXXX based on the value from the current record in rstZZZ.

Should I have a named query when declaring my querydef and add a customized where clause in my loop or is the inefficiency of that equal to the inefficiency of restating the querydef.SQL each time.

Thanks for any advice you can offer. I have just dicovered this site after years of working with Access at different times, I've learned a lot from searching through old topics. Hopefully I can help out some others as well.
 
Regarding the syntax - "Is Null" is the standard syntax. Apparently "= Null" also works. Since it is not the recommended syntax, I would avoid using it even though it works. It may not work with the next version of Access.

Querydefs that take parameters are more efficient than SQL created "on the fly". One way to pass a parameter to a querydef is to use a form that contains an unbound field to hold the value. Say that your form name is "MyForm" and the control name is "MyControl". Then the SQL would be:

Select ...
From ...
Where SomeField = Forms!MyForm!MyControl;

Using this method you can supply a value for SomeField when the query runs.

A lot of processing goes on behind the scenes when you create and save a querydef. Access needs to parse the SQL and calculate a method to retrieve the data requested. This access plan is calculated and stored when the querydef is saved. It is then used whenever the query is executed. When you create SQL in code, Access needs to go through the parsing and access plan calculation EACH time the query is executed rather than only once if it is stored as a querydef. You probably won't notice any performance delays unless you need to execute the same SQL statement many times during the execution of a process. However, this also contributes to database bloat which can be more problematic. The workspace that Access takes to do this "calculation" is not recovered until the db is compacted. So each time Access needs to calculate an access plan for a query, some bloat is added to your db. Therefore, stored querydefs reduce the need for frequent compacting.
 

Users who are viewing this thread

Back
Top Bottom