Standard VB statement bombing

[Ken Smith]

The following statement is used throughout my application's coding and I have
not had a problem in the past:

Dim db As Database

Now, when I compile the application on a PC running Access 2000 or on a PC
running Access 2003, I get a compile error stating the "db As Database"
portion is a

User defined type not defined!


I have no idea what this is telling me or how to overcome it. The code for
the entire part is as follows:


Private Sub cmdOk_Click()
Dim rst As Recordset
Dim db As Database
Dim strsql As String
Dim frmName As String

frmName = Me.OpenArgs
If txtStname = "" Then
MsgBox "You must enter data into the search field", vbOKOnly, "Search
Error"
Else
Set db = CurrentDb()

Set rst = db.OpenRecordset("tblStreet Inventory", dbOpenSnapshot)
strsql = "[Street Name] Like '*" & txtStname & "*'" & ""
rst.FindFirst strsql
If rst.NoMatch Then
MsgBox "You must enter a valid Street Name"
txtStname.SetFocus
Else
Forms!switchboard.Text1 = txtStname
End If


rst.Close
db.Close
Set rst = Nothing
Set db = Nothing

DoCmd.Close acForm, Forms!frmStreetStatusSearch1.Name, acSaveNo
DoCmd.OpenForm "StreetLookup"
End If
End Sub

 

[John Spencer (MVP)]

It means that you need to change the line to
Dim db as DAO.Database
And I would also change
Dim rst As DAO.Recordset

You also need a reference to the DAO library.


Quoting Doug Steele
Database is a DAO object. By default, Access 2000 uses ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

(Hopefully this explains to you why you can't just use "DIM rst as
Recordset")

 

[Bas Cost Budde]

Be sure to have a reference to DAO 3.51 (or so), and explicitly declare

Dim db as dao.database

 

[Dirk Goldgar]

Be sure to have a reference to DAO 3.51 (or so), and explicitly
declare

Dim db as dao.database

In Access 2000 or later, that should be DAO 3.6, not 3.51.