How to update backend tables not linked together

**Jim Doherty** · May 21 '10, 07:55 AM

Originally posted by MisaC

I am so hoping someone here can help me out.

I have a main Access 2007 database on a shared network that is split as a frontend and backend. As long as coworkers are in the office making changes, they use the main database and I have no problems with linking several different computers to the main database backend, but I have two laptops that are used to go from building to building. Some buildings do not have internet access, so they are not always able to be linked to the main database when using them in the field, so I have set them up to operate independently, but their databases are also split, because having the database as one will eventually exceed the 2 gig limit.

I need to make it user friendly to update the main database; of the laptops additions, changes, and deletions and then download the main database tables to replace the existing ones on the laptops; once back in the office, by hopefully using a button or maybe, if I have to, more. I tried Sharepoint, but it has limits too on how much can be stored there, so I'm unable to use it. Once again will be storing info more than 2 gig.

What my database is used for is to do chemical inventory for our main campus and four regional campuses. Each campus has different departments in which one chemical could be at each campus in each department, or it could be just at one campus in one department.

I have it set up so a UPC goes to an existing record on a form where a subform is linked to the chemical with the campus and department as combo boxes, if not it goes to a blank form so the item can be added. Once added it goes to that record on a form where the subform is linked to the chemical with the campus and department as combo boxes.

Once the chemical has been added, and we have all the information we can to obtain an MSDS, a button is pushed to look for another UPC.

Once back at the office: MSDSs are found by various methods and the Product is looked up using the database and the record is modified by adding the MSDS as an attachment in the Product table. This is done by using the main database.

My database has six tables;

Campus - (Lookup Table)
Campus - Text

Department - (Lookup Table)
DepartmentID - Autonumber
Department - Text
DateAdded - Date/Time - (Now())
User - Text
(A VBA code puts this information in when record has been changed.)
DateChanged - Date/Time
(A VBA code puts this information in when record has been changed.)

DepartmentProdu cts - (Subform Table)
DepartmentProdu ctsID - Autonumber
Campus - Text - (Lookup) and (PrimaryKey)
Department - Number - (Lookup) and (PrimaryKey)
ProductID - Number - (PrimaryKey)
DateAdded - Date/Time - (Now())

Hazards (Lookup Table)
HazardsID - Autonumber
Hazard - Text

Product (Main Form Table)
ProductUPCID - Text
ProductName - Text
ProductManufact uer - Text
ProductNetWeigh t - Text
ProductColor - Text
ProductMftPhone - Number
ProductOtherInf o - Memo
ProductMSDS - Attachment
ProductID - Autonumber (PrimaryKey)
DateAdded - Date/Time - (Now())
Contact - Text
Email - Text
WebAddress - Text
SerialNumber - Text
Type - Text
DistributorName - Text
Fax - Text
ProductCode - Text
PartNumber - Text
CatelogNumber - Text
MSDSInfo - Memo
Date1 - Date/Time
Date2 - Date/Time
Hazard - Number - (Lookup)
User - Text
(A VBA code puts this information in when record has been changed.)
DateChanged - Date/Time
(A VBA code puts this information in when record has been changed.)

Sync
SyncID - Autonumber
User - Text
(A VBA code would put this information in when sync is completed.)
DateChanged - Date/Time
(A VBA code would put this information in when sync is completed.)

Sounds very much to me that at some point in time you should read up on SQL Server. You speak of database size quickly going over 2 gig size, working in multi user networked environments, working offline with laptops independantly to the central database server and so on.

All of this is handled by SQL server 'publisher'/'subscriber' built in merge replication functionality. It is a technical crossroads for you backend wise but the features are there and available.

There are many articles on the web describing the principles behind the, 'subscriber', 'distributor' aspects of replication and it does demand a commitment from your organisation to implement and administer something like this. You mentioned 'Sharepoint' so my guess is they might well already have an SQL Server box sitting somewhere on the network with a friendly DBA willing to help you :)

Welcome to Bytes :)

How to update backend tables not linked together

How to update backend tables not linked together

Comment