Recommendations to Synchronize Access 2010 Database

Hello ~

Recognizing the "replication" feature is no longer part of Access 2010, does anyone have recommendations for synchronizing an Access database?  This example requires only one-way synchronization, from 4 offline user databases into one, then redistribute the one.  Client is committed to Access and working offline.

I'd appreciate your thoughts.

Thank You, Jacob
LVL 2
Chi Is CurrentAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1.  Because you have 4 separate computers adding and editing records, you will have to use a primary key that contains both an identifier of who created the record, and some other field (maybe a manually created LongInt value).>>

 You can also use a GUID, just like you would have if Access was doing the replication.  That gets rid of a lot of the problems.

Jim.
0
 
Dale FyeCommented:
The down side of this effort is that you won't be able to use Autonumber primary keys for two reasons:

1.  Because you have 4 separate computers adding and editing records, you will have to use a primary key that contains both an identifier of who created the record, and some other field (maybe a manually created LongInt value).

2.  When you merge this data to the one computer, you would not be able to add an autonumber value created one one of the 4 computers into the "master", and likewise would not be able to push an autonumber from the "master" out to the offline users.

I have done this as described above, but you have to maintain a record of the next value for the ID fields in each table.  This makes it a bit more difficult to do bulk append queries as each record has to have a new ID value computed, but it can be done, but is not trivial, and the more tables you have, the less trivial it becomes.

Gotta run, but will check back if you have additional questions
0
 
PatHartmanCommented:
We con't have enough information to do anything except ask questions.
1. Are the remote users "Add Only"?  This is the best of all possible worlds since there won't be any conflicts and it is simply a matter of importing new records from the remote databases.
2. If updates from the remote databases are limited so that replica1 updates only type1 records and replica2 updates only type2, then that isn't bad either and again you won't have to deal with conflicts.
3. If any of the four replicas can update any record we get to the $64,000 question.  Who wins?  How will you reconcile this?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Chi Is CurrentAuthor Commented:
Thank You, All!  I realize my question lacked specifics; I was looking for a way to do this and needed to broaden my view here.

Jim, your idea to replace my autonumber ID with a GUID is excellent!  The db is fairly simple.  Using a GUID would enable users to add and edit records without stepping on each other every time data is combined.

Thank You!!!  ~~~ Jacob

Others may find this method from MS Support helpful:
https://support.microsoft.com/en-us/help/4036837/run-time-error-70-permission-denied-generate-guid-with-office-vba
Private Type GUID_TYPE
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type
 
Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr
 
Function CreateGuidString()
Dim guid As GUID_TYPE
Dim strGuid As String
Dim retValue As LongPtr
Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
retValue = CoCreateGuid(guid)
If retValue = 0 Then
strGuid = String$(guidLength, vbNullChar)
retValue = StringFromGUID2(guid, StrPtr(strGuid), guidLength)
If retValue = guidLength Then
' valid GUID as a string
CreateGuidString = strGuid
End If
End If
End Function
 
Sub GetGUID()
Dim strGuid As String
strGuid = CreateGuidString()
MsgBox(strGuid)
End Sub

Open in new window

0
 
PatHartmanCommented:
The GUID helps but it doesn't solve your problem.  Please review my last post.
0
 
Chi Is CurrentAuthor Commented:
Thank You, Pat.  Yes, any one of the users could update any record, because different individuals in the department typically edit different records in the database, this should not be a problem.  If it does happen, there's agreement the most recently edited record would win = updated information.
0
 
PatHartmanCommented:
It's not that simple.  What if person 1 updates field 1 and person 2 updates field 2?  You're client might consider using RDP which will allow everyone to share the app over the internet.
0
 
Chi Is CurrentAuthor Commented:
Thank you, Pat.  I agree.  Clients are aware of this and willing to go with limitations - since they claim the same record is "never" edited by different people on different machines.  RDP has been my standing recommendation to these clients, which I have used before!  HOWEVER, they are not yet willing to invest in the implementation and configuration.  Thank you for your thoughts!  Best Regards ~ Jacob
0
 
PatHartmanCommented:
As long as they are aware of the problems.

Merry Christmas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.