Multi user database that updates from excel help

[braff24]

Hi, I have a database that needs to update with current data from an excel file and also may be opened by multiple people at the same time.

Initially I had the database linked to an excel file but it wouldn't work because excel has an issue with having 2 people open the file at the same time.



Then I tried to have the database import updated data from excel on every open using DoCmd.TransferSpreadsheet but now when another user has the file open I get the error "You tried to lock table while opening it, but the table cannot be locked because it is currently in use".

Does anyone have an idea of how I can accomplish this? Thanks in advance.

Edit: I found out the reason I was getting the "You tried to lock table while opening it" error was because I was having the table be deleted before importing from excel because if I didn't, it would create duplicates for records that already existed. So a follow up question, does anyone know how to use DoCmd.TransferSpreadsheet without creating duplicates? Thanks.

[aytee111]

Create a table with a timestamp which is updated every time you import the file and first check when last it was imported, that way if 2 people open it in quick succession they won't knock each other. Give a time length difference, depending on how often the data needs to be updated from the Excel (an hour, a day?).

Import the file to a temp table and run queries to update the data in the real table: update data for those records that already exist, append new records if they don't exist, delete records that no longer exist.

(Edit: alternatively delete all records in the real table and append all the records from the temp import table.)

[braff24]

I tried indexing the table and that works but for some reason if another user has the file open at the same time, it takes a very long time to import, so maybe that won't work.

I also tried deleting all records in the table and append from the temp table or just directly and that works but if another user has the file open with a form at the same time, the form automatically updates to #deleted on everything if you click anywhere and you have to hit refresh all to fix it. Do you know of any way to solve this? Like if this happens, me.requery or something. Thanks.

[aytee111]

You could check to see if the form is open and do a refresh of the screen after the import process has completed. Not sure how the user will take to this, altho you could provide a message to them first.

Which table did you add indexing to, and why? When you say it takes a long time to import, is time a problem, like is it urgent that the new data gets in?

[braff24]

I tried indexing the table to prevent duplicates when importing but I'm realizing that wouldn't work.

I tried to use: If CurrentProject.AllForms("form").IsLoaded Then Forms!form.Requery but that only works for the current user. Is there another way to do this so that if a different user has the form open while someone else updates to automatically requery if changes to the table are made or something?

Also, how can I create a time stamp for the table when it imports?

[aytee111]

In attempting to prevent duplicates when importing suggests that you are importing directly to the table instead of to a temp table?

Your second question, I do not know the answer to that. I suggest you start a new thread on it. Make sure you describe the structure of the Access files - is there a back-end, is it shared, how many front-ends are there?

Create a new table with just that one record holding the date/time, check it before the import is done and if you are going to do the import then update it (run an UPDATE query). You can name it "USys...." then it will be a hidden table.

[braff24]

Ok, thank you for the help. How do I reference the date record in the new table and compare it to the current date in VBA?

[aytee111]

- Get the date from the table:
Dim LastDate as Date, NextDate as Date
LastDate=Dlookup("DateFieldName","tablename")

- add a value to the date using DateAdd - not sure what time periods you are looking for. DateAdd can add hours or days or months, etc.
NextDate=DateAdd("?",1,LastDate)
If NextDate > Now() Then
do the import
run update query to update date on table to Now()
End If

[braff24]

Thanks!!!!!