Re-linking problems if linked db no longer exists..Help!

[ARC]

I'm having trouble here with Access 2007 and connecting to a different
database.

The code below works great IF the previous back-end database connection is
still present and you are trying to connect to a new location (ie: datapath
variable below).

However, if you remove or even rename the old database, then launch your
program, the code fails on the mytable.refreshlink line with an error 3044.
(Description of that error is: 'C:\pc
experts\develop\IQOFF2007\Database.accdb' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides.) This doesn't happen for all linked
tables, just a handful (possible corruption in MSysObjects table). Here's
the code I'm using:

If MyTable.Connect <";DATABASE=" & datapath Then
MyTable.Connect = ";DATABASE=" & datapath
MyTable.RefreshLink
End If

So this is not good. Basically I was trying to test an end-user running the
program (and they naturally wouldn't have my development folder and
database), so I renamed the dev. folder, and the .refreshlink fails on a
handful of tables. Apparently you don't even have to rename the folder, you
can simply rename the db that you were originally connected to.

Now here's the interesting bit. I can't even manually re-link the tables
that wouldn't re-attach through the linked table manager (if the original
database is no longer present). Looking in the MSysObjects table, I did a
search under the Database column for the db that is no longer present. It
appears that there are some corrupt lines that include a reference to the
old db in the Database column, however the Foreign Name column is something
like: MSysComplexTypeVH_ and then a bunch of numbers.

Another odd thing, it doesn't do this for all linked tables, just some of
them, but even one table not re-linking is enough to throw off my
re-attachment routine.

I did try creating a blank database, and re-importing all objects. This
removed the corrupt MSysObjects lines, but on first launch, the corruption
happens again during the reattach process, and some tables simply won't
re-attach.

Can anyone confirm this on Access 2007? If you have an app with re-linking
code at the start, all you have to do is rename the db that your connected
to, then launch your app and see if you have the same problem.

Thanks!

Andy

[ARC]

After much testing, I think I've found the answer...

I Searched the database column in MSysObjects for anything starting with
"C:\", and then reviewed all the items in the name column. The 2 entries
with complex data seemed to indicate version history. That made me think
about testing the new access 2007 feature for version history on memo notes
fields.

I went back into my database, located the 2 memo fields, changed the Append
Only back to No, then created a blank database, and imported all the tables
and relationships from the back end db.

Then, I rebuilt my main program database, and searched MSysObjects again and
it came up clean.

Now the code below works perfectly when I reattach tables (if the old
attached db no longer exists).

So needless to say, do NOT use the new Append Only feature in 2007!!!!!
Chalk this up to the list of Acc2007 bugs. Does anyone have an easy "in" to
report this to microsoft?

Oh, another possible bug...Might relate to the append feature as well. If
you create a clean program file, the queries will get hosed if you don't
have the properly attached tables. You'll get things like: Expr1, Expr2,
Expr3 and so-on after it imports the queries.

Andy

"ARC" <an**@andyc.comwrote in message
news:_v*******************@newssvr29.news.prodigy. net...

I'm having trouble here with Access 2007 and connecting to a different
database.

The code below works great IF the previous back-end database connection is
still present and you are trying to connect to a new location (ie:
datapath variable below).

However, if you remove or even rename the old database, then launch your
program, the code fails on the mytable.refreshlink line with an error
3044. (Description of that error is: 'C:\pc
experts\develop\IQOFF2007\Database.accdb' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides.) This doesn't happen for all linked
tables, just a handful (possible corruption in MSysObjects table). Here's
the code I'm using:

If MyTable.Connect <";DATABASE=" & datapath Then
MyTable.Connect = ";DATABASE=" & datapath
MyTable.RefreshLink
End If

So this is not good. Basically I was trying to test an end-user running
the program (and they naturally wouldn't have my development folder and
database), so I renamed the dev. folder, and the .refreshlink fails on a
handful of tables. Apparently you don't even have to rename the folder,
you can simply rename the db that you were originally connected to.

Now here's the interesting bit. I can't even manually re-link the tables
that wouldn't re-attach through the linked table manager (if the original
database is no longer present). Looking in the MSysObjects table, I did a
search under the Database column for the db that is no longer present. It
appears that there are some corrupt lines that include a reference to the
old db in the Database column, however the Foreign Name column is
something like: MSysComplexTypeVH_ and then a bunch of numbers.

Another odd thing, it doesn't do this for all linked tables, just some of
them, but even one table not re-linking is enough to throw off my
re-attachment routine.

I did try creating a blank database, and re-importing all objects. This
removed the corrupt MSysObjects lines, but on first launch, the corruption
happens again during the reattach process, and some tables simply won't
re-attach.

Can anyone confirm this on Access 2007? If you have an app with re-linking
code at the start, all you have to do is rename the db that your connected
to, then launch your app and see if you have the same problem.

Thanks!

Andy

[AnandaSim]

On Sep 9, 5:13 am, "ARC" <[e-mail]:

So needless to say, do NOT use the new Append Only feature in 2007!!!!!
Chalk this up to the list of Acc2007 bugs. Does anyone have an easy "in" to
report this to microsoft?

Um, I got a bit lost in your discussion of the two issues. Please only
on the Append Only feature of memo fields, tell us again what is the
bug?

Thanks

Ananda

[AnandaSim]

Thanks Andy. I've magpied the idea away.

Ananda