DoCmd.TransferDatabase corrupting objects in target database

[chris.spano]

I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignments" of our large
database project. We all work on a local copy and export changes made
to various objects. Version controlling is becoming more of an issue,
so we've developed a real basic version control system that tracks
"exports" to the primary DB (which is then version-controlled prior to
distribution to the client--which occurs on an ongoing basis). Note
that the DB is merely a front-end, with SQL-Server 2005 running the
back-end.

The problem lies within our code that exports the objects as each
developer completes them. The codes uses the transferDatabase method
of DoCmd, but is occasionally corrupting the target DB (objects within
the target DB are becoming corrupt, and Access can't seem to figure
out if they're there or not. This is quite a nuisance, as once the
target DB becomes corrupt, we have to replace with a previous version
and export manually. No data is lost, but it defeats the purpose of
handling the whole operation with a neat interface and VB code.

The following article describes (almost identically) the problem We're
experiencing:
http://support.microsoft.com/kb/158923
This article is for Access 95, but MSDN has nothing about v2003.

Wondering if anybody else has experience the same problem, and ideally
if there's a workaround.

 

[Allen Browne]

Chris, I have not experienced that, so perhaps someone who does this sort of
thing will respond with some real-world answers.

In the mean time, you might like to try getting users to do a compact,
decompile, compact before exporting. If your developers have different
versions (even different service packs in some versions), the binaries are
incompatible, and could be the issue here. Let us know if that works.

If all developers use the same version, an alternative might be to use the
undocumented SaveAsText and LoadFromText to upload the objects. That's the
approach this Backup Wizard uses:
http://www.mvps.org/access/modules/mdl0045.htm

If you are trying to investigate the kind of corruption you are
experiencing, you said:
Access can't seem to figure out if they're there or not.
Access 97 and earlier rely on the list in MSysObjects (typically exposed by
DAO.) Access 2000 introduced a second canonical list, which is exposed as
collections such as AllForms. There is a kind of corruption where these 2
don't match, e.g. a form listed in AllForms, but has a different name or is
not found in MSysObjects. If this happens, the solution is to import all the
other objects into a new database (after decompiling and compacting), and
then importing this particular form again from whoever developed it.

Hope something there is of help.

 

[Tony Toews [MVP]]

I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignments" of our large
database project.

Are you aware of the following problem?

You receive a "Microsoft Access Was Unable to Create an MDE Database"
error message in Access 2002
http://support.microsoft.com/kb/289686/
I couldn't find any similar KB article for A2003 but I doubt this
problem has been fixed.

We all work on a local copy and export changes made
to various objects. Version controlling is becoming more of an issue,
so we've developed a real basic version control system

Why not use MS's Visual Source Safe? It does work with Access
although I do not know how well as I've never used it. A few people
have. It in turn uses the SaveAsText functions that Allen mentions.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/