Thread: Shadow tables in Access 2007
05-01-10, 01:59 #1Registered User
- Join Date
- Apr 2010
Unanswered: Shadow tables in Access 2007
I am a Peace Corps Volunteer in Ethiopia helping a hospital implement their first computer application, tracking their patients. The biggest problem is finding a returning patient if they do not have their Medical Record Number with them. An Access 2007 database is being built to do this. they have limited resources. A used desk top computer has been found to do this. The power is very unreliable here. I am pushing them to get a laptop with at least 2 spare batteriies to give them at least 8 hours without power. I am trying to design the database to have real time "shadow" tables on an external USB drive. What is the easiest way to write the data to both tables. I understand there is a potential of having a hiccup that will cause the tables to be out of synd, but I think that potential is less than the problems they would have with only periodic backups. If they do get at least 8 hours of backup power, it may be less important to have the shadow tables. I have been looking at alternatives for writing the shadow tables, what is the simplest and most reliable way to write to a second/shadow table as I write to the primary table?
05-01-10, 02:55 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
Have you considered just getting a simple router to connect any of the computers together? You don't necessarily need a router just for connecting to the internet but could use it just to configure connecting 2 or more computers together, making one of them the 'central' computer which houses your mdb (ie. the desktop computer). Then you could easily just share the desktop harddrive and plug in a laptop to the router (or possibly utilize a wireless type connection) to then see the harddrive on the desktop and open the mdb.
Depending on the complexity of this database, if you try to make it so the data tables are ALSO 'lively' maintained on a USB key, you can end up spending most of your time just maintaing the data between the harddrive and usb to keep accurate records.
I would avoid putting the actual live mdb on a USB except as a backup copy. I might instead write a routine to export the mdb to a USB (again, as a backup) and then import back to the laptop in an emergency situation or possibly export just the key data tables to the USB key to then be imported on another laptop versus trying to 'shadow' the data tables actively themselves as a live type mdb to be synched with 2 different computers (just avoid MSAccess replication!!) If you write an export type routine of the data tables (ie. to excel or csv file), you can then control in an importing routine the overwriting of data which may have been updated on a 2nd laptop.
If you decide to export/import the actual mdb with the data tables to a USB, you could write a simple vb type script to automatically do the copying of the mdb onto the USB and again, use it as a backup only to be 'restored' so to speak when needed. Trying to keep a 'lively' synchranized mdb onto a USB though would be highly unadvisable. I would instead go the route of an export/import routine of the data tables to a csv and write some very good queries to import the data so nothing is over-written which shouldn't be.
I'd personally go the router type method though and share the desktop harddive and mdb file.
Last edited by pkstormy; 05-01-10 at 03:06.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
05-01-10, 07:04 #3Moderator
Provided Answers: 14
- Join Date
- Mar 2009
If you application is quite simple, you can work with unbound forms and encapsulate every "Write" operation into a transaction. Each "Write" operation (INSERT or UPDATE) would be performed on two sets of tables, one in an mdb (or accdb) file located on the hard disc of the computer and the other set located on the usb drive. This would slow down the application, though.
You could also consider to replace the back-end mdb (or accdb) file by a SQL server which has several method for insuring data integrity. MSDE (for SQL 2000) or SQL Server 2005 Express Edition (for SQL 2005) are free editions that you can download from Microsoft. Both can be installed on the client machine (no need for a dedicated server) and are not too exigent in ressources.Have a nice day!