dim db as database - error 1

[Darrylles]

Hi,

For ages now I've attempted to define a variable as type 'database', but have always received: 'User defined type not defined'.

e.g. dim db as database

I've always been forced to use 'currentdb' without first defining a variable, and so far that's all I needed to reference. Now, I need to reference an external database.

Which library reference am I missing? Surely a database type should be standard by default? (Obviously not).

Can anyone point me to a lib reference that will give me access to the 'database' typedef?

All help much appreciated.

Regards,

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

In VBE - Tools | References - ensure you've referenced Microsoft DAO 3.#...

Also, explicitly declare any DAO objects

[tt]dim db as dao.database
dim rs as dao.recordset
dim td as dao.tabledef
dim fl as dao.field
...[/tt]

Roy-Vidar

 

[Darrylles]

Hi,

Thanks for response.

I understand your reference statements.

However, I try ensuring DAO 3.# in tools -> refs and on typing 'dim db AS dao.database' in code, and leaving the line - it does not update the word 'database' to 'Database' as expected (it does not recognise the type 'database'). And on attempting to run the code it throws the usual 'typedef' error.

Also, I may wish to use ADO, ADODB. Same problem here.

If I refer to currentdb(), then all works fine.

This is a 5 year historical problem. I have had numerous computer / MS Office changes in between. I'm obviously missing some fundamental setting somewhere.

I have wide experience of software development and develop quite complex MS Access (VBA) applications for my company.
I am stumped by this seemingly stupid lack of access to a typedef which I would have assumed was available to ADO, DAO, ADODB.

Seemingly, it is not available to any by default.

Thanks again,

Darrylle






Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

Select/Check/Put a mark on the line

[tt]Microsoft DAO 3.6 Object Library[/tt]

in tools | references.

Unless you're working with ADP, but in ADP's currentdb wouldn't work, either. CurrentDB is a member of the Access Application object, available for mdb's.

The history is as follows

DAO was default in prior versions, then in the 2000 version, ADO became default. To use DAO, one had to check the DAO library. This lasted through the xp (2002) version. In 2003 both ADO and DAO are checked by default.

If checking the reference doesn't do the trick, then there is something faulty with your setup.

Roy-Vidar

 

[Darrylles]

Roy,

Nice n succinct (if cryptic at times).

Yep - u r correct. It worked.
Check DAO 3.6 on (but also look for order of DAO / ADO library references if loaded together - check one or the other off to arrange 'order'. Highest is called first as default).

ADP though (I have no idea)?
Give us a clue Roy.

Star for you - thanks, much appreciated.

ATB

DarrylleS



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[RoyVidar]

ADP - Access Data Project (forum958) - Access as a front end for SQL-server. Here are some threads discussing ADP/mdb thread958-1040307, thread958-1028921.

I gave advice to explicitly declare the variables, see first reply, and not rely on the order of references. The latter will sooner or later bomb (humble opinion/experience...)! with the errormessage you've gotten, or the method or memeber not found error.

Think also of other objects, sharing the same name, but completely different properties and behaviour, for instance the word dictionary object vs the scripting runtime dictionary object.

If you're using objects that can get bart of such ambiguity challenge, always prefix with library (again, recommandation).

[tt]dim wdDic as word.dictionary
dim fsDic as scripting.dictionary
dim rsD as dao.recordset
dim rsA as adodb.recordset[/tt]

Which will reduce the challenges that usually arise when needing to use objects from different libraries.

You know, some even like to prefix the standard VBA functions

[tt]debug.print VBA.Date
debug.print VBA.Strings.Mid$("This is a test",11,4)[/tt]

... yeah, it does reduce visual ambiguity, when browsing the code, perhaps it'll also be a tiny fraction faster, but I don't know, the only real ambiguity it would solve, is when someone has used the name of a VBA function as name of controls/objects...

Roy-Vidar