From Excel, Open Access, Copy a cell to Access, close Access

[FTM]

Hello Everyone,

I want to open Microsoft Access Database using VBA from an MS Excel Spreadsheet, open a data entry form then add data to the existing database. When I have exhausted all of the lines on the spreadsheet then I want to close MS Access.

What is happening is that I have a spreadsheet that contains daily production data per part number. I want to append that data to a spreadsheet at the end of the production shift. In the past I have used the Macro Editor that is native to MS Excel and life has been good. In the event that I can't use the editor I have been quite adept in pirating code from sundry forums on the web.

Now...

I consider my "Google-Foo" to be at or above average and I've crawled all over google every way that I can think of and as of yet I have found no mention made at all of appending a database from within MS Excel. I don't write VBA code very often so most every time that I try I have to relearn most things. Is there anybody that has tried this before? Is manipulating an Access database from an Excel VBA script even possible?

Regards to everyone and thanks in advance for any assistance that can be offered.

 

[FTM]

xenou,

The code is indeed running almost instantaneously. I originally created the database with an export to access, linked table. I can't find a reference in MS Excel or MS Access that indicates and existing link. The .laccdb file doesn't appear until I attempt to open the database. If I open the macro editor and click the stop button then the .laccdb file goes away and I can open the database.

I know that the data being exported to the database is correct. I'm going to treat this detail as a fluke and create the next database and write an export script using the first as a model. I have scripts stacked up doing different things. I clear out data from an old form, I get data from a pair of other spreadsheets then I e-mail a report based on the data that I copied into the master spreadsheet. My plan is to write data from the master spreadsheet to a database then kick off the e-mail. We'll see how all this works out.

You have been more than kind in your assistance thus far in regards to this matter. When I get the next spreadsheet written and the export scrript running I'll let you know how it is all working out.

Regards,

FTM

 

[xenou]

Okey dokey. I push data to and from Access/Excel all the time so you should be good to go in the end.