Working without DAO object model

[Tim Schiermeyer]

What models should VBA for Access 2000 have? Should the DAO object model be
included? I tried to run the following code and failed. But I got this from
an example in the help pages:
Sub AlterTableX1()


Dim dbs As Database

' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")


' Add the Salary field to the Employees table

' and make it a Money data type.

dbs.Execute "ALTER TABLE Employees " _

& "ADD COLUMN Salary MONEY;"


dbs.Close


End Sub

I got the error 'user_defined type not defined' for the Database
ObjectDataType. What is the alternative to implementing this code to work in
Access 2000? Also if the DAO object model is not included in Access 2000 is
there an updated help window that remove this type of misinformed code?

 

[Cheryl Fischer]

Tim,

For the code you are trying to use, you do need to set a reference to
Microsoft DAO 3.6.

hth,

 

[Van T. Dinh]

You don't need the "DAO" qualifier for Database as
Database object exists in DAO only and not ADO.

You need the qualifier for Recordset, Field, etc ... as
bothe DAO and ADO have incompatible objects using the same
names "Recordset", "Field", ...

HTH
Van T. Dinh
MVP (Access)

 

[Joan Wild]

And if you aren't going to use ADO, then remove it from the references.

 

[Tim Schiermeyer]

I figured it out thanks, but now I have a new questions, which object model
would you prefer? Also with a declaration like this:
Dim dbs As DAO.Database
in my code do I not need to add a reference to DAO to the reference list in
VBA. Or when selected in the reference list, are those settings tagged to
the code? So when this is open on another machine will it run correctly?
Thanks
Tim

 

[Joan Wild]

If you are only using Jet then DAO is best. ADO is more suited to going
against SQL Server.

Regardless of whether you disambiguate the reference, you need to ensure
that there is a reference to DAO in Tools, References.

If you uncheck ADO, check DAO and use
Dim rst as Recordset
it will assume a DAO recordset.

If someone ever adds the reference to ADO later, then you'll have a problem
because both libraries include a recordset object.

It is safer to use DAO.Recordset

You should not have problems when opening on another computer. However, you
can have problems if the files in the references are located in a different
folder that they were on the original computer, or if they are differing
versions. The usual symptom in this case is that certain functions like
Date(), Format(), Left() don't work. Doug Steele has lots of info on this
at
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
s