Access Replication Problem

Have multiple databases that are linked together, and was created in 97. Has since been changed to 2002-2003. Problem will not let me create replica. Missing MsysAccessObjects. If you copy this hidden file from new database to this database, I am able to create a replica. Now if I try to syn get error MsysAccessObjects is missing. If copy that file to replicated database it will syn up.

How can I get MsysAccessObjects to be in original database, like it is suppose to be, so when creating replica the file will be there. Do not want to try remember to copy MsysAccessObjects over all the time. Also by doing this it is creating an issue with the repair and compacting. Get error "Could not find field 'Data'.
Who is Participating?
Leigh PurvisDatabase DeveloperCommented:
There will likely be a field referenced in your report or it's recordsource query that doesn't refer to a valid source field - and so your're being prompted for it.

For example if you had a control that was bound to a field not in your query.
Another classic example often overlooked is a field in Sorting and Grouping that is no longer relevant to the recordsource.

And I'm afraid I have to re-iterate again...
Just because 9 out of 10 databases have always behaved fine while replicating Front End UI objects as well as data doesn't mean that at any time it couldn't all go horribly wrong. :-S
Leigh PurvisDatabase DeveloperCommented:
To be honest - it sounds like a mess.

Replication in Jet is actually a pretty tenuous thing.
There's lots to go wrong.  I only use it myself when it's reeeeally necessary.

You're only replicating a back end mdb with data only in it yes - no forms or code or any UI objects?

I'd probably to going from scratch again.  (If you still have a copy of your blank unreplicated mdb then you'll be good to go - that's always a good idea!)
If not then create a blank database (probably in 2000 format - a there's no real advantage beyond that data wise).
Export the data (to almost any format you like) from an up to date replica and make sure no other changes are happening.

Then Import that data into your new mdb.
(You could possibly link to the data and use Make table queries).

Then make it replicable (into a design master) where it will be sitting permanently.
Then create the replicas where they will be replicated to and from.

And go from there again.
No conversion - no messing :-)

BigIron18Author Commented:
Problem with recreating the database is that this database has been running for 7 years. Several databases are linked together, which also has several queries, reports, forms, and modules that are all linked. To try and follow all the links and creating the forms again would take two months of typing if not more.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Leigh PurvisDatabase DeveloperCommented:
So there *are* FE object in there too?
It's not just a data only back end?
BigIron18Author Commented:
This database does have FE objects. I have done some more testing, and the database runs just fine in Access 2000. There are no issues with it when in Access 2000. The problems start as soon as it is converted to Access 2002 - 2003. We would like to convert it to 2002 - 2003 to keep up with the current, and hopefully help prevent the corrupt database we are getting alot.
Leigh PurvisDatabase DeveloperCommented:
I can't say much else other than there shouldn't be FE objects in a replcated database.

If you had it split into a BE FE then you should be in a better (more robust) position to synchronize.
The data file could be left in 2000 format.
(There's no harm in that at all - no advantage to be had in having data in a newer format).

There's no reason why you can't run a 2000 database under 2003.
Wanting to be up to date is a double edged sword.

There needs to be a good reason for it :-)

But you really should only be replicating the database BE data.
BigIron18Author Commented:
The reason for the update being necessary is speed. For some reason when working with the 2000 format the database is slow over the network. When running in 2002 - 2003 format the database is a lot quicker in all aspects, such as opening, printing, and data entry. Also have a boss that insist that this be put into the 2002 - 2003 format.
Leigh PurvisDatabase DeveloperCommented:
Bah - since when do bosses know anything! ;-)  (Unless they're their own boss - then they're cool lol).

If you're working with a single file over the network then speed is relative.
With a split UI and data - the 2002/2003 format FE residing on users machines and a shared 2000 format BE should be just fine.

I know it might be.seem too great a task to implement a split and so forth to solve this - but as a long term plan it really should be your goal.

(One advantage of leaving data in 2000 format is that it's much more standard to be read by other sources if you so wish).
BigIron18Author Commented:
sorry have not answered earlier, have a nice work load.

The databases total 10 that are linked together to one main db. 9 of the databases are in the 2002-2003 format and they are being used as replica's. I have only one database left that is causing me issues, and it is the one that has all the links. I have started rebuilding a good portion of the database, which can replicate and syn.

However I still have a problem, one of my forms is suppose to display a report when the dates are selected. The preview daily is working fine, but my preview monthly pops up window says, "Enter parameter value". Do I have something wrong in my queries, or is it related directly to the form?
BigIron18Author Commented:
Yes I know, they are being extremely difficult. I just want it to work so I can move on to next job and let them face the corrupt databases that they have been getting. They believe that the only reason they are getting corrupt is because they can not replicate the last database. Also was trying to convince them on redesign of the database, but that would have been a bigger mess. They want everything like it is, ok to put all in one hugh database. :( NO WAY

I will celebrate when done with this and move on. I will look again at the query. I admit it is a hugh mess.
Leigh PurvisDatabase DeveloperCommented:
Fair enough.  Good luck!
BigIron18Author Commented:
OK have the databases everything working. I have run across one more small problem. One of the users created a replica on her computer which also created the design master. Now when I try sync to the master replica, which is now on the server,  it keeps trying to go to her computer. No matter what I have tried it keeps trying to sync to this other persons computer rather than the server.

Almost there. I HOPE :|
Leigh PurvisDatabase DeveloperCommented:
Umm so you have a user - using one of your replicated Front Ends on her pc.
She then created another replica on her pc of that Front End - and with it a Design Master?

So when you use your Design master (or just hub database) to synchronize - her pc is wanting her new version of a Design master?

(Just wanting to make sure that's what's happened - before fainting ;-).
BigIron18Author Commented:
The database is up and running thanks for the help.
Leigh PurvisDatabase DeveloperCommented:
My parting advice is (as I believe you're moving on) to part with some parting advice to the company.
It all needs sorting out.
Either professionally - or just painstakingly.
No replication to update front ends.  (That will cause problems eventually).
Split FE/BE (with possibly even a single core BE) - updates handled separately for FE's and installed on each user's pc.

It's not that that's just my opinion.
It is time and again the recommended way to implement an Access database application.

Best of luck in your future projects.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.