Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Replication / December 2004

Tip: Looking for answers? Try searching our database.

Replication Best Practices

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.