Synchronized Tables?

[traceytr]

Hello Group.

We have an Access application designed by a power-user who left the company last year. The same .mdb is being used by up to ten users from a network drive, so the data becomes corrupt quite frequently. The decision was made that instead of rewriting the app in VB6 we would keep the Access frontend and use the Upsizing Wizard to create the SQL Server backend. After Upsizing, the relationships that existed in the local tables do not exist in the linked SQL tables, so we recreated them as closely as we could. There were some relationships we could not recreate in Access on the SQL Server tables.

I've been told I need to create these relationships in SQL Server, and I have not done that yet. I'm kind of hung up on the following issue.

The original designer created a form with a subform, and within that subform another subform... Because certain data relationships were lost, this totally doesn't work correctly. When I change the data source back to the local tables, it works again. When I do that, though, the data changed or added in the form only populates the local tables, not the table on SQL Server.

If I could append and update records in the SQL Server table every time a record is added or changed in the local table, then the SQL Server database would be up to date, and at the same time, the Access frontend would work with the old local table relationships. I've looked for triggers, but I don't see anything like that.

I've done some programming with Access queries and reports, but nothing at all with forms. I need all the advice I can get. Thanks.


Tracey

 

[willir]

Tracey

Sorry to hear you were having a problem with corruption of your database. I was surprized to see that you were experiencing this corruption with 10 users -- personally, I use databases on the network with 30 and 40+ users without corruption, so it would have been nice to resolve your initial problem you were experiencing on corruption.

Now you have upgraded....
You should always write to the SQL database rather than update a local table then update the main (SQL) database. Why? Several reasons which all tie back to data integrity. You can not enforce integrity rules dictated by the SQL backend on the local database.

Also, Access does not support triggers.

You can code the populate / repopulate process of the subform yourself. After querying the record on the main form, run a query for the subform using the foreign key used to link the data in the main and subforms. There are examples of coding for this in the Access forums. A simple thing to look at would be "cascading cobmo boxes" and apply the same strategy for the subform.

Richard