Dim dbs as Database not accepted

[chris klein]

Am finding that the statement
Dim dbs as Database, which is included in the VB Help system as an example, is not accepted when I try to run the sub that contains it. Code stops with
Compile Error: User-defined type not defined

What's wrong?

 

[izyrider]

check your references.
you are using DAO code and you probably don't have DAO 3.6 referenced.

izy

 

[chris klein]

References

And how / where do I check the References?
Thanks

 

[Argonak]

in vba, tools menu, references.

 

[izyrider]

exactly!

alt-f11
menu: tools/references
trawl down to M for Microsoft and locate DAO 3.6

izy

 

[izyrider]

also -

check ALL the object declarations in the example code

you probably have:

dim dbs as database
dim xxx as recordset
dim yyy as querydef 'maybe

CHANGE them to:

dim dbs as DAO.database
dim xxx as DAO.recordset
dim yyy as DAO.querydef

database throws an error in ADO-only machines as you saw.
but e.g. recordset exists in both ADO and DAO libraries so there is no error and A defaults to whichever library is higher in the list of references... probably ADO in your case.
trying to open an ADO recordset against a DAO database object will trigger even more obtuse error messages.

izy

 

[chris klein]

Got it working

Setting the reference got it working. Thanks for the help!

 

[jonomac]

What would be the alternative to using "dim dbs as database" in ADO? I've found "CurrentProject" is this correct?

I need the below replicated in ADO as I'm trying to build something entirely with the new language to learn how it operates (this is taking some time I can tell you!) Thankfully I finally found the ADO help guide ADO210.CHM on my C drive which is helping me a lot more than Access XP help is!

Private Sub Form_Load()
  Dim dbcds As Database
  Set dbcds = CurrentDb
  dbcds.Properties!AppTitle = "CPB Billing Tool"
  Application.RefreshTitleBar
End Sub

It doesn't like the below as there doesn't appear to be an equivalent to Apptitle in ADO, any ideas or am I going about this all wrong?

Private Sub Form_Load()
  CurrentProject.Properties!AppTitle = "CPB Billing Tool"
  CurrentProject.Application.RefreshTitleBar
End Sub

 

[Mile-O]

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "QueryName", cn

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

 

[Mile-O]

Sorry, read the full question now.

You'll need to add an ADOX reference too. i.e MS Extensions for ADO and DDL Library v2.x and use the Catalog object.

 

[jonomac]

Aha! OK thanks for the quick reply...

It seems I require the reference to ADOX for a number of things I wanted to build if using ADO in Access XP... the other problem I'd been experiencing was copying a recordset into an existing table or even just creating a new table using the recordset... this also seems to require the ADOX reference...

 

[Mile-O]

ADOX handles the whole database scheme with respect to views, tables, etc.

The DDL (Data Definition Language) in the reference's title is a clue that you may need it to play about with table structures.

 

[jonomac]

hmm yes that would appear to make sense

I'll give the recordset copying a go now that I've added the ADOX reference in