January 31, 2006 at 6:01 pm
Hi,
I have an SQL Server at our main office, and an access database at a site office, which has the same table. You could say the access table is an offline version of the SQL table.
What I need to figure out is if there is any way to update the Access table based on SQL Server triggers (i.e. when records in the original table are changed).
Thanks for the help.
Dinu
January 31, 2006 at 7:05 pm
I think you'd be better off creating an ADP (access data project). Basically the data resides on sql server only, but all the manipulations are made using access. That way you don't have any replication tasks to do.
January 31, 2006 at 8:26 pm
I considered this, but from what I know, an ADP cannot store offline data right?
January 31, 2006 at 9:30 pm
Why do you need to do that?
January 31, 2006 at 10:01 pm
because the users at the sire will not always have access to the server.
February 2, 2006 at 5:53 am
Not sure if you've thought about this properly. If the site users can't connect to the head office (hence the offline Access database) how would triggers on the SQL Server tables connect to the Access database when they need to update it?
I think you should look at replication because this is just the sort of environment that would benefit. You could have replication using Access databases or SQL Server databases. I can't see any simple way of doing this, though you could write a trigger that writes any SQL database changes to a set of "bucket" tables and, when the server can connect to the Access database, it updates the necessary tables in the offline copy...but this is essentially a poor, hand-written way of implementing replication anyway
February 2, 2006 at 11:37 am
If you just need a copy of a table in an Access database, I would just copy from SQL-Server to Access (using dts if SQL-Server 2000).
Either that, or have two tables in your Access. One is linked to SQL-Server and is good when connected. The other is local and is a copy of the linked one. I'm sure a procedure (query or macro) could be developed that will delete the rows in the local table and reload it from the linked table before disconnecting.
This assumes that the table is never updated from the Access side.
Steve
February 2, 2006 at 2:20 pm
That's a very big assumption. If the tables are read-only then it's a simple process... But if it takes a 2 way replication, you got a whole lot more work to do (assuming no wizard can be used for this task).
February 6, 2006 at 1:58 am
Thanks for all the replies ppl.
The reason for the offline databases was because I work for a construction company, and usually new site offices are set up in the middle of nowhere. So at the initial stages internet connectivity is not very reliable.
Fortunately for me the management that we can ignore connectivity prolems for now and see how things go. So that saves me from all the replication stuff, at least for now.
February 8, 2006 at 12:42 am
How I can make a program with used offline using VB code to export some tables from SQL to an Access database or on the contrary
Thanks
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply