Importing Excel files into a Multi-User Database

[jonosborne]

I know this is very long-winded but I am trying to include all information.

I currently manage a data input database which is used within a multiple user environment. This data is stored within one large table.

Currently, each user inputs one record at a time and I have been asked to create a method of input whereby a large number of records can be added to the database in one go.

So... I have created an excel spreadsheet with six columns. The data is entered into this spreadsheet and saved. This is held on the users personal drive on our network (i.e. Z:\spreadsheet. xls). I have used the ‘Transfer Database’ macro which copies the data into a table called tblOK. This table has a field called ‘Inputter’ whose default value is ‘environ(“usern ame”)’ so that each entry by that person is easily identifiable.

There are then two queries; the first appends data from tblOK and two other tables into the main table (tblMainInput) where the field ‘Inputter’ is equal to environ(“userna me”) to ensure that only their data from tblOK is added to tblMainInput and the second deletes the data in tblOK where the field ‘Inputter’ is equal to environ(“userna me”) to ensure that only their data is deleted.

This curently works when carried out by one person at a time but when the import is carried out by more than one person at the same time (an action which needs to be possible considering the large number of people using the database) I notice errors; firstly when transferring the spreadsheet into the first table (some records are lost and the Inputter field incorrectly assigns someone elses username to records) and secondly when the append query is running (I am encountering Record Lock Violations which loses records).

I have tried amending the Record Locks propery on the form to ‘Edited Record’ but there is no difference in the outcome. Is there any way of ensuring that all users records are imported ?

Any ideas/suggestions would be greatly appreciated, thanks. Oh, i am using Access '97.

[ADezii]

I know this is very long-winded but I am trying to include all information.

I currently manage a data input database which is used within a multiple user environment. This data is stored within one large table.

Currently, each user inputs one record at a time and I have been asked to create a method of input whereby a large number of records can be added to the database in one go.

So... I have created an excel spreadsheet with six columns. The data is entered into this spreadsheet and saved. This is held on the users personal drive on our network (i.e. Z:\spreadsheet. xls). I have used the ‘Transfer Database’ macro which copies the data into a table called tblOK. This table has a field called ‘Inputter’ whose default value is ‘environ(“usern ame”)’ so that each entry by that person is easily identifiable.

There are then two queries; the first appends data from tblOK and two other tables into the main table (tblMainInput) where the field ‘Inputter’ is equal to environ(“userna me”) to ensure that only their data from tblOK is added to tblMainInput and the second deletes the data in tblOK where the field ‘Inputter’ is equal to environ(“userna me”) to ensure that only their data is deleted.

This curently works when carried out by one person at a time but when the import is carried out by more than one person at the same time (an action which needs to be possible considering the large number of people using the database) I notice errors; firstly when transferring the spreadsheet into the first table (some records are lost and the Inputter field incorrectly assigns someone elses username to records) and secondly when the append query is running (I am encountering Record Lock Violations which loses records).

I have tried amending the Record Locks propery on the form to ‘Edited Record’ but there is no difference in the outcome. Is there any way of ensuring that all users records are imported ?

Any ideas/suggestions would be greatly appreciated, thanks. Oh, i am using Access '97.

Try having the first 2 stages of the process occur on the Client's PC, namely the transfer of data from the Spreadsheet into tblOK, massage the data, then append it to a localized version of tblMainInput. The final steps would involve the appending of data from the local tblMainInputs to tblMainInput residing on the Back End, and the subsequent deletion of Records from tblOK and the local tblMainInput. This is just an idea, I do not know if it is feasible in your current situation.

[jonosborne]

Try having the first 2 stages of the process occur on the Client's PC, namely the transfer of data from the Spreadsheet into tblOK, massage the data, then append it to a localized version of tblMainInput. The final steps would involve the appending of data from the local tblMainInputs to tblMainInput residing on the Back End, and the subsequent deletion of Records from tblOK and the local tblMainInput. This is just an idea, I do not know if it is feasible in your current situation.

Thanks for the response, i will have to look into this possibility in more depth due to the large amount of people who use the database at the same time (we have over 100 people who all have access to the database and use it regulary). This would mean that everyone in the office would need a separate database on their own filespace.

Definately given me something to think about, thanks.

[Stang02GT]

Have you thought about this as a possibility?

Create on spread sheet out on your network drive so that all the users can update it. Then link the spread sheet into excel as a table.

1. File
2. Get External data
3. Link table

This way any changes made to the spread sheet will automaticlly be made to access, and any changes made to the table in access will be made in excel. This will allow for multiple people to make changes to the the spread sheet at once, and then when access is opened all the changes will happen automatically without having to run and import macro/code.