Question Replication/Synchronization in Access 2007

[genesis]

while Replication or synchronization is not available in Access 2007, so how does one should synchronize records between two or more databases?

My idea would be to have it done manually through query. any other ideas?

 

[datAdrenaline]

I think you will need to give us more to go on, like what is your intent ... I know you want two db's to be same, but to what degree ... for example, do you want them as close to real time as possible? ... Which one is the master? ...

Also, do take note that Replication is file format dependant, not Access version dependant. In other words, if your file format is an .MDB, then you can still replicate with Access 2007. However, replication is not valid with the .ACCDB format.

 

[genesis]

well. I dont need them to be on real time, probably on at the end of the day daily basis updating only.

Both of them are master databases.

These databases are used for two or more office or store branches.

Yes, I have searched in google that replication is no longer supported in access 2007 but I am using Access 2007. And therefore, I really cannot use that function. So I am planning to use Append query to update the two database.

I have stated in this post what I have already come up but still have problem.

http://www.access-programmers.co.uk/forums/showthread.php?t=182440.

 

[datAdrenaline]

Ok ... I see from your other post that you are using .ACCDB format. But ... I wanted to make sure you knew that the Access 2007 application supports replication IF and only IF, the file format used is .MDB ...

I replied to your other thread with an insert syntax that is probably more workable than using the IN clause. (see other thread for details)

Also, if each db is for a different store, do you have a way to distinquish which records are from which store? .... thus preventing the possibility of duplicates?

 

[genesis]

yes. they would have a unique store / office id.

 

[dfenton]

while Replication or synchronization is not available in Access 2007

THIS IS FALSE. COMPLETELY FALSE, WRONG, ERRONEOUS, TOTAL BS.

Jet replication is fully supported by Access 2007 and the new ACE.

What is *not* supported is replication of files using the ACCDB format. This is a limitation that Microsoft chose to implement, not one that they *had* to implement, as the functionality is clearly built into A2007 and the ACE, otherwise, it wouldn't be able to work with replicated MDBs.

so how does one should synchronize records between two or more databases?

Microsoft would like you to think that Sharepoint is the replacement for Jet replication. They are wrong in that regard.

My idea would be to have it done manually through query. any other ideas?

Use MDB format for your data tables. You can still use ACCDB for the front end (which wouldn't be replicated in the first place even if it were an MDB).

 

[dfenton]

Yes, I have searched in google that replication is no longer supported in access 2007 but I am using Access 2007. And therefore, I really cannot use that function.

Whatever you "searched in google" that told you that was WRONG.

Many people have been blithely stating that A2007 drops replication support, but all of those people are COMPLETELY MISTAKEN.

 

[genesis]

sorry for phrasing it wrongly but what you have said dfenton is what I was trying to say that replication / synchronization is not available for the new access 2007 database format.

 

[Rabbie]

sorry for phrasing it wrongly but what you have said dfenton is what I was trying to say that replication / synchronization is not available for the new access 2007 database format.

As has been stated several times in this thread A2007 supports replication for .mdb,.mde foramt but not for .accdb format. From having replicated databases running on A2007 I can confirm that is correct.

DFenton's website has some excellent advice on data replication which I would advise you to follow.

In particular keep your design master safe and remember to split your DB before replication so you only have to replicate the Back End with the tables in it.

 

[dfenton]

sorry for phrasing it wrongly but what you have said dfenton is what I was trying to say that replication / synchronization is not available for the new access 2007 database format.

But that doesn't in any way make replication impossible in A2007. There is no need to seek out some alternative simply because you're upgrading to A2007, which in no way, shape or form requires conversion to ACCDCB format.

 

[genesis]

again, I am using access 2007 accdb format. I have manage to create a code to programmatically synchronize between two database using the append query however, I have problem in that query because it says error key violation when there will be duplicates in records.

maybe you can give me sites on where to study proper and different syntax for append query with outer join connection. I would like to mention that I have already exerted my effort to find answer for this problem thru googling and in different forums on a daily, hourly basis. and I have exerted my own effort to modify code that will fit into it but none still work.

 

[datAdrenaline]

Hello David,

>> There is no need to seek out some alternative simply because you're upgrading to A2007, which in no way, shape or form requires conversion to ACCDCB format. <<

Unless, of course, you wish to utilize the features of the new format that are not available in the .MDB format...

 

[genesis]

you are right there datAdrenaline. that is why I want to use Accdb format of access 2007 to utilize other features there. that is why I built my own synchronization code for accdb format.

can I ask you again datAdrenaline where did you learn the other syntax for INSERT INTO that you gave me?

this one:

INSERT INTO (;DATABASE=D:\rommelfiles\Database2.accdb) table1 ( ID, Fn, Mn )
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN (;DATABASE=D:\rommelfiles\Database2.accdb) table1 AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;

it seems its not the original syntax. I would like to study it why and how it works, especially if I use that on module level.

 

[datAdrenaline]

... I replied in your other thread that seems to be merging with this one ...

http://www.access-programmers.co.uk/forums/showthread.php?p=902813#post902813

 

[genesis]

got it. thanks.

 

[dfenton]

>> There is no need to seek out some alternative simply because you're upgrading to A2007, which in no way, shape or form requires conversion to ACCDCB format. <<

Unless, of course, you wish to utilize the features of the new format that are not available in the .MDB format...

What features would those be? So far as I know, multi-value fields and attachment fields (both of which were added for Sharepoint compatibility, not because Access users were clamoring for them) are the only real new features in ACCDB format. Multi-value fields are an abomination and nobody who doesn't require Sharepoint compatibility should use them. Attachment fields are theoretically nice (if they do what they claim to do), but in my opinion far from sufficient to justify giving up ULS and replication, particularly in a case like this where the user could actually really benefit from using Jet replication.

I've asked this question in several forums (i.e., what are the new features of ACCDB that make it compelling?), and nobody can give me any answers that are not tied up with Sharepoint integration. Since that's something my clients (and most people) don't need, it means the new features of ACCDB that are truly worthwhile are rather slim.

Or am I missing something?

 

[datAdrenaline]

Regardless of your opinion (or mine for that matter) about the new features of the Access 2007 format, if the person/customer is required/requested to utilize them, they will lose ULS and Replication. ULS, which is easily cracked anyway, is not a huge loss (my opinion of course), as it gives the false sense that you are "secure" when the app and data are still quite vulnerable, but ULS was/is quite useful in a specific environment (ie: internal departmental use) where the scope of deployment is controlled/limited. Replication is the biggest loss (again, my opinion) in moving to the new format, especially in the situation the original poster finds themself in.

One feature you did not mention of the new format is the VERY much improved database password, which may be enough for folks to move to the .ACCDB format.

But, we don't know what prompted the move to the .ACCDB format for the OP, but that is where they are at ... so ... that is where I will try to assist.

With all that, it does beg the question as to why the move was made to the .ACCDB format. But again, whether we agree or not with the change, it is what it is ...

 

[dfenton]

Databases passwords have no value whatsoever. They are a much more "fake" form of security than ULS ever was. That they use stronger encryption still does not remove the problems associated with them. What good is a password that has to be embeded in your table links or provided to all the users?

This is a feature that was added for a checklist in a product review. It has no actual value to any user anywhere.

 

[datAdrenaline]

>> What good is a password that has to be embeded in your table links or provided to all the users? <<

That is incorrect, you do not need the pwd embedded in the table link to access a pwd protected db through a linked table.

You can create a connection to the back end programmatically and keep that connection in a global variable, you can then open your linked tables WITH OUT the pwd embedded. The trick is to Link first (with out the pwd), then add the pwd AFTER you link. Then open the connection to the BE via code in the initialization code of the FE ..

I wrote up a post on this technique and have called the process vPPC --- Virtual Password Protected Connection ... it works great ... but its is simply a barrier that blocks many, but definately not on par with SQL server or Oracle.

BUT ... as you say, there is a sense of "security" with a db pwd is a false sense, the db pwd in and .ACCDB is indeed better that previous formats ... but it is not impervious to attacks... especially with the File Based nature of Access databases.

 

[dfenton]

>> What good is a password that has to be embeded in your table links or provided to all the users? <<

That is incorrect, you do not need the pwd embedded in the table link to access a pwd protected db through a linked table.

You can create a connection to the back end programmatically and keep that connection in a global variable, you can then open your linked tables WITH OUT the pwd embedded. The trick is to Link first (with out the pwd), then add the pwd AFTER you link. Then open the connection to the BE via code in the initialization code of the FE ..

But then the password is embedded in your VBA code. This is only a mild improvement. Sure, the MDE strips out the VBA code and leaves only the p-code, but strings like a password are still there.

And my understanding is that the A2007 database password's encryption is still pretty easy to crack.

I just don't see the utility of it and really can't think of a situation in which anyone should waste any time with it.