Problem running query using DAO in Access 2000

  • Thread starter Thread starter Bill
  • Start date Start date
B

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

Bill said:
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
Click to expand...


.
Click to expand...
 
Back
Top