Upsizing and DAO code

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

I was opening a query and reading it sequentially in my
old database, i.e., my .mdb. so, for example

Set dbs = CurrentDb
strSQL = a valid SQL select statement
Set tbl = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)

In my new .adp database, the table setting does not
work. I get the error message "object variable not
set". I have tried to rewrite this in ADO, but I have
never tried it before and can't get it right. Can anyone
supply me with the correct code in either DAO or ADO?

Thanks, Stuart
 
By default, in Access 2000 and above, the DAO reference is not
automatically included in a new database, only ADO. Initially, I recommend
removing the ADO reference, and adding the newest available DAO reference
(Tools -> References from the VB Editor). It is also a good idea to fully
qualify references in any DAO or ADO code, so it will work right even if
-both- references are checked, and regardless of which order the references
are in.

Example:
Dim rst As DAO.Recordset
....or...
Dim rst As ADODB.Recordset
 
Thank you Steve.

Actually, I have already done what you suggest. I have
DAO 3.6 in my references, and I have fully qualified my
db and rst statements, all to no avail.

I might add that, before I put DAO into my references, I
got compile errors. The error I am getting now is a run
time error.

Any other suggestions?

Stuart
 
You cannot use CurrentDb in an ADP/ADE application as
there is no Database Object in an ADP/ADE. The ADO code
is recommended with any back-end except JET.

Try

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = a valid SQL select statement
rst.Open strSQL, cnn, (options here)
....

See Access VB Help for options of the Open Method of the
ADODB Recordset.

Note also that the syntax for SQL in MS-QL Server is
different from the JET syntax in some areas. You need to
check and make sure your SQL String can be processed by
the MS-SQL Server.

HTH
Van T. Dinh
MVP (Access)
 
Thanks! This works fine.

Stuart

-----Original Message-----
You cannot use CurrentDb in an ADP/ADE application as
there is no Database Object in an ADP/ADE. The ADO code
is recommended with any back-end except JET.

Try

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = a valid SQL select statement
rst.Open strSQL, cnn, (options here)
....

See Access VB Help for options of the Open Method of the
ADODB Recordset.

Note also that the syntax for SQL in MS-QL Server is
different from the JET syntax in some areas. You need to
check and make sure your SQL String can be processed by
the MS-SQL Server.

HTH
Van T. Dinh
MVP (Access)



.
Click to expand...
 
Back
Top