Relationships

[Guest]

I am trying to delete some table relationships in my backend database so that
I can rework the key fields. This all happens for the user at startup. Three
of the four different table relationships are deleted with no error but the
fourth generates an "Item Not Found in the Collection" error. My code is as
follows:

Set dblink = DBEngine(0).OpenDatabase(strDBPath)
dblink.Relations.Delete "TableATableB"

The item is in the collection. If you go to Tools/Relationships in the
back-end db the relationship can be seen. The relationship is functioning and
the Tools/Analyze/Documenter function shows that the name is correct. If I
loop through the Relations collection this too shows the name to be correct.
If I use the Relationship tool to delete the relationship and then re-create
it everything works fine. What's happening here? Thanks.

 

[Allen Browne]

Either there is a problem with the name (e.g. a hidden unprintable character
as part of the name), a corruption, or Access is confused about the name.

First thing would be to remove the Name AutoCorrect nonsense that regularly
fouls up these names. Details:
http://members.iinet.net.au/~allenbrowne/bug-03.html
Then compact.

After that, it might be worth creating a new (blank) database, turning off
name autocorrupt, and importing (File | Get External | Import). In the
Import dialog, uncheck the box for importing relations, so you know you are
not importing any garbage.

 

[Guest]

Thanks Allen, I believe you are right. My problem with the solution is that I
need to do all you suggested via code when the user first opens the program.
Here is what I have so far:
'1. Copy new blank db to folder containing the current data source.
fso.CopyFile "C:\PathToDataFIX.mdb", C:\PathToCurrentFolder
'2. Import all data from existing db tables to new db tables.
strSQL = "INSERT INTO TableA IN '" & strDbFixPath & "' SELECT TableA.*
FROM TableA;"
dbs.Execute (strSQL)
'3. Delete or rename/save existing db.
Kill strNewPath
'4. Compact and rename the fix db to the name of the original db.
DBEngine.CompactDatabase strDbFixPath, strNewPath

The problem is with either killing or renaming the original back-end db and
then replacing it with the new clean version. Because it is linked to the app
that is open I obviously can't do that. Is there any way I can close and then
kill the existing back-end from the app that it is linked to? Or, do I have
to run the above code from a separate third db dedicated to this task (if so,
how?)? Thanks.

 

[Allen Browne]

You cannot have the user send you the file to be fixed?

If not, you may be able to create another mdb that you can send the client,
to perform the "update" on their existing data.

There is probably a better way to copy the data than an INSERT query
statement. That probably won't end up with all the properties set correctly.

 

[Guest]

My client has deployed the app to over 200 of their customers who are spread
out across the U.S. and Canada, so having the file sent to me is a bit
problematic. A separate db used for the purpose of fixing the corrupted
back-end is what I will do.

Do you have any recommendations regarding the copying of data from the
corrupted db to the new db? The new db contains all of the needed tables and
relationships. Nothing other than the INSERT route is jumping out at me.
Thanks.

 

[Allen Browne]

Okay, if you have created the empty tables already so your INSERT is an
Append query (not a Make Table query), it should be fine.

 

[Guest]

OK. Thank you for your help, I greatly appreciate it.