DoCmd.TransferD atabase 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 "assignment s" 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 transferDatabas e 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 posted this reply in another group where you posted this q:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ch*********@gm ail.comwrote in message
news:19******** *************** ***********@d19 g2000prm.google groups.com...

I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignment s" 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 transferDatabas e 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.

[JvC]

Two suggestions:
In the destination database, do a rename of the object you are exporting,
prior to the export. I add a date/time stamp to the name. This gives you a
version history, too.
Make sure you refresh the destination container after each export.

Good luck!

John

<ch*********@gm ail.comwrote in message
news:19******** *************** ***********@d19 g2000prm.google groups.com...

I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignment s" 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 transferDatabas e 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.

[chris.spano]

On Jun 8, 12:32 pm, "JvC" <johnv...@earth link.netwrote:

Two suggestions:
In the destination database, do a rename of the object you are exporting,
prior to the export. I add a date/time stamp to the name. This gives you a
version history, too.
Make sure you refresh the destination container after each export.

Good luck!

John

<chris.sp...@gm ail.comwrote in message

news:19******** *************** ***********@d19 g2000prm.google groups.com...

I'm running Access 2003 on an XP machine, and there are multiple
developers who are working on different "assignment s" 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 transferDatabas e 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 & JvC,

Thanks both for your comments/suggestions... I'll try playing around
with some of your ideas and see what happens. Will post results...