[AccessD] Manual Database Synchronisation (Problems with GUID)

Joshua B jbusergroups at optushome.com.au
Thu May 15 18:50:39 CDT 2003


Group,

Further to my post below, I have been playing with GUID's as primary keys and have run into a few problems.

Firstly, the code "DoCmd.OpenForm "frm_MyForm", , , "theGUID = " & Me.txttheGUID does not work. 

When I type "?Me.txttheGUID" into the debug window, it returns "???????", same result when I try CStr(me.txttheGUID)

When I type in "?StringFromGUID(me.txtTheGUID)" it returns "{guid {1612A983-98E4-48CD-A3F1-4CDC78FF69A5}}", which is close, but I would have to then parse out the "{guid{}" section, and if I'm looking at doing this throughout the whole db, this could be a major headache.       

(Interestingly, if I run "DoCmd.OpenForm "frm_MyForm", , , "theGUID = '{1612A983-98E4-48CD-A3F1-4CDC78FF69A5}'" it works perfectly) 

So I jumped on Google and had a quick look around and came across this http://www.trigeminal.com/usenet/usenet011.asp?1033

which basically lists all the reasons why its NOT a good idea to use GUID's as primary keys.

I'm looking at going back to my original idea. (Using a random autonumber, and attaching a time-stamp to the end of that). But I thought I'd throw this back out there for discussion before I go ahead.

======= At 2003-05-13, 20:16:00 I wrote: =======

>Group:
>
>Hoping someone may have a few ideas on this. I am in the process of setting up specifications for the modification of a clients database. The client has offices in five different locations around the world, and a database that has been shared up until now using Access's in-built replication tool. At the end of each trading day, the database would be sent to the other offices, and they would synchronise. The db has now grown to the size where this is not possible anymore (It weighs in at around 5 megabytes zipped up, and the offices that don't have the luxury of cable are starting to complain). They wish to send only the new and updated records back and forth.
>
>Eventually they will be looking at a web-based solution, but this will not be happening for at least a year, if not more.
>
>My idea is to time-stamp each record when it is updated (or added), and then use this time stamp to determine which records need to be sent to the other offices. I have been assured that the offices will "never" (I know that thats a dirty word) be working on the same record on the same day, so there will be no need to update on a field-by-field basis, I'll simply overwrite the whole record with the updated information.
>
>In the meantime, my main concern with setting this up is how I handle the primary keys for records. Up until now, I have been a firm believer in using an autonumber for a primary key. Of course, this will not be possible in this scenario. When a record is added to the system and sent to all the other offices, the primary keys will have to match, so that my code will be able to match up the updated records when imported at the other end. The best idea I have come up with is to use a random autonumber, and attach a time-stamp to the end of that, and use it as a primary key. For there to be a conflict, it would mean that two offices have generated a duplicate random autonumber at the exact same second. I'm quite happy with the odds of that never happening.
>
>I just thought I would throw this in there, with the hope that maybe someone has done something similar in the past. And of course, there is the likely possibility that I am missing some incredibly easy way of doing this.
>
>Thanks for reading :-)

= = = = = = = = = = = = = = = = = = = =
			

Best regards.				 
Joshua B
jbusergroups at optushome.com.au
2003-05-16





More information about the AccessD mailing list