How to create a separate local database for temporary tables (1 Viewer)

sra2786

New member
Local time
Today, 04:49
Joined
Sep 3, 2019
Messages
4
I am fairly new to Access. I have split my database because we have multiple users. Only two users can edit data and the rest can just displaythe data. I want the "edit users" to be able to Compact/Repair the database. To allow this, I have created local tables on the front-end and delete/insert data from the back-end database to the local tables on the front-end. This way the "display users" do not have a lock on the back-end database.

I have read that deleting/inserting data into a table will cause database bloat. They recommend creating a separate local database that will be deleted and created each time.

I do not understand how to do this. Do I have to use VBA code to recreate the local tables in the temporary database? I have searched for examples on how to do this but I could not find any.

Can someone please give me some code example for me to get started?

Thanks.
Sandy
 

moke123

AWF VIP
Local time
Today, 07:49
Joined
Jan 11, 2013
Messages
3,852
Not sure I follow or agree with your process but heres an example database which creates a temp database and adds template tables.
 

Attachments

  • ExampleTempDatabase.zip
    57.5 KB · Views: 103

sra2786

New member
Local time
Today, 04:49
Joined
Sep 3, 2019
Messages
4
Since creating an Access database for multiple users is new to me, can you suggest a better method to allow users to display data but also allow someone to Compact/Repair the database on a daily basis.

Is there something I should check to see why displaying the data in a form would put a lock on the back-end database?

We had one incident of a corrupt database and I just thought running Compact/Repair on a daily basis would help reduce the risk of a corrupt database.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:49
Joined
Jan 20, 2009
Messages
12,849
Temporary tables in this situation is not as simple as you might think and can introduce more problems than it solves. If your two editing users (or more sometime down the track) both extract data into temporary tables they have no awareness of what changes the other has made and can easily overwrite each others edits.

Access intrinsically manages this quite well with a process called Optimistic Locking. Basically it (optimistically) hopes nobody else changes it and deals with it if they do by keeping a copy of the old record and checking for any changes to the master record before the update is applied. The user gets a warning if the record has changed.

I would strongly advise you not to pursue the temporary table approach and stick to using bound forms. It will make your life much easier too.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:49
Joined
Jan 20, 2009
Messages
12,849
Your back end database should not need to be compacted every day. If you got corruption I would be looking for other reasons than having two users editing. Access is meant to handle that and a lot worse routinely.

BTW Many developers don't delete records but mark them as deleted so there is nothing to compact anyway. If you are editing big text field all the time there could be a reason to compact more regularly but it should not be considered a routine process.

Get everyone out and Compact, after taking a backup, just in case, if you really care about the data.
 

moke123

AWF VIP
Local time
Today, 07:49
Joined
Jan 11, 2013
Messages
3,852
I would be more concerned with finding the source of the corruption and perhaps automating backups of you backend. You can easily replace a corrupt front end but a corrupt backend can be a disaster.
 

moke123

AWF VIP
Local time
Today, 07:49
Joined
Jan 11, 2013
Messages
3,852
Full disclosure: a lot of the code in the example I posted came from TheDBGuys temp database demo :eek:
 

sra2786

New member
Local time
Today, 04:49
Joined
Sep 3, 2019
Messages
4
Thank you for all the very helpful replies. Based on what everyone wrote, I will not use temporary tables. I think the best option is to let users know that during certain days/time the database will not be available for 15 minutes so some Admin tasks (ie Compact/Repair) can be executed. We do have a nightly job that bacups the back-end database.

Thanks again for all of your help.
Sandy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:49
Joined
Oct 29, 2018
Messages
21,358
Full disclosure: a lot of the code in the example I posted came from TheDBGuys temp database demo :eek:
Hi moke. Thanks for posting your demo. Ever since the website owner moved the files to a Linux server, the page to download the tempdb demo has been broken, and I don't know how to fix it (since I don't have access to the server).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 28, 2001
Messages
27,001
I think the best option is to let users know that during certain days/time the database will not be available for 15 minutes so some Admin tasks (ie Compact/Repair) can be executed.

I came into this discussion late, but that is what I would have recommended. I did this for my U.S. Navy database. We polled our users (and since I kept log files, looked at usage logs) to find an optimum time for this activity to occur twice monthly for up to one hour at a time. When it becomes a regularly scheduled item, nobody complains. They all had other stuff to do anyway.
 

moke123

AWF VIP
Local time
Today, 07:49
Joined
Jan 11, 2013
Messages
3,852
Someone had an interesting procedure for this. I thought it was isladogs but didn't see it on his site.
It entailed placing a text file in the application folder named "LockMe" or something like that.
When starting the app it checks for the presence of the text file and if present fires a message box and quits. I think there was also a timer event that would also fire a message box to save your work and get out.
 

isladogs

MVP / VIP
Local time
Today, 11:49
Joined
Jan 14, 2017
Messages
18,186
Hi moke
You may have been thinking of this app, written by David Crake but updated by me, which does indeed use a locked.txt file to block access to an app.
http://www.mendipdatasystems.co.uk/logged-in-users/4594398123

My usual method in other apps is to have a tblKickout table in the BE with one boolean field Kickout and one record. The table also acts as a persistent connection with the field value being checked every 30 seconds. If the program admin sets the field true, a 5 minute countdown starts warning users to save work and exit. New users are unable to start using the app if kickout =true.

For completeness, I should mention I also use a detect idle time approach to close apps after say 20 minutes of inactivity.

Unable to post code or find links for those approaches at the moment. My power supply is being switched off soon for a few hours to enable maintenance work in the area. In other words, I'm on the receiving end of a kickout routine! :mad:
 

Users who are viewing this thread

Top Bottom