USE DAO with an Access 2002 Data Project? 1

[jgoodman00]

I am developing an Access front-end which connects to a SQL server 2000 database. Some of my code (migrated from Access 97) uses DAO as the access method. Reading the help file, Microsoft states it is possible to use this method, but you must explictly declare variables for use with it, & set a permenent link to the DAO 3.6 library.

I set this link.

I tried the following:

Sub AnyName()
Dim db as DAO.Database

Set db = CurrentDB

End Sub


This sub does not work. The variable, db remains to be 'nothing' even after the assignation.


Has anyone else seen this? James Goodman

http://www.james-goodman.co.uk

 

[Rolliee]

Did you set references with a check in the DAO 3.6 box?

Rollie E

 

[jgoodman00]

Yes James Goodman

http://www.james-goodman.co.uk

 

[Rolliee]

I just did this adding a 'msgbox db.name' after the set call and it gave me the name of the databse. You might try it as a function with the vartype of the function as an object and return db.

Rollie E

 

[pcdaveh]

If your linking to SQL Server, You might consider using a Microsoft Access *.adp project (Access Data Project). You won't need to link using DAO or ADO.

 

[jgoodman00]

I am using an adp. I have a number of functions which run & need to gain access to the data. They are currently all written in DAO, & I dont have time to change them over yet... James Goodman

http://www.james-goodman.co.uk

 

[ClydeDoggie]

You are in for some work. If you are using an .adp connected to a SQL Server db you can't use the CurrentDB method to get a reference to it. You can still use DAO but you have to use ODBCDirect and establish a connection to your database. Try this snippet in a sub and I bet it works.
(substitute your db's name for DBNAME, create a DSN for it and substitute your DSN name for DSNNAME)

Dim db As DAO.Database
Dim wk As DAO.Workspace

Set wk = DBEngine.Workspaces(0)
Set db = wk.OpenDatabase("", dbDriverNoPrompt, True, _
"ODBC;DATABASE=DBNAME;UID=sa;PWD=;DSN=DSNNAME&quot
MsgBox db.Name
Set db = Nothing
set wk =nothing

Hope this helped, I might suggest you take the pain now and convert to ADO. It won't be much more effort, and will save you having to do it later.

Alternatively you could use the mdb flavor of Access 2000 and link to the SQL Server tables. That way you wouldn't have to change any of your code.

 

[jgoodman00]

ClydeDoggie,

I thought this also, but this is what the Microsoft Help file states:

Note In Microsoft Access the CurrentDb method establishes a hidden reference to the Microsoft DAO 3.6 Object Library in a Microsoft Access database (.mdb). If you want to use the CurrentDb method in an Access project (.adp) you must set a permanent reference to the DAO 3.6 Object library in the Microsoft Visual Basic Editor.

This to me states it can be used. However, it doesnt work!! I have now referred this problem to Microsoft. They are yet to respond... James Goodman

http://www.james-goodman.co.uk

 

[ClydeDoggie]

I read the same thing in the Help file, but not trusting MSoft to know their own products I looked around for a different answer. I found a link on 'The Access Web' site(

http://www.mvps.org/access/modules/mdl0052.htm)

to a code snippet on Trigeminal's web site for code to handle this problem. You''ll see it's basically the same thing I showed yesterday, just wrapped up in a usable form. Michael Kaplan, the lead guy at Trigeminal, is a certified genious so if he says you can't use it I would trust him more than MSoft. Both the Trigeminal site and the Access Web site are very useful resources.
I hope this gets you going. If you use Trigeminals module you will only have to change the lines where you set the database variable.

Peter Lake