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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.