VBA Access Database to Excel

[jeffbruce]

I have recently taken over the duty of maintaining an Access '03 database linked with VB. The program employs a series of recordset objects and connection objects associated with type ADODB. Thing is, I have absolutely no idea what the distinction is between DAO, ADO, and ADODB. Anyone have a clue? Probably - I'd appreciate the help.

What I would like to do is export to Excel a recordset object from Access, to make a file that I can import to MapPoint. But, do not concern yourself with the latter objective; all I care about is automating the process of transferring data from Access to Excel.

What is the formal definition of a recordset? What does a connection do? I have an Access book right in front of me and it's terrible. 1300 pages of nothing. What a waste of money. If someone is able to explain the function of a recordset and connection in simple terms I would be so thankful. And thanks for reading.

 

[Moniker]

You need a beginner's guide to Access. Not trying to be rude, but I believe there's a "Dummies" book for Access. (Again, don't take that the wrong way -- it just assumes no prior knowledge of Access where as an Access "Bible" or "Programmer's Guide" assumes you know you're way around.)

DAO - Data Access Object. It's the old way of doing things (changed in Access 2000), but is still supported mainly for legacy purposes.

ADO - Active Data Object. The preferred way of dealing with recordsets. ADODB is the same thing and stands for "Active Data Object Database". This is how you access recordsets in Access. It's a little more complex than DAO, but much more flexible.

Recordset - A collection of records that contains data specific to your requirements. For example, you can have a table of 100 employees that includes name, title, salary, etc. The table containing this data is a recordset, but a recordset can also be a view of that table where the salary is greater than $75,000, for example.

I've probably confused you a little bit with just that little bit of information, which is why I'm recommending a book for beginners. If you don't want to buy another book, I'd imagine there are some decent starter guides on the Internet. Google has a purpose, after all.

 

[boblarson]

DAO - Data Access Object. It's the old way of doing things (changed in Access 2000), but is still supported mainly for legacy purposes.

Actually, this is slightly inaccurate. It was the default in versions prior to Access 2000 and was supplanted by ADO as the default for referencing recordsets, in 2000 and 2002, but was reinstated in 2003 and 2007 as the default. The Microsoft Access development team took all of the complaints about DAO planned to be done away with and reversed course because it actually is the preferred method of dealing with JET objects (see this post by MVP Pat Hartman about this):
http://www.access-programmers.co.uk/forums/showpost.php?p=455820&postcount=3

ADO - Active Data Object. The preferred way of dealing with recordsets. ADODB is the same thing and stands for "Active Data Object Database". This is how you access recordsets in Access. It's a little more complex than DAO, but much more flexible.

ADO is actually ActiveX Data Objects (not Active Data Object) and is actually simpler than DAO because it has fewer properties and methods that are exposed to the programmer, but it is preferred by some because of that simplicity. For example, I prefer using it for a lot of stuff because I don't have to worry about table def and query def objects, etc. But, at the same time there are some things that ADO just isn't as good for as is DAO.

So, hopefully Moniker will forgive me for the corrections but Pat Hartman has had the "inside scoop" with the Access development team for some time now and has posted several good informative posts about these things, including:

ADO is a generic data access method. It is intended to work with a variety of RDBMS including Jet. DAO was created for Jet and expanded to work with other RDBMS.

 

[Moniker]

Thanks for the clarification Bob.

I'll never forgive you, but thanks.

Really, accurate information is always better than inaccurate. (And, I forgot the "X" in ActiveX Data Object. )