TableDefs and QueryDefs extremely slow in replicated dataset
I'm using a split database with one client and multiple data files, ocx's dll's etc etc.
The complete set of client and data is approx 160mb. From this I manage an additional 20GB of data (not stored inside access)
One of the data files contains replicated tables only, and is approximatly 60mb.
All databases are in Access2003 format.
Periodically when I try to attach tables or access the tabledefs collection (or similar with he querydefs collection) from my client, it takes several seconds to refresh or add a queydef/tabledef. Usually the same action is instantaneous. My client uses about 50 tables spread across the data files, which it links to once only during startup. Usually the linking process takes about one second, but can blow out to several minutes.
Also the problem seems to be exacerbated by the synchronisation process.
Why would it work so fast MOST of the time, then slow to an absolute crawl others???
If I create an empty database file and save as DM1 then import all the data from the original design master into DM1
THEN create ANOTHER empty database file, save as DM2 then import all the data from DM1
Then If I promote DM2 to be the new design master and recreate the replica set, then repeat this double importing process for all my other data files, the system runs the way it used to (before the upgrade to 2003) and will work fast for a few days, then fall in a heap again.
Also the client and data files are issued to mutltiple users at various sites.
Compacting the databases doesn't help. Defrag HDDs doesn't help.
Only recreating a complete new set of mdb's temporarily fixes the problem.
Synchronising the replicated data file seems to exacerbate the problem.
Shutting down the client and re-opening the client after having it invoke the syncronisation of the replicated data file, sometimes temporarily aleviates the problem
Dazed and confused...
Any suggestions???
The complete set of client and data is approx 160mb. From this I manage an additional 20GB of data (not stored inside access)
One of the data files contains replicated tables only, and is approximatly 60mb.
All databases are in Access2003 format.
Periodically when I try to attach tables or access the tabledefs collection (or similar with he querydefs collection) from my client, it takes several seconds to refresh or add a queydef/tabledef. Usually the same action is instantaneous. My client uses about 50 tables spread across the data files, which it links to once only during startup. Usually the linking process takes about one second, but can blow out to several minutes.
Also the problem seems to be exacerbated by the synchronisation process.
Why would it work so fast MOST of the time, then slow to an absolute crawl others???
If I create an empty database file and save as DM1 then import all the data from the original design master into DM1
THEN create ANOTHER empty database file, save as DM2 then import all the data from DM1
Then If I promote DM2 to be the new design master and recreate the replica set, then repeat this double importing process for all my other data files, the system runs the way it used to (before the upgrade to 2003) and will work fast for a few days, then fall in a heap again.
Also the client and data files are issued to mutltiple users at various sites.
Compacting the databases doesn't help. Defrag HDDs doesn't help.
Only recreating a complete new set of mdb's temporarily fixes the problem.
Synchronising the replicated data file seems to exacerbate the problem.
Shutting down the client and re-opening the client after having it invoke the syncronisation of the replicated data file, sometimes temporarily aleviates the problem
Dazed and confused...
Any suggestions???
Hi PuppyDogBuddy,
Thankyou for your comments. Please understand I appreciate your comments, and my response is not intended to flame you in anyway (All directed at MS not you)
We had to move away from the microsoft synchronization tools about five years ago because of *severe* performance limitations and I have since written my own C++ programs (ocx/dll) to syncronize my data. As I mentioned in my original post, we manage about 20gb and > 30,000 associated disk files which is completely out of the scope of the briefcase replication system. We also synchronize this over the internet, including images, documents, using file compression and archiving etc. The MS conflict resolution tools where completely out of their league for our conflicts, and I have since written my own algorithms and UI.
The client attaches to approx 180 normalised tables and approx 500 queries.
***
The only tables left being synchronized by access are legacy static lookup tables which haven't been converted over to version controlled managed tables.
In answer to your actual post, sorry no conflict tables where being created - already checked that, and I hadn't made any design changes or record changes. I was testing this with as simple a sync as can be, but caput MS craps itself.
***
'Attach to all user tables
Private Function AttachTablesFromDB(ByRef ctl As Control, ByRef dbRemote As Database, DataSource As String) As Boolean
On Error GoTo ErrHndlr
Dim local_tdf As TableDef
Dim I As Integer
Dim j As Integer
Dim tblName As String
Dim attrib As Long
Dim connect_string As String
Dim dbLocal As Database
Set dbLocal = CurrentDb
AttachTablesFromDB = False
connect_string = ";DATABASE=" & DataSource
j = dbRemote.TableDefs.count
'Iterate until no user tables remain
Do While (I < j)
tblName = dbRemote.TableDefs(I).Name
attrib = dbRemote.TableDefs(I).Attr
If (attrib = 0) Then
ctl = tblName
Set local_tdf = dbLocal.CreateTableDef(tbl
local_tdf.Connect = connect_string
local_tdf.SourceTableName = tblName
'### This is the problem line which slows it to a crawl ###
dbLocal.TableDefs.Append local_tdf
'###
End If
I = I + 1
DoEvents
Loop
AttachTablesFromDB = True
Exit Function
ErrHndlr:
MsgBox "Error attaching table " & tblName, , "Error"
End Function
***
In my test case I hadn't created any subsequents replicas yet.
But a question though, what are the 'rules' regarding copying the replicas around.
Ie: Use DM to create R1 then copy R1 to R2 on another machine
When synching R1 against DM and synching R2 against DM ???
Still dazed and confused
Kill Bill.
Thankyou for your comments. Please understand I appreciate your comments, and my response is not intended to flame you in anyway (All directed at MS not you)
We had to move away from the microsoft synchronization tools about five years ago because of *severe* performance limitations and I have since written my own C++ programs (ocx/dll) to syncronize my data. As I mentioned in my original post, we manage about 20gb and > 30,000 associated disk files which is completely out of the scope of the briefcase replication system. We also synchronize this over the internet, including images, documents, using file compression and archiving etc. The MS conflict resolution tools where completely out of their league for our conflicts, and I have since written my own algorithms and UI.
The client attaches to approx 180 normalised tables and approx 500 queries.
***
The only tables left being synchronized by access are legacy static lookup tables which haven't been converted over to version controlled managed tables.
In answer to your actual post, sorry no conflict tables where being created - already checked that, and I hadn't made any design changes or record changes. I was testing this with as simple a sync as can be, but caput MS craps itself.
***
'Attach to all user tables
Private Function AttachTablesFromDB(ByRef ctl As Control, ByRef dbRemote As Database, DataSource As String) As Boolean
On Error GoTo ErrHndlr
Dim local_tdf As TableDef
Dim I As Integer
Dim j As Integer
Dim tblName As String
Dim attrib As Long
Dim connect_string As String
Dim dbLocal As Database
Set dbLocal = CurrentDb
AttachTablesFromDB = False
connect_string = ";DATABASE=" & DataSource
j = dbRemote.TableDefs.count
'Iterate until no user tables remain
Do While (I < j)
tblName = dbRemote.TableDefs(I).Name
attrib = dbRemote.TableDefs(I).Attr
If (attrib = 0) Then
ctl = tblName
Set local_tdf = dbLocal.CreateTableDef(tbl
local_tdf.Connect = connect_string
local_tdf.SourceTableName = tblName
'### This is the problem line which slows it to a crawl ###
dbLocal.TableDefs.Append local_tdf
'###
End If
I = I + 1
DoEvents
Loop
AttachTablesFromDB = True
Exit Function
ErrHndlr:
MsgBox "Error attaching table " & tblName, , "Error"
End Function
***
In my test case I hadn't created any subsequents replicas yet.
But a question though, what are the 'rules' regarding copying the replicas around.
Ie: Use DM to create R1 then copy R1 to R2 on another machine
When synching R1 against DM and synching R2 against DM ???
Still dazed and confused
Kill Bill.
I can surely sympathise with you. The only suggestions I can think of are:
1. My reference sources say the following: A table can not be designated as local if it is involved in a relationship with a replicated table. Did you follow this guideline???
2. See if this link has anything that might help:
http://www.trigeminal.com/resources.asp?1033
PDB
1. My reference sources say the following: A table can not be designated as local if it is involved in a relationship with a replicated table. Did you follow this guideline???
2. See if this link has anything that might help:
http://www.trigeminal.com/resources.asp?1033
PDB
Thanks PDB, had a look at the URL, lots of info there but nothing I could see directly related.
I had also removed all the relationships already (thought of that too!!!)
All I can put it down to is bugs in MS. What???
What are your thoughts on trying to use ADO rather than DAO to invoke the sync and tabledef links?
I had also removed all the relationships already (thought of that too!!!)
All I can put it down to is bugs in MS. What???
What are your thoughts on trying to use ADO rather than DAO to invoke the sync and tabledef links?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This problem has never really been resolved but I appreciate your contribution.
Thanks
Thanks
Thanks for the points and grade. Sorry, you could not resolve your problem.
I recentlyfound the following detailed HowTo/Tricks/Traps for replication and synchronization on a public forum. You should look it over and see if maybe there is something in it that may help with your problem.
__________________________
Posted: Fri Jan 09, 2004 3:50 am Post subject: Tip: MS Access - Database Replication
Tip: MS Access DB Replication:
This is perhaps a little used tool within Access that can save A LOT of time and frustration when managing data updates across multiple instances of the same Access database (DB). I myself just figured it out last week...so I thought it would be good to share. Plus, there isn't very much documention online or in the help about this function.
What is replication? A built-in tool that automates the synchronization of multiple instances of the same Access database. No need for import, export or even update queries! Once setup...it is a simple click to synchronize your records across your DB instances.
The fastest way to describe why you would use replication is by example:
You created an Access database and you want (or already have) 2 or more computers to have the same DB. Both are updated independently of each other, and you need to somehow synchronize the updated records so all instances of the DB have the same up-to-date data and structure. This is where you need replication (it's easy...don't worry).
Note: Read the entire post BEFORE starting the process on a database you care about. Guys, this is one instruction manual you'll have to read. Extra parts in the end mean trouble.
Note: The different instances of the Access database do NOT need to be connected via a network for you to be able to use replication and synchronize! Each DB is independent of the other...it is only when you synchronize that you need to somehow connect them. Keep reading for instructions on synchronizing with or without a network. Of course multiple replicas can also reside on the same computer.
Note: In order for a database to be "synchronizable" it must first be converted to a "replicated" database. The process is completely automated and you (or other users) really won't know the difference when it is done. You will have a chance during the setup process to backup your current DB as it is. So no worries about data loss.
INSTRUCTIONS:
Creating a Replicated Database (SETUP):
1. Open the Access DB you want to replicate (create multiple instances of and enable synchronization for)
2. Tools -> Replication -> Create Replica (You will be prompted to close the DB. Click Yes) --- NOTE: This DB will become the "MASTER" (I'll explain more later)
3. CLICK YES!!!! - This is where you create that important backup. If you don't like replication...you always have the backup. Once a DB is converted...it CANNOT be undone (without major hairpulling and lots of tears).
Note: Your backup copy will be placed in the same folder the original was in before you converted, and is given the extension "bak". To start using this backup DB...just change the extension back to "mdb". (You may need to change your Folder options [Tools menu] to "show extensions" in order to do this.)
4. Select the location and name of the NEW REPLICA (this will be a "Child" instance of the Master (your original DB) that you just converted). You can put it anywhere and call it anything you want. If you are on a network, put it in the place you want it reside so you don't have to move it later.
Note: Notice the check box in the dialog box - "prevent deletes". By checking this box you will prevent this "child"/replica database from deleting records. If this box is checked this "replica" DB can only modify and add records. The Master DB can do anything it wants with records...the check box will only limit deletes in the replica.
5. You have now created 2 instances of the same database and both can accept synchronization.
6. Both databases are now ready for use. Put the "child"/replica wherever you want it. If you are on a network, copy it to its home on the network. If not, copy it to a CD or other media...and move it to another computer by pasting it from the CD into its location.
7. Fire them up...both instances are ready for action. All instances are independent of each other...and do NOT require any communication between them until you want to synchronize. How hard was that!? (OK I'm assuming it worked)
INSTRUCTIONS:
Synchronizing Replicated Databases:
1. To synchronize your Master & replica (or replica with another replica) the two must be able to see each other. If you are on a network...skip to step 4
2. Synchronizing w/o a network between computers isn't that difficult, it just requires a little copy/pasting between computers using a CD or other media.
3. Copy one of the databases to a CD -> paste it to a place on the computer that has the replica (or master). Follow steps 4 thru 6. Then copy the replica back onto the CD and overwrite the old replica on the other computer.
4. Open either the replica or master DB.
5. Tools -> Replication -> Synchonize Now
6. Click BROWSE and select the target DB -> Click OK (for some reason you must browse and select your target EVERY TIME you synchronize). Allow the DB to close so it can synchronize...it will reopen when complete.
Note: DO NOT CHECK THE BOX on the synchronize dialog, unless you want to swap which database is the Master of the set. You can only have one master DB. I haven't tested checking the box...so I can't help you there.
Finished! Both DBs are now synchronized. Any structural changes you made in the master have been propogated...and all data changes from both DBs have been updated to each other. If any conflicts are detected, you will be promted to select the changes to accept.
Now for a little explanation:
a) Only the "Master" DB can modify the actual structure of the replicated databases. This means that the "child" DBs cannot modify the relationships, delete/add tables, fields, queries, etc... So to make changes to the actual database, you must do it in the Master. When you synchronize, these changes will be propogated to its replicas.
b) Autonumber fields are NOT sequential in a replicated database. Instead random numbers are used in these fields. This is done to reduce the chance that 2 new records will have the same value. This cannot be changed.
c) You will notice that the icons have changed for the objects within the database. All of the objects (tables, queries, etc) have a yellow, double arrow on them. This means that they are watching for changes and will be synchronized. You can stop an object from participating in synchronization by right-clicking it -> properties -> uncheck the "replicable" checkbox.
d) How synchronization works: When the DB was converted to handle replication, several things were modified "behind-the-scenes" to enable the DB to track changes. For instance, the table "MSysCompactError" was added. These changes provide the functionality for synchronization and allow only the actual data/structure changes to be synchronized...not the entire database. Don't mess with the tables you didn't create (child/replica DB's can't mess with them so don't worry about that).
e) You will see a difference in file size between the Master/Replica. The Master is now larger than the original database was, and the child is now much smaller than the original. I don't know how the child got smaller...but it contains all the info that is in the master. If you have a clue, fill me in.
Creating multiple replicas:
You can create new replicas from the Master or any other existing replica.
Tools -> Replication -> Create Replica
Same procedure as listed above
Creating partial replicas:
Partial replicas allow you to specify only certain objects to participate in the synchronization. I won't get into details of that now. Test it out or post a question.
Optional Exercise: (If you are feeling adventurous [this might only work with Windows/Access XP...try it out anyway])
Browse to a folder containing an access database
Right Click -> New -> Briefcase
Drag and drop the Access DB into the briefcase
You will prompted to create a replica. Neat!
but....If you can figure out the rest and make it work...good luck It is strongly recommended that you go through the replication process WITHIN Access...and not use the briefcase method. I have had only problems when trying to figure out that darn briefcase with Access.
It does work great for synchronizing word/excel documents though. Especially if you are synchronizing between a laptop & PC all the time. Just create a briefcase folder on your laptop...drop your files in there...use the briefcase files on your laptop...click synchronize when you connect to your PC...and bingo both the laptop & PC files are synchronized.
Here is an MSDN article on replication: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/dbrepjet.asp
Posted: Fri Jun 04, 2004 8:48 am Post subject:
--------------------------
Mike, thanks for the tip. I think this will work very nicely when having a database that is stored at work, yet some work needs to be done at home. This will eliminate having to remember what was changed and what wasn't the night before! Thanks.
Posted: Fri Jun 04, 2004 2:27 pm Post subject: More Synchronization Tips
Peggy,
PLEASE REMEMBER TO MAKE A COMPLETE BACKUP OF YOUR DATABASE BEFORE ATTEMPTING REPLICATION!
I can't say it enough - if you're going to create a replicated database from a production DB - back it up, and read ALL of the above instructions twice before starting. It's not hard to do - but it can a disaster if you just start clicking things willy nilly (you can't 'undo' the replication process). Feel free to call or email if you have questions during the process.
Replication is definately under utilized. It is very powerful and simple (once you get over the initial setup and minor learning curve). I didn't know anything about it until someone asked how to keep 2 instances of a database synchronized. In the past I just created update queries. Now - a little planning goes a long way. It makes it easy to allow multiple users to work on unique, connected or disconnected instances of the same database.
It's a bit odd that you can take 2 databases courses that focus a great deal on MS Access - and not once was replication EVER mentioned. What's that about?!
Here are a couple more synchronization tips:
-) If you have a number of systems that you want to keep synchronized - "Second Copy" is the biznatch. It is my newest favorite software (a couple months ago anyway). It works slick! There is a trial version online - and to purchase is extremely cheap for what it does.
-) If you're using XP, you might want to try using the Briefcase functionality (for Word documents only - I don't know if I trust it with anything else). It's certainly a bit awkward to figure out - but it is an option. It was designed for the purpose of keeping your laptop & desktop files synchronized. How well it works - I don't think any knows (no one really uses it). Essentially the "Briefcase" is a specialized folder that has functionality to keep files synchronized across multiple machines or on a network. Again - good luck figuring it out for anything other than word documents.
Posted: Wed Jan 05, 2005 12:48 pm Post subject:
Mike,
Have you noted any performance issues during synchronization? I am specifically concerned about automated synchronization when the master DB is on a server that lives on our LAN and the "child" or replicated DB is on a laptop connected via VPN. Can you suggest a mechanism to see if the Master DB has changed then activate a synchronization based upon the status returned?
THX
_________________
-Mike
Posted: Wed Jan 05, 2005 5:55 pm Post subject: Reply for original post
I can't believe how smoothly this replication process works. I will remember this tool when I need it.
Jim
I haven't setup an automated synchronization routine so I am unsure of any performance issues.
I was unable to find any info on programatically determining if a database is "dirty". I will keep an eye open for a solution.
Here is a thread that might be something::
http://www.tech-archive.net/Archive/Access/microsoft.public.access.replication/2004-05/
I-------------------------
I have been using the Replicated Database for over a year between the Master database at my office and the databases at ALASD. This is the slickest process ever. I also synchronize with my external harddrive that simulates my network when I am out of the office.
Kudos to MIKE once again, for making our lives a little easier!
I recentlyfound the following detailed HowTo/Tricks/Traps for replication and synchronization on a public forum. You should look it over and see if maybe there is something in it that may help with your problem.
__________________________
Posted: Fri Jan 09, 2004 3:50 am Post subject: Tip: MS Access - Database Replication
Tip: MS Access DB Replication:
This is perhaps a little used tool within Access that can save A LOT of time and frustration when managing data updates across multiple instances of the same Access database (DB). I myself just figured it out last week...so I thought it would be good to share. Plus, there isn't very much documention online or in the help about this function.
What is replication? A built-in tool that automates the synchronization of multiple instances of the same Access database. No need for import, export or even update queries! Once setup...it is a simple click to synchronize your records across your DB instances.
The fastest way to describe why you would use replication is by example:
You created an Access database and you want (or already have) 2 or more computers to have the same DB. Both are updated independently of each other, and you need to somehow synchronize the updated records so all instances of the DB have the same up-to-date data and structure. This is where you need replication (it's easy...don't worry).
Note: Read the entire post BEFORE starting the process on a database you care about. Guys, this is one instruction manual you'll have to read. Extra parts in the end mean trouble.
Note: The different instances of the Access database do NOT need to be connected via a network for you to be able to use replication and synchronize! Each DB is independent of the other...it is only when you synchronize that you need to somehow connect them. Keep reading for instructions on synchronizing with or without a network. Of course multiple replicas can also reside on the same computer.
Note: In order for a database to be "synchronizable" it must first be converted to a "replicated" database. The process is completely automated and you (or other users) really won't know the difference when it is done. You will have a chance during the setup process to backup your current DB as it is. So no worries about data loss.
INSTRUCTIONS:
Creating a Replicated Database (SETUP):
1. Open the Access DB you want to replicate (create multiple instances of and enable synchronization for)
2. Tools -> Replication -> Create Replica (You will be prompted to close the DB. Click Yes) --- NOTE: This DB will become the "MASTER" (I'll explain more later)
3. CLICK YES!!!! - This is where you create that important backup. If you don't like replication...you always have the backup. Once a DB is converted...it CANNOT be undone (without major hairpulling and lots of tears).
Note: Your backup copy will be placed in the same folder the original was in before you converted, and is given the extension "bak". To start using this backup DB...just change the extension back to "mdb". (You may need to change your Folder options [Tools menu] to "show extensions" in order to do this.)
4. Select the location and name of the NEW REPLICA (this will be a "Child" instance of the Master (your original DB) that you just converted). You can put it anywhere and call it anything you want. If you are on a network, put it in the place you want it reside so you don't have to move it later.
Note: Notice the check box in the dialog box - "prevent deletes". By checking this box you will prevent this "child"/replica database from deleting records. If this box is checked this "replica" DB can only modify and add records. The Master DB can do anything it wants with records...the check box will only limit deletes in the replica.
5. You have now created 2 instances of the same database and both can accept synchronization.
6. Both databases are now ready for use. Put the "child"/replica wherever you want it. If you are on a network, copy it to its home on the network. If not, copy it to a CD or other media...and move it to another computer by pasting it from the CD into its location.
7. Fire them up...both instances are ready for action. All instances are independent of each other...and do NOT require any communication between them until you want to synchronize. How hard was that!? (OK I'm assuming it worked)
INSTRUCTIONS:
Synchronizing Replicated Databases:
1. To synchronize your Master & replica (or replica with another replica) the two must be able to see each other. If you are on a network...skip to step 4
2. Synchronizing w/o a network between computers isn't that difficult, it just requires a little copy/pasting between computers using a CD or other media.
3. Copy one of the databases to a CD -> paste it to a place on the computer that has the replica (or master). Follow steps 4 thru 6. Then copy the replica back onto the CD and overwrite the old replica on the other computer.
4. Open either the replica or master DB.
5. Tools -> Replication -> Synchonize Now
6. Click BROWSE and select the target DB -> Click OK (for some reason you must browse and select your target EVERY TIME you synchronize). Allow the DB to close so it can synchronize...it will reopen when complete.
Note: DO NOT CHECK THE BOX on the synchronize dialog, unless you want to swap which database is the Master of the set. You can only have one master DB. I haven't tested checking the box...so I can't help you there.
Finished! Both DBs are now synchronized. Any structural changes you made in the master have been propogated...and all data changes from both DBs have been updated to each other. If any conflicts are detected, you will be promted to select the changes to accept.
Now for a little explanation:
a) Only the "Master" DB can modify the actual structure of the replicated databases. This means that the "child" DBs cannot modify the relationships, delete/add tables, fields, queries, etc... So to make changes to the actual database, you must do it in the Master. When you synchronize, these changes will be propogated to its replicas.
b) Autonumber fields are NOT sequential in a replicated database. Instead random numbers are used in these fields. This is done to reduce the chance that 2 new records will have the same value. This cannot be changed.
c) You will notice that the icons have changed for the objects within the database. All of the objects (tables, queries, etc) have a yellow, double arrow on them. This means that they are watching for changes and will be synchronized. You can stop an object from participating in synchronization by right-clicking it -> properties -> uncheck the "replicable" checkbox.
d) How synchronization works: When the DB was converted to handle replication, several things were modified "behind-the-scenes" to enable the DB to track changes. For instance, the table "MSysCompactError" was added. These changes provide the functionality for synchronization and allow only the actual data/structure changes to be synchronized...not the entire database. Don't mess with the tables you didn't create (child/replica DB's can't mess with them so don't worry about that).
e) You will see a difference in file size between the Master/Replica. The Master is now larger than the original database was, and the child is now much smaller than the original. I don't know how the child got smaller...but it contains all the info that is in the master. If you have a clue, fill me in.
Creating multiple replicas:
You can create new replicas from the Master or any other existing replica.
Tools -> Replication -> Create Replica
Same procedure as listed above
Creating partial replicas:
Partial replicas allow you to specify only certain objects to participate in the synchronization. I won't get into details of that now. Test it out or post a question.
Optional Exercise: (If you are feeling adventurous [this might only work with Windows/Access XP...try it out anyway])
Browse to a folder containing an access database
Right Click -> New -> Briefcase
Drag and drop the Access DB into the briefcase
You will prompted to create a replica. Neat!
but....If you can figure out the rest and make it work...good luck It is strongly recommended that you go through the replication process WITHIN Access...and not use the briefcase method. I have had only problems when trying to figure out that darn briefcase with Access.
It does work great for synchronizing word/excel documents though. Especially if you are synchronizing between a laptop & PC all the time. Just create a briefcase folder on your laptop...drop your files in there...use the briefcase files on your laptop...click synchronize when you connect to your PC...and bingo both the laptop & PC files are synchronized.
Here is an MSDN article on replication: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/dbrepjet.asp
Posted: Fri Jun 04, 2004 8:48 am Post subject:
--------------------------
Mike, thanks for the tip. I think this will work very nicely when having a database that is stored at work, yet some work needs to be done at home. This will eliminate having to remember what was changed and what wasn't the night before! Thanks.
Posted: Fri Jun 04, 2004 2:27 pm Post subject: More Synchronization Tips
Peggy,
PLEASE REMEMBER TO MAKE A COMPLETE BACKUP OF YOUR DATABASE BEFORE ATTEMPTING REPLICATION!
I can't say it enough - if you're going to create a replicated database from a production DB - back it up, and read ALL of the above instructions twice before starting. It's not hard to do - but it can a disaster if you just start clicking things willy nilly (you can't 'undo' the replication process). Feel free to call or email if you have questions during the process.
Replication is definately under utilized. It is very powerful and simple (once you get over the initial setup and minor learning curve). I didn't know anything about it until someone asked how to keep 2 instances of a database synchronized. In the past I just created update queries. Now - a little planning goes a long way. It makes it easy to allow multiple users to work on unique, connected or disconnected instances of the same database.
It's a bit odd that you can take 2 databases courses that focus a great deal on MS Access - and not once was replication EVER mentioned. What's that about?!
Here are a couple more synchronization tips:
-) If you have a number of systems that you want to keep synchronized - "Second Copy" is the biznatch. It is my newest favorite software (a couple months ago anyway). It works slick! There is a trial version online - and to purchase is extremely cheap for what it does.
-) If you're using XP, you might want to try using the Briefcase functionality (for Word documents only - I don't know if I trust it with anything else). It's certainly a bit awkward to figure out - but it is an option. It was designed for the purpose of keeping your laptop & desktop files synchronized. How well it works - I don't think any knows (no one really uses it). Essentially the "Briefcase" is a specialized folder that has functionality to keep files synchronized across multiple machines or on a network. Again - good luck figuring it out for anything other than word documents.
Posted: Wed Jan 05, 2005 12:48 pm Post subject:
Mike,
Have you noted any performance issues during synchronization? I am specifically concerned about automated synchronization when the master DB is on a server that lives on our LAN and the "child" or replicated DB is on a laptop connected via VPN. Can you suggest a mechanism to see if the Master DB has changed then activate a synchronization based upon the status returned?
THX
_________________
-Mike
Posted: Wed Jan 05, 2005 5:55 pm Post subject: Reply for original post
I can't believe how smoothly this replication process works. I will remember this tool when I need it.
Jim
I haven't setup an automated synchronization routine so I am unsure of any performance issues.
I was unable to find any info on programatically determining if a database is "dirty". I will keep an eye open for a solution.
Here is a thread that might be something::
http://www.tech-archive.net/Archive/Access/microsoft.public.access.replication/2004-05/
I-------------------------
I have been using the Replicated Database for over a year between the Master database at my office and the databases at ALASD. This is the slickest process ever. I also synchronize with my external harddrive that simulates my network when I am out of the office.
Kudos to MIKE once again, for making our lives a little easier!
http://support.microsoft.com/kb/q182886/
http://www.databasejournal.com/features/mysql/article.php/3355201
Synchronization Conflicts
When you have multiple database replicas and it comes time to synchronize them either to each other or to another main database synchronization conflicts occur if the same field and record were changed on more than one replica. Database replication engines have sophisticated algorithms for determining the correct value for a field but obviously there are occasions when a conflict can not be resolved. In this case human intervention is required to resolve the conflict.
The Microsoft Replication Conflict Viewer is a tool which you use to resolve conflicts manually.
To reduce or eliminate the occurrence of conflicts you may want to make design changes to your database tables or modify you work practices so that there exists a segregation of duties and thereby avoiding having two people work on the same tables and records between synchronization instances.
Design changes - for example you may have a work request database which has a status comment field which can get updated by several people during the day. In this case you may want to break the comment field out into a separate table with a date/time stamp to store each version of the comment when it is changed by more than one user.
For a comprehensive set of questions and answer about Microsoft Access database replication please consult Microsoft's page via the following: Microsoft Access Replication FAQ Download and the Database Journal