MS Access Forum / Replication / December 2004
Replication Best Practices
|
|
Thread rating:  |
AWM - 13 Dec 2004 22:49 GMT I am developing a MS Access db using Access 2003. The db is automatically being saved v. 2000. I have replicated the db to a file on the corporate server. The master file is located on my laptop so I can work on it at home in the evening.
There are four users accessing the db on the server. The users are accessing the db with version 2000, 2002 or 2003.
Everytime I replicate an unexplained error develops. For example, after working on a new form, I compiled the VB, then compressed the db. Then I replicated with the db on the server. A user, in this case using Access 2002, opened the db on the server and when accessing a form (that I did not work on previously), a dialog box appeared stating...
'The record source '~sq_cfrm_Quote_Add~sq_cCustID' specified on this form or report does not exist.
You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.
In the Form or Report's Design view, display the property sheet by clicking the Property button, and then set the RecordSource property to an existing table or query.
<OK>
I checked the recordsource and it appears correct. I have practically rebuilt the form as well. I have also compiled and again compressed the db. All references seem correct yet the problem persists.
Every time I replicate I have problems. This is but one example. Am I doing something wrong with the procedure I am using to replicate the db? And/or, can someone explain this error message and corrective action steps?
Can someone also explain why the error message above has "~" in the message? Does this have a hidden meaning?
I am not impressed with the 2003 version. I rarely had these type of problems with 2000. Are there many reported bugs in 2003? Thanks for the help, Art
Cheval - 14 Dec 2004 20:24 GMT Another case of not understanding the limitations of MS Access Replication. You _*ONLY*_ replicate tables, NOT forms, queries, reports, pages or modules. It also sounds like you're using a windows file copy to take the design master home with, another no-no. You can't move or copy a replica. Have a read of the postings over the last month on why and read http://www.trigeminal.com/
Q "~") It's tried to turn the query reference in to a table.
Steps: 1) Unreplicate the database 2) Split the database in to a front end / back end. 3) Replicate only the backend to the server. 4) Buy a cheap laptop if you need to, but don't copy or move the design master, if you want to take it home.
As far as problems with Access 2003, by saving the file in the 2000 file format, that's like putting a 4 cylinder engine in a 8 cylinder car and complaining that it is slow. Not the cars fault be any means, but the responsibility does belong somewhere...
I am developing a MS Access db using Access 2003. The db is automatically being saved v. 2000. I have replicated the db to a file on the corporate server. The master file is located on my laptop so I can work on it at home in the evening.
There are four users accessing the db on the server. The users are accessing the db with version 2000, 2002 or 2003.
Everytime I replicate an unexplained error develops. For example, after working on a new form, I compiled the VB, then compressed the db. Then I replicated with the db on the server. A user, in this case using Access 2002, opened the db on the server and when accessing a form (that I did not work on previously), a dialog box appeared stating...
'The record source '~sq_cfrm_Quote_Add~sq_cCustID' specified on this form or report does not exist.
You misspelled the name, or it was deleted or renamed in the current database, or it exists in a different database.
In the Form or Report's Design view, display the property sheet by clicking the Property button, and then set the RecordSource property to an existing table or query.
<OK>
I checked the recordsource and it appears correct. I have practically rebuilt the form as well. I have also compiled and again compressed the db. All references seem correct yet the problem persists.
Every time I replicate I have problems. This is but one example. Am I doing something wrong with the procedure I am using to replicate the db? And/or, can someone explain this error message and corrective action steps?
Can someone also explain why the error message above has "~" in the message? Does this have a hidden meaning?
I am not impressed with the 2003 version. I rarely had these type of problems with 2000. Are there many reported bugs in 2003? Thanks for the help, Art
AWM - 15 Dec 2004 00:09 GMT Cheval,
Thank you, thank you, thank you. This was excellent help (perhaps the best I have ever received on a newsgroup)!!!
Would appreciate it if you could help me a little further...
1. I am not using windows file copy to take the design master home. No, my design master is located on my laptop and I am replicating to the server. Should I continue this or are you stating I should put the design master on the server... I don't understand, please clarify. 2. Once I split the db and replicate the tables to the server , how do I manage changes to the front-end on the individual computers? Do you have articles that you could refer me to?... for example, do I simply delete the old front-end and copy the new front-end (on each workstation)... then relink the front-end to the back-end?? 3. fyi... You stated that replication does not copy forms, queries, etc but it certainly does copy forms. I have checked the replicated db after replication and confirm that the form designs have changed but the code has not. Perhaps an issue with Access 2003. Thanks again for your help.
Art
Douglas J. Steele - 15 Dec 2004 00:19 GMT While it will copy forms, queries, etc., it's not intended for that purpose. As Cheval stated, you should only be replicating the data. Your application should be split into a front-end, with only the back-end being replicated.
 Signature Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)
> 3. fyi... You stated that replication does not copy forms, queries, > etc but it certainly does copy forms. I have checked the replicated db [quoted text clipped - 3 lines] > > Art AWM - 15 Dec 2004 02:37 GMT Cheval/Doug,
You have been very helpful. Thank you for both of your responses. I just have a couple more questions if you could help a little further...
1. How do you "unreplicate a database"? 2. Now that I will split the db, how do I manage the front ends? Can I replicate the front-ends so that when I change a form, etc. all front-ends located on various workstations are automatically updated? 3. Just to reconfirm... my laptop does have the master copy. The file on the server is replicated to the design master. Is this the right way to manage this? Thanks again for your excellent help.
Regards, Art
Cheval - 15 Dec 2004 20:43 GMT Q1) http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7 Q2) See the other message Q3) Yeah, it doesn't matter who replicates to who, as long as all replicas get replicated to, otherwise they become dead replicas.
Cheval/Doug,
You have been very helpful. Thank you for both of your responses. I just have a couple more questions if you could help a little further...
1. How do you "unreplicate a database"? 2. Now that I will split the db, how do I manage the front ends? Can I replicate the front-ends so that when I change a form, etc. all front-ends located on various workstations are automatically updated? 3. Just to reconfirm... my laptop does have the master copy. The file on the server is replicated to the design master. Is this the right way to manage this? Thanks again for your excellent help.
Regards, Art
AWM - 17 Dec 2004 02:14 GMT Thanks for sticking with me Cheval.
Great advice!
AWM - 17 Dec 2004 02:14 GMT Thanks for sticking with my Cheval.
Great advice!
Cheval - 15 Dec 2004 20:39 GMT No problem, Access has some unwritten rules that makes the learning curve a little steeper than expected.
Q1) You can keep the Design Master on your laptop. It makes no difference where you keep it. So if you want to take it home, the laptop would be a perfect place. Just remember not to move it with any tool other than the Replication ones. One more bit of advice on that, I would recommend that you create another replica on the server called [AppName}_backup.mdb or something similar. The reason for this is that in the worse case of a corruption between the Design Master and the server backend, you shouldn't restore a backup from tape or what ever you use to backup your system. Also create another replica on your laptop (see below) so that you have App_DM.mdb and a App_Be.mdb. Q2) Interesting problem when people first come across this, but it is easily solved. Yes you are right, but don't make it hard for yourself. Part 1) From the users point of view their backend is on the server, so their tables should be linked to say J:\App\App_Be.mdb. You on the other hand link to your backend C:\Prog..files\App\App_Be.mdb. The way I solve this is in two parts. In the front end replica, off the Autoexec macro, have a function that in code searches for the C: backend, if not finding it looks for the J: backend to link to and use table object method ".connect" to relink the tables to either database it finds. Part 2) You want to makes changes to the front end and have the users get the latest version. This is easy. Have the users run a VB Script (or JScript whatever to open the app) which copies the app.mde file from the server and then opens it. That way every time they open the application, they get the latest version. Q3) No, I may have not communicated that very well. Access _can_ replicate everything, but it doesn't do it well, so you shouldn't. You always copy the front end everywhere, but you replicate _only_ the backend.
Cheval,
Thank you, thank you, thank you. This was excellent help (perhaps the best I have ever received on a newsgroup)!!!
Would appreciate it if you could help me a little further...
1. I am not using windows file copy to take the design master home. No, my design master is located on my laptop and I am replicating to the server. Should I continue this or are you stating I should put the design master on the server... I don't understand, please clarify. 2. Once I split the db and replicate the tables to the server , how do I manage changes to the front-end on the individual computers? Do you have articles that you could refer me to?... for example, do I simply delete the old front-end and copy the new front-end (on each workstation)... then relink the front-end to the back-end?? 3. fyi... You stated that replication does not copy forms, queries, etc but it certainly does copy forms. I have checked the replicated db after replication and confirm that the form designs have changed but the code has not. Perhaps an issue with Access 2003. Thanks again for your help.
Art
|
|
|