Import Tables that have relationships

[goldenorb82]

Hello,

I know this has been asked a few times before, and I did search it, but could not find an adequate answer.

I want to use code (VBA) to import tables from a different Access database into the current one, by first deleting the current database tables, and then importing the new tables from the other database. I found code in the following thread that does exactly this:

http://www.access-programmers.co.uk/forums/showthread.php?t=74700&highlight=import+tables

However, the code in this example only appears to delete tables in the current database that have no relationships with each other before importing the outside tables. My tables, however, are rife with relationships and when i run this code, i get the message, "You cannot delete the table "tblWhatever", it is participating in one or more relationships." Can someone give me a pointer or two about how I can solve this problem (if at all) ?

Thanks in advance!

 

[goldenorb82]

Thank you for your reply, i will consider linking the tables instead. However i should probably explain my situation. I am creating an MS Access application that allows users to use forms/reports to view data that is kept in the tables of the database. The application is nearing the testing stages, and my superviser mentioned that it would be great to put the app on our server and allow people (testers) to input real data and see how it performs. However, while this is going on, i will be constantly updating my own copy of the app, changing things if there are bugs, problems, etc. so rather than having any new entries entered manually, it would be nice to just hit a button and update the database values too.

so, you see -- i will need to update data, for it will probably be changing a lot, according to whatever people enter through the forms on the server copy. Sorry for the long-winded explanation!

Here's an idea i had -- how about a function that goes one by one deleting the values in the database, and then creates a DAO connection to the external database to "Select * INTO..." and re-upload into my database?

 

[misunders2d]

Hi, goldenorb82!
As a solution, you can:
1)empty the initial tables by delete query (no way deleting record by record),
for instance DoCmd.RunSQL "DELETE * FROM tableName"
2)import new table with temporary name
by DoCmd.TransferDatabase acImport
3)select all data into initial table
DoCmd.RunSQL "INSERT * INTO ..."
4)drop the temporary table
for instance by DoCmd.RunSQL "DROP TABLE tableName" or by
DoCmd.DeleteObject acTable, strTableName

Definetely you can implement your initial idea, which I HIGHLY DON'T RECOMMEND by removing all relations, keeping them aside and then creating same way, for instance you can clear all relations using DAO:

Sub ShowRel()
Dim dbs As DAO.Database
Dim rel As DAO.Relation
Dim strFT As String

Set dbs = CurrentDb
For Each rel In dbs.relations
dbs.relations.Delete rel.Name
Next rel
Set dbs = Nothing
End Sub

if, in addition u store the removed relations in some set u'll be able to insert them back by dbs.relations.Append... (with some formatting)
However, i still don't recommend to use this technique! Changing the database structure on fly is rather dangerous, much better just to update it's data...
Good Luck

 

[goldenorb82]

Wow -- thanks misunders2d ! Actually i started implementing part 1 of your solution, but i think i like the way you are finishing it with the temporary table. Yeah, i feel like changing the relationships is tricky and sticky and especially since i'm knee deep in them, i don't want to mess about. Right now, i'm working late (i'm gonna be here till 11 by the looks of things, so Ima sit here and work on this...wish me luck and coffee