Multiple User upload en download from Access Database

[JohnGaltnl]

[After posting this thread i realised the title would be better described by something like : " Upload and update seperate excel sheets into one Access table". But I noticed i cant change this anymore]

Hi all,

With the limited knowledge i have, but i'm learning, i'm starting a little access database project.

The idea is to let multiple users who are using separte excel files with the same data layout upload their data into an access table using an Macro.
The excel files all have the same header and contain the agreements they made with customers which will result in an invoice. The agreements are entered in one row which starts with an unique reference (persons initals and a number).

Below you find a macro which i managed to create so far. It will upload the data in the sheet to a seperate table within the database, after it has deleted all the data in that table.
I want to edit this marco so that it will upload the data of all the seperate excel files into one table in the acces database and update the data if changes have been made.

THe following step is that i can download all the whole table from this access database and add some invoice information which will be updated in the database and in the excel sheets of all the users.

I would be very grateful if anyone could tell me how to continue.

Please Login or Register  to view this content.

[davegugg]

Why are you deleting the data from the MONPAs table each time?

[JohnGaltnl]

Hi davegugg,


Thats one of the things. I don't know yet how to make it update the data instead of deleting all data first.

[davegugg]

Can't you just get rid of that line and not delete the data?

It'd probably also be faster just to pull in the spreadsheet from the Access side, once you put it on an Excel sheet in the correct format to be uploaded. You can use the DoCmd.TransferSpreadsheet method.

[JohnGaltnl]

But then it wont update the lines that have been changed in the excel file and are already in the database?

[davegugg]

Your code doesn't do that right now, it just puts all the data on your spreadsheet into the table in Access.

Let me see if I understand you correctly. You have data in Excel that you want to get into Access. Some of this data is already in Access. Some of it is in Access but the values may have changed. Some is not in Access yet. You want to take the information in Excel and either add it to Access or update any values in Access that are different from the data in Excel. Is that correct?

Can you post a sample of your Excel data, including an example or two of how it will change so as to need updating in Access?

[JohnGaltnl]

Hi Davegugg,

Thanks for your quick reply. Unfortunately i couldn't post the last few days.

An example of the user file is attached.

The data thats in the users excel file i'd like to add to the access database. There are severel users that use this same kind of excel file which all have a unique reference in column A.
All this data from the different files should be upload to one excel table from which i can make a download later on and add values to the last five columns (which won't be filled in by the users) and update those again into the access database.

All the data in the columns between the the first column A and the last columns( those filled in by me) might change later on by the users.

So what the macro actually needs to do is to check which rows in the excel file are already in the access database based on a match with the unique Reference in column A and update and overwrite all cells beteen column A en the last five columns.

[davegugg]

Ok, this will run much quicker from the Access side. You can set up a form in Access with a single command button to import a spreadsheet from Excel and update your data with an UPDATE query using SQL.

You'll want two tables in Access, one to hold all your data and another to import your data into. You'll need the method to delete the current data in the import table, import the new data into the import table, then do an update query using the Reference field in the WHERE clause. Does that help?