User defined type not defined (as database)

[Darrylles]

Hi,

I'm really trying to get my head around referring to tables etc via VBA, from within Access 2000.

I'm getting confused by ADO, DAO etc.

I can easily refer to a table in the current database and manipulate records within that table with:
[tt]
Set rst = CurrentDb.OpenRecordset("tblMyTable", dbOpenDynaset)
[/tt]
this is with or without defining rst as a recordset.

Examples however, show: Dim dbs as database and when I try this I get: 'User defined type not defined'.
Database is not a type in 2000 obviously.

I assume this is down to a library that I need, but what is it called, and more importantly where can I get it? Or, what is now the replacement?

I have the Office 2000 cd's, and VB 6 cd's but can't find any obvious files.

There are reams of info. on this VBA subject, and I know VB pretty well (as a developer in other areas), but if you know of a site on the net that 'cuts to the chase' I would be grateful.
A quick explanation of this prob. here would also be appreciated.

Thanks a lot in advance,

Darrylle

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

 

[vb5prgrmr]

First off if you want to use DAO you need to goto tools>references and select Microsoft DAO 3.6 Object Library then you will be able to do this...
[tt]
Dim daoDb As Dao.Database
Dim daoRs As Dao.RecordSet
[/tt]

Note: As you can see I specify what technology is to be used by these objects (Dao). You will need to do this or you will have problems because Access2K will try to default into using ADO and then things can get crazy why it does not work.

Good Luck

 

[Darrylles]

Hi vb5,

Thnx for response.

You have said: 'If I want to use DAO...'.

I HAVE got DAO 3.6 in there, but why should I 'want to USE' DAO?

What is it exactly, in comparison to ADO for instance or I may want to use neither for that matter?

On using context-sensitive help on 'set' and 'database': I get specific code that states: 'SET dbs as database' - why? if it's not relevant to my 'installation'.
If it 'defaults' to ADO - why should things 'go crazy' - why does it not have a 'database' data type?

I understand the need for the 'DAO.' reference incidentally, it makes sure that the code references DAO methods etc.

I much appreciate your response, and am not being pedantic, I'd just love to get a general (with a capital G) understanding of these concepts/technologies, before I roll my sleeves up and get in there.

If you could make a 'GENERAL' description of ADO, DAO and none - what would it be?

Kind regards and thnx again,

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

 

[Nikita6003]

Darrylle,

You say you want to use

db As Database

. That's a DAO way of opening a connection to your database. In ADO you'd not use the Database object, you'd use the Connection object to access your database. Guess that's what vb5prgrmr means.

The reason you can't just go in and use Dim db As database in Access 2k or highr is that ADO is now the standard data access object rather than DAO. If you still want to use the Database & Workspace objects you'll need to set a reference to DAO 3.6

Because ADO's also active, this means you'll now need to specify which Data Access tool you want to use when declaring variable. Both ADO and DAO have a Recordset object, for instance. Just to be on the safe side stick either DAO or ADODB (depending on which data access method you need) in front of the declaration, e.g.

Dim l_rsRecordSet as DAO.Recordset

As for which to use & when - that's basically up to you. Microsoft wants to push us more in the direction of ADO, and to be sure, it has its advantages over good old DAO, but personally I stick with DAO for Access, and use ADO for my connections to Sybase and Oracle.

Both objects have their own set of properties & methods; these are not completely in sync (of course not ;-)) so stuff you've been using in DAO is not defined in ADO per se. It's a matter of choose the method which offers you the most flexibility & ease of use, i guess ...

Now, using ADO means that you have to set up a connection to your database. For this you use the connection object. You pass a Connection String to this object, then open it. If all goes well and all drivewrs are installed, the connection will be established. Once it's set up, ADO behaves in a similar way to DAO - i.e. you open & (ab)use recordsets, use filters, move about, update records, delete them; you can use stored procs (i.e. Access queries) or pass a bit of SQL to your connection; however, forget about tableDefs and QueryDefs - they don't exist in ADO. Instead, you have table & procedure objects - but you'll need the referenc to ADO Extensions set as well. This adds ADOX to your project, which contains Catalog, Table, Index, User, Procedure & View objects which allow you to manipulate tables, queries, etc.

Hope this rather involved explanations manages to clear up some isseus - if not, sorry ;-)

Cheers
Nikki