Excel to Access

[whatthe]

I just wanted to know if there was a way to add the information contained within an excel form into an access database using a macro? What I am looking to do is automatically have the data added when I press a button, or when I close a worksheet.

[royUK]

What kind of form?

[whatthe]

I meant to say excel sheet. I want to add the data from my excel sheet to an access database by clicking on a button. Can anyone tell me if this is possible?

[Marzuk]

Absolutely. You would use ADO to do so, and there are quite a few tutorials available on the net for that. Just keep in mind that when you read from or write to a database, locks are placed on the database depending on how you do so. You want to make sure to take care how you implement this depending on how many users etc.

[whatthe]

There will only be one user adding the information to the database. Will we be able to repeatedly add data from the excel sheet or is it a one time deal?

Would you explain what you mean by "locks"? I am new to access.

[Marzuk]

Databases are environments that are suitable for more than one user. Whenever a user accesses data in a database, the database has to know how to deal with that.

If for example I run a query on the database and maintain an open connection, I may lock the row. If you tried to make any changes to the row, then you would get an error because it is currently being edited. If I requested that same information as ReadOnly, then no lock would be placed (but if you change it, I would not be aware and I'd be looking at old data).

If you are the only one accessing a database, you would not really have to worry about that as much. You would be able to retrieve / store data as many times as needed.

EDIT:

I'll provide a simple example later. I am at work right now so my time is a bit limited.

[whatthe]

Thanks, I think an example would help.

[putnum]

Not to butt in on this thread but I too would like to know how to do this.

My wife and I are finally doing our "finance budget" together after many years of her doing it. I want a way to be able to separate the data from the presentation. I need to be able to setup the access database with all of the "categories, tags etc" and then map them into excel 2007 and when either I or her edit the spreadsheet in excel the "data" gets written back to access.

[Marzuk]

It appears that .mdb or .accdb files are not accepted as uploads. I was going to attach one to simplify the example. Guess you have to roll your own

The workbook attached is a *very simple* way of retrieving information from an Access 2007 database, and adding a record to it. In order to test it, create an Access 2007 database named "ADOExample" and place that database in the same folder as the workbook. Create a table named "MyData" with an Auto Incrementing column, 2 text columns, and a number column.

Keep in mind that ADO is a pretty big topic, especially depending on your level of experience with vba (or some other programming language), and this is just the briefest of thumbnail sketches.

Different data sources require different providers and sometimes additional properties to be set. This is only showing 1 way to get and store data, though there are many (batch updates, different cursor types, lock types, etc). There are various ways to manage the connection, which can have an impact on performance (leaving connections open, always closing them after a query etc).

Also note: In order to use ADO, you need to set a reference to "Microsoft ActiveX Data Objects" which has several versions, I've used 2.8 in this example.

Another issue with ADO, is the error messages you get from it are less than helpful. I've included an Error Handling routine which attempts to give a more clear error message (though it really "handles" nothing). This was shamelessly stolen from somewhere else at least 2 years ago and I cannot remember the source.

You do not need Access installed to use ADO, though creating a database and simply opening it up is pretty valuable from a debugging standpoint.

Once you get it tuned to do what you want, its flexibility and performance is great, compared to trying to store data in a spreadsheet which has so many pitfalls its not even worth discussion.

[whatthe]

Would you send that file to my email? Its NoSpam-mjc41918@roadrunner.com just remove the NoSpam-.

Thanks

[putnum]

Hi could you send it to me too?

My email can be found here. http://scr.im/putnum

Thanks

[Marzuk]

I included instructions on how to recreate that db, no email should be needed.

[putnum]

I don't have access 2007 only 2003. Phewy

[putnum]

ok i did what you said but using access 2003 and I can't figure out how to write to the db using excel?? Am I missing something

[putnum]

I tried the GetData and the StoreData macros and i get a blank error box.

[Marzuk]

ok i did what you said but using access 2003 and I can't figure out how to write to the db using excel?? Am I missing something

You had Excel 2007 listed so thats what I used as the Access version as well. To use Access 2003, you would need to use this provider "Microsoft.Jet.OLEDB.4.0" instead of "Microsoft.ACE.OLEDB.12.0".

The rest should work the same. Its a bit odd that you got a blank error. Perhaps some issue with the code I borrowed to translate the automation errors.

EDIT:

LOL thats what I get for hacking something together in a hurry. I forgot to put "Exit Sub" prior to the error handling in the GetData sub.

[putnum]

ok so in the code i changed the provider and added the exit sub....

Now when i run GetDate I get a unspecified Error.

Do I need to create a table in excel or something?

I got the access db setup and it ends in.mdb

[Marzuk]

Did you create a table? Is the table called "MyData"? Are there 4 columns in it, an AutoIncrementing column, Text, Text, and Number?

I saved the 2007 db as a 2003, and was able to access it with either provider (even though the JET provider is always listed for mdb files).

[putnum]

Yea I got it but I guess I just don't understand ado.

I don't want a macro that I have to manually define the field names in.

I just want excel to read/write from a access DB! lol

I dunno its frustrating.

[Marzuk]

The field names do not have to be manually defined in the macro. You *do* however need a database setup to put data in and retrieve data from.

You can not however just slop data into a database in the same way you would Excel. Databases have to have a structure setup, and have to have columns defined etc. You have to tell a database what kind of data you are going to put in that column.

I'd take that as a sign however that what you are asking for, is FAR too complex for what you need to do (or are willing to learn).

EDIT:

And yes, ADO is complex. There are entire books on ADO, so its not something you can just mimic with the ease of a copy and paste in VBA. I'd say that the target for ADO would be intermediate / advanced VBA programmers.