Whats wrong with this code

[Rachael]

Hi All,

As part of my strategy to upgrade clients back ends which involves matching version numbers, copying tables to BE, running queries etc I have a piece of code whereby I need to delete 3 tables from their BE's (these tables are causing relationship conflicts). The following code works fine on my pc but didn't work on my laptop until I added the full path at the Set DB = "..the path etc" (originally I just had the BE file name and it worked on pc) Anyway changing this line to the full path of the BE made it work on the laptop. Cool, or so I thought! Now, having sent the upgrade to a client its mucking up again

Heres the code:

Dim DB As DAO.Database, i As Integer

Set DB = OpenDatabase("C:\Program Files\VinRec\VADataFile.mdb")

For i = DB.Relations.Count - 1 To 0 Step -1
If (DB.Relations(i).Table = "Vintage") Then
DB.Relations.Delete (DB.Relations(i).Name)
End If
Next i
For i = DB.Relations.Count - 1 To 0 Step -1
If (DB.Relations(i).Table = "Harvest Data") Then
DB.Relations.Delete (DB.Relations(i).Name)
End If
Next i
For i = DB.Relations.Count - 1 To 0 Step -1
If (DB.Relations(i).Table = "Pruning") Then
DB.Relations.Delete (DB.Relations(i).Name)
End If
Next i

DB.TableDefs.Delete ("Vintage")
DB.TableDefs.Delete ("Pruning")
DB.TableDefs.Delete ("Harvest Data")

Set DB = CurrentDb

I hope someone can enlighten me as to what is going wrong, it seems very fickle.

Thankyou, Rachael

 

[Rachael]

Well, the mystery continues, its a bit like being a detective sometimes!

Is it possible that if one of the tables that I'm trying to remove relationships from and delete does not have any relationships that this will stall my code and throw up an error? on some computers!

I am bamboozled because it works in some cases and not others.

Any input or related experience would be greatly appreciated.

Rachael

PS Why do I get the black dot on the messages I post??

 

[Mile-O]

Why do I get the black dot on the messages I post??

To indicate that you have posted to a thread. Certainly makes it easier to look down a list of threads and find those that you've got yourself involved in.

 

[bradcccs]

Hi Rachael,

I like your "upgrade strategy" (Which reminds me, I haven't drank that pinot grigio yet)

I followed you original post on this topic with interest, and have not ran into the same issues that you describe. However, I have only been using this on test db's, and as we all know, things don't go wrong until you are live.

Is it possible that if one of the tables that I'm trying to remove relationships from and delete does not have any relationships that this will stall my code and throw up an error? on some computers!

As you are using an IF embedded in the For Next loop, no issues should present themselves. IF no relationships exists for Tbl_Whatever, then the table can be safely deleted (and will).

Do you get any error messages etc??
Is there any difference in the db structure from those that work, and those that don't (remnant tables / relationships etc)?

Had a chance to get to the snow fields this year? I am heading to Falls this Friday. (Have my boots on already )

Brad.

 

[Rachael]

Hi Brad, thanks for your reply,

The upgrade strategy works really well. I'm comforted to know that the For Next thing won't stall if relationships aren't there.

I am starting to suspect know that the client hasn't installed the last upgrade and am now diverting my attention to this. I feel satisfied that my code is OK.

Thanks, Rachael