Dim db As Database ... I don't understand

[EdAROC]

Working on a project to take checks written (ERP SQL database) for a specified date range and create a text file which will be sent to our bank.

In the VBA Coding I used:
Dim db As Database
*This works in other files.

When I run this app/file get the error message:
"Compile Error: User-defined type not defined."

Help resources indicate the need for DAO or ADO. I haven't found a resource that explains these in detail (i.e. I understand the gist, but, are they needed for SQL connections?).

Teach me oh wise ones. Note: I prefer to learn the why's and what for's and to use them appropriately, hence, I avoid using work arounds simply because they work - it just leads to lost time "the next time".

 

[PHV]

Database is a DAO object.
When in VBE, menu Tools -> References ...
Tick the Microsoft DAO 3.x Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

Or Dim db As DAO.Database

DAO (data access objects) was predominately used pre-2000. It was used to program controls within Access.
2000 and on, Microsoft, to become interactive with the hundreds of other database types in the world and a client/server environment, created ADO (activex data objects). This is the programming language used to access and manipulate the data in a database. So when you use a form to update a table, the programming language doing this is ADO.
ADO is an advanced DAO.
Supposively, Microsoft isn't doing any more development of DAO, just ADO.
For grandfathering, you can still use both in new applications.

 

[PHV]

So when you use a form to update a table, the programming language doing this is ADO
Really ?
I thought that in a MDB the Recordset of a bound Form was DAO ...

 

[fneily]

As with all knowledge, some comes from genetics, some from experience and some from books/resources.
"When you update data by using a form, Access itself uses an entire system of programs, written in ADO, to access and update the database." Page 1139 - Microsoft Access 2000 Bible, Cary N. Prague and Michael R. Irwin.
Now, on the second page is a Disclaimer of Warranty that states "The publisher and author make no representations or warranties with respect to the accuracy or completeness of the contents of this book ...".
In summation, could they be wrong? Yes. Could you be wrong? Yes. You are not the original programmer on the Microsoft Access development team.
So, unless someone can quote and supply a name and a way to contact the actual developers at Microsoft Corp., no one should answer any Access questions.

 

[fneily]

Well, it looks like the book and you are both right. Check out:

http://support.microsoft.com/kb/281998

How to bind Microsoft Access forms to ADO recordsets
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Now this doesn't explain internally what Microsoft uses, but I'll let someone else continue the research.

 

[JoeAtWork]

Little test I just did in a simple Access2003 database

Private Sub cmdTest_Click()
    Dim rsDAO As DAO.Recordset
    Dim rsADO As ADODB.Recordset
    
    'This works
    Set rsDAO = Me.Recordset
    
    'This causes "Type Mismatch" error
    Set rsADO = Me.Recordset
End Sub

I don't have a link to the definitive article (if it exists) where Microsoft plainly states that Access is still based on DAO (at least when it comes to MDB files), but all the experience I've had with versions starting from 2000 through to 2003 has me convinced this is the case.

When I first started using Access2000, the Microsoft articles I read lead me to believe that it was now based on ADO. Another thing that convinced me was that if I created a blank Access2000 database, then went into the VBA editor and checked references, ADO was referenced and DAO wasn't. Aha, that proves it, I thought, forms and reports must now be using ADO instead of DAO!

I believed this until the first time I tried to run code such as the above, which Access immediately gagged on. What was really silly was I had to go into references, set a reference to DAO, so that I could create a recordset that referenced the form's recordset. In other words, the form was using DAO, but sort of hiding the fact that it really was based on DAO, like it was some sort of dirty little secret that Microsoft didn't want to admit.

I remember having a little argument with my supervisor when I found this out, because he too had believed the Microsoft propaganda. I believe Microsoft's obfuscation of the fact that DAO is still a core part of Access has helped fuel this confusion.

 

[EdAROC]

Tick the Microsoft DAO 3.x Object Library

Your hopes are answered. Thanks.

I didn't expect the conversation that followed, but, do enjoy following along. While I don't understand most of it I'm sure that the information will help me as my skill set develops.

Thanks all!!!

 

[fneily]

Mr. JoeAtWork
However, if in your code you you have Dim rsDAO As Recordset (drop the DAO.) without setting the references to the DAO lib., and then use a DAO method (such as Movefirst, FindFirst, etc. )it can cause an error. At least it has for me. So it would seem that DAO is not the intrinsic default.
Someone out there should know what Microsoft is actually doing internally.

 

[drchuck59]

I'm just a simple programmer, but I thought that the order of the references mattered...

If DAO is ticked above ADO, and one references the same property without clarification, then the property is assumed to be a child of the first reference.

If JoeAtWork moved his ADO reference above the DAO reference, would his error-result be reversed?


Chuck Kowalewski
Maj, USAF, MC
"Forget world peace. Visualize using your turn signal.

 

[JoeAtWork]

If declared this way...

Dim rsAmbiguous As Recordset

...then it would indeed matter which library is referenced first.

But if declared this way...

Dim rsDAO As [COLOR=red]DAO.[/color]Recordset

...I am clearly telling it to create a DAO Recordset object.