Problem running query using DAO in Access 2000

[Bill]

Hi,

I am having a problem with creating a recordset in an
Acess 2000 database. It is a very simple database at this
point, with only 1 table that holds 1200 records do deal
with.

The code I am using to retrieve all fields from the table
to my recordset, move to the first record and then write
the First Name to the text box named FirstName follows:

Private Sub LastName_Exit(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim test, test1 As String


On Error GoTo ExitErr

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT * FROM [tblPlayers]")

rs.MoveFirst

FirstName.Text = rs("FirstName")


Exit Sub

ExitErr:
' test1 = Err
' test = Err.Description

' Stop
End Sub

The problem is that when I run the code I get an Error 13,
Type mismatch when the OpenRecordset line is reached. If I
remove the quotation marks I get a
syntax error.

What am I doing wrong? This same code works OK with Access
97, but not on the Access 2000 database I am currently
working on. Any help would be greatly appreciated.

Thanks!

Bill

 

[Andrew Smith]

By default Access 2000 uses the ADO library rather than DAO. To use DAO in
Access 2000 you must have added a reference to DAO (tools/ references in the
VBE window). You may have done this already as you don't get an error with
the database object. However ADO also contains a recordset object so you
need to either remove the ADO reference or to "disambiguate" (horrible
American word) the code as follows:

Dim db as DAO.Database
Dim rst as DAO.Recordset

This is not too much of a chore as you get an Intellisense list after you
type DAO.

 

[Bill]

Thanks, Andy.I knew it was something like that. I have
done as you suggested and it now works correctly.

Bill

-----Original Message-----
By default Access 2000 uses the ADO library rather than DAO. To use DAO in
Access 2000 you must have added a reference to DAO (tools/ references in the
VBE window). You may have done this already as you don't get an error with
the database object. However ADO also contains a recordset object so you
need to either remove the ADO reference or to "disambiguate" (horrible
American word) the code as follows:

Dim db as DAO.Database
Dim rst as DAO.Recordset

This is not too much of a chore as you get an Intellisense list after you
type DAO.

Hi,

I am having a problem with creating a recordset in an
Acess 2000 database. It is a very simple database at this
point, with only 1 table that holds 1200 records do deal
with.

The code I am using to retrieve all fields from the table
to my recordset, move to the first record and then write
the First Name to the text box named FirstName follows:

Private Sub LastName_Exit(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim test, test1 As String


On Error GoTo ExitErr

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT * FROM [tblPlayers]")

rs.MoveFirst

FirstName.Text = rs("FirstName")


Exit Sub

ExitErr:
' test1 = Err
' test = Err.Description

' Stop
End Sub

The problem is that when I run the code I get an Error 13,
Type mismatch when the OpenRecordset line is reached. If I
remove the quotation marks I get a
syntax error.

What am I doing wrong? This same code works OK with Access
97, but not on the Access 2000 database I am currently
working on. Any help would be greatly appreciated.

Thanks!

Bill

.