Update Access Data from Excel

Question

I have a database in Excel that was imported into Excel from an Access query.

I would like to modify the data in Excel and then update the Access query and its underlying access table with the changes made in excel.

Is there a way to that programatically.

Thanks for your help.

jswalsh

Answer 1

In my experience, Excel VBA code snippets tend to be more complex then equivalent Access VBA because Excel is not a database and has to manipulate individual cells. 

I inherited a few Excel apps when I joined my current company and, when I converted them to Access, the code was much simplified.

But the thing is you have a requirement of multiple users. Excel is not a multi-user platform, Access is!

Answer 2

Perhaps I could do that.

My issue is that the Macro program in the Excel workbooks is quite complex. There are many manipulations of reservations and customer data. The Macro program has been developed over three years and at this point performs the needed tasks quite well.  Its problem is that only one user can use it at a time. We would like to have three users using it at at the current time.

If we were to go all Access, that program would have to be redone in Access. I am not sure all of its functionality could be duplicated in Access. I am much more experienced in Excel that Access.

My proposed approach was to try to save all the work that went into the Excel Macro program. However, we may have to go to all Access if we cannot make it work this way.

Thanks again for your help.

jswalsh

Answer 3

Again, the question is WHY are you using Excel? Why not share the Access tables on your server and have the users manipulate the data using Access? 

To do this from Excel, you would have to use Office Automation to open an instance of Access and import the data back to Access.

Answer 4

I am putting two data tables on a server. Two or more users on seperate computers with Excell workbooks will upload these tables and use the workbooks macro programs to maintain the data ( change and add data) . I would like the modified data to be linked back into the server tables so the updated data is available to all users.

I have looked at the transfer spreadsheet approach but not tried it. If I understand it, that process is run from Access. I would like the run the process from the user's computers using Excel. Perhaps the Access DoCmd could be called by the Excel users.

My question was hoping to find someone who already done this.

Thanks for your help.

jswalsh

Answer 5

Let me understand...

You take data from Access put it inot Excel, change it and send it back to Access?  Why not just leave it in Access and change it there?

To answer your question, have you looked at the TransferSpreadsheet Method?  Will that work for you?