Excel VBA Code to export to access table

[creative-2008]

I'm working on a spreadsheet in Excel with three workbooks. The first two contain forms for the user to input data and the third displays the results and allows the results to be printed. This is working as I wish at the moment, but I would like to extend it's functionality by exporting the data from the third workbook into an Access table.

I have googled this extensively but haven't been able to find working code that will satisfy my situation, or even anything that I can adapt. This is the first project that I've attempted in Excel, and I realise that in trying to export data to Access I'm in slightly over my head.

I have four cells that I want to export to Access: text, an integer, the date, and a time in minutes and seconds. I have therefore created an access database with the four appropriate fields, and also a fifth for a unique ID (which I understand access will handle.)

I'm looking for some VBA code that can be run by pressing a button and will export the four cells into Access. At the moment the cells are between J1 and J4 in one Workbook.

Does anyone know of a tutorial that I can follow to achieve this? Or of any code that I can make basic edits to?

Any help will be greatly appreciated because I know that I am asking for quite a lot, and appreciate that I am slightly out of my depth. I've come really far in this project though, though advice on HF and Googling, and would be pleased to put the finishing touches on this project.

 

[PTNL]

Assuming that you have the desired schema already setup in the Access file, there's a couple ways I see this can be achieved:
1 - Write some VBA within the Access file to connect to the Excel sheet, loop through the sheet/rows/columns however you need, and load rows into the Access table of your choice.
2 - Do the same as #1, but the worker logic is in the Excel file's VBA.
3 - If you're comfortable with writing about a dozen lines of code in C# or Java or some other more robust language. Then you can use ODBC connectors to execute some (limited) SQL against the Excel file, iterate however needed through the resultset, and send INSERT (or UPDATE) statements to the Access database.

Personally, I would look at #3 or #1.

I would also question whether Access is an appropriate datastore for your needs; something like SQL Server Express would be free and can avoid a few potential issues with Access data storage. (e.g. growth limitations, odd-ball datatypes like OLE, etc.)

As for writing code... You can take a stab at VBA or another language. There's plenty of people that can assist in this forum on a case-by-case basis, though you usually have to show that you're making efforts yourself (code snippets, progress updates, etc.). Alternatively, get in touch with a contracting agency and ask them for some temporary help. You can budget time for not only completing the work, but also for knowledge transfer and training.


*Edit*
Just re-read and noticed this:

I'm working on a spreadsheet in Excel with three workbooks. The first two contain forms for the user to input data and the third displays the results and allows the results to be printed.

Are you sure you mean "workbooks" instead of "worksheets"?

 

[creative-2008]

Thank-you for your reply.

I was intending to follow option number two, because that is the only program that is being opened by the general users. It's only me that will access the database to read and analyse entries.

You are also correct that I mean I have three worksheets, rather than three workbooks!

I've decided, that for the time being, I'm going to restart my project in PHP / HTML / MySQL instead. I think it's more suited to this task, it will produce a result that is easier for the end user to interact with, and I have more experience with PHP.

Thanks very much for your help. I've really enjoyed having a play around with Excel, and I think it's likely that at some stage in the future I'll complete this project in Excel, just to say that I've done it.

Thanks again.

 

[PTNL]

I'd suggest sticking with your decision of the PHP route or at least an actual (web) app, if for no other reason than to separate the data migration logic from the data input/output containers. With Excel and Access, it's too easy for novice users to set themselves up for disaster over time with different versions of your Excel/Access VBA in circulation over emails, thumb drives, and network shares.

Good luck!

 

[creative-2008]

Thanks for your advice.