Excel connect/import Access data using ADO and VBA?

[david.j.winfield]

Excel connect/import Access data using ADO and VBA?

I was wondering if it would be possible to keep an access database as
a repository of data and use Excel as a method of interacting with
that database.

What I am envisioning is a setup where excel can quickly connect to a
database and grab the current set of data and then disconnect. Then
the excel user could do whatever they wanted to a few of the fields
and then later press a button to update the Access file.

This setup would be sort of like how you connect to a website with a
form. Connect and get the page then disconnect. The user fills out
the form and then hits submit to send it back to the server. Except
in this case Excel would be like the browser and instead of a server
there is just an Access File.

I have some experience with using VBA in excel but I have never used
ADO.

How difficult would it be to read/write data in Excel to Access.

Also, if it is possible would Access need to be installed to read/
write from Excel? Or could the Access file be setup on one computer
and then moved to another that only has Excel?

Would it be possible to change individual entries in the Access file
or would an entire record need to be modified and appended?

Would it be possible to delete records from Access from within Excel?

I know someone is probably going to suggest just setting up a proper
MySQL database and webapp, while this would be ideal it's not really
applicable to my situation.

Thank you for your help,
David

 

[Ron de Bruin]

Hi David

For reading see
http://www.rondebruin.nl/accessexcel.htm

For reading and writing
http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Dick Kusleika]

Excel connect/import Access data using ADO and VBA?

I was wondering if it would be possible to keep an access database as
a repository of data and use Excel as a method of interacting with
that database.

Would it be possible to change individual entries in the Access file
or would an entire record need to be modified and appended?

Would it be possible to delete records from Access from within Excel?

I do this all the time. Here's a basic ADO primer

http://www.dailydoseofexcel.com/archives/2004/12/16/ado-recordset-basics/\

Contrary to that post, I use the Execute method of the Connection object
almost exlusively. I hear it's faster, and while I haven't bothered to test
it, it does seem faster to me.

My normal method for reading and writing to a Jet database is to create a
custom class that mirrors my table. I read the records into class instances
and work with the class instances in my code. When something changes, I
flag it with a read-only IsDirty property in the class and write the changes
back to the database.

If you're used to something like Ruby on Rails, you're not going to get the
tightly bound data store in Excel. You basically have to do all the work
yourself. But it's not that hard and Excel makes a good front end for Jet
databases, in my experience.

 

[david.j.winfield]

Ron and Dick, thanks for your replies.

I haven't had time to examine this link (http://
www.dailydoseofexcel.com/archives/2004/12/16/ado-recordset-bas... ) in
detail yet and try it out.

However, I did notices that it mentions SQL, I do I need any kind of
MySQL server running or anything? I was hoping to just use excel
files and access files with no special server software running.

Thanks again,
David

 

[Dick Kusleika]

However, I did notices that it mentions SQL, I do I need any kind of
MySQL server running or anything? I was hoping to just use excel
files and access files with no special server software running.

No you don't need any other software than what you have. Access is the
front end for a Jet database. It could be the front end for SQLServer,
MySQL, or just about any other kind of database, but if you create the
database in Access (rather than link to it in Access), then you'll get a Jet
database.

You need Jet (installed with Access so you have it), Excel, and ADO. I'm
not sure how ADO gets installed, but I'm sure you have it. It may get
installed with Windows, Office, or both.

ADO lets you work with a lot of different databases using the same syntax.
You'll need to know some basic SQL (structured query language) to tell ADO
what records to return. If you don't know SQL syntax, you can open up
Access, design you're query, and look at the SQL view.

Post back after you've looked at the article if you have any more questions
or need more clarification on anything.