Data not saving to database when using a save module (1 Viewer)

[davidb88]

Hi All -

I have a back end database that a number of people are working with using an Excel add-in that imports and edits records saved in the backend. Occasionally, when a user will update a record at first it saves to the backend but then an hour or two later, the changes made disappear. We have validated that after the user saves the changes, those changes are present in the backend of the database, but for some reason those changes disappear after a period of time. Has this happened to anyone else? Does anyone have any suggestions of how to remedy a problem like this?

Thanks!

 

[CJ_London]

Is it possible that a second user has the link to the table open, and after the first users makes changes and saves, the second user makes no changes but save the data, effectively restoring it to the the original value?

 

[davidb88]

That was one of the conclusions I had thought of as well. If that is the case, is there anyway to prevent this type of occurrence from happening?

 

[CJ_London]

Sorry, don't really know as I've never used Excel to edit data in Access and it will depend on how your users work - if they all need to make changes then....

Is it a case the users open a table and range up and down making changes or do they only open a record at a time? and are the records cached in Excel and Access is updated in one go?

When you say changes, do you mean they add new records or update existing ones? Or both?

What do you expect to happen if two users change a record at the same time?

Suggest you experiment (take a backup first unless you know you can easily undo), get two users to open the db, one changes and saves then the other saves. If the data reverts you know that is the problem.

You should be able to open the db read only, but then no one can make changes or you can open the table exclusive, which means the first user to open can make changes, all the others would open read only.

Another option is that before any saves are made, excel checks Access to see if the record has changed since the data was accessed and if it has a warning is given. But this all depends on how everything works. It may be that your save module has warnings turned off, so turning them back on again will solve, or at least provide a warning before a save is made.

 

[davidb88]

Thanks for your suggestions. The way the database is constructed is multiple users could be making edits to the same table at one time. Within the Excel ribbon where people are importing data from Excel I included multiple different options for imports (people can import closed accounts, open accounts, accounts only in specific states, etc.) but I have found that a number of people are using the "Import All" function that I included in the ribbon.

I hesitate to have the database open exclusively because the reason I constructed this database was to move away from shared spreadsheets and one user having the spreadsheet open for exclusive editing.

Your final suggestion though is interesting to me. Is there any sample code or links you can provide that would at least set me on the right path for being able to include some warning before the user saves that the specific record has been edited since the second user imported the account? Each account has an auto-numbered primary key so maybe is there a way to timestamp the last time an account was edited?

Thanks for your help.

 

[CJ_London]

remove the import all option!

more relevant - try linking rather than importing

re code, don't think I can help with that as such. If you link the data it should generate a warning automatically. To find some options try googling something like

'vba check if record has been changed by another user'

Although this is usually referring to access events and properties which you may not have access to in Excel.

Why don't you just have the users use a form in Access?

 

[davidb88]

What I ended up doing was adding an additional field to the end of the dataset when the user imported the table into Excel that said "Save Updates" with a drop down in each cell to identify the records that the user wanted to save. So far, it seems to be limiting records from getting saved over. At some point when I have more time though I definitely want to figure out it is possible to programmatically check if a record has been updated by the user.

Thanks for your help!

 

[gemma-the-husky]

Personally, I would stop using Excel to maintain access data.

I struggle to see what excel can do that Access can't do more easily.