Importing Tables in code

[kaiana]

I am trying to import the tables from one database to another with the relationships. If I do it using the wizard it imports the relationships but when I use transfer database it only allows me to import one database at a time which then doesn't import relationships.

I need to do this in code to import the tables of an existing backend to a new backend of a distributed app. I am using DAO.

 

[mp9]

I use code to import the tables, then recreate the relationships. It's a bit long-winded but it works! Here's a simple example for two tables that have a cascading 1:m realtionship based on CustomerID:

DoCmd.SetWarnings False
Dim dbs As Database, rel As Relation, fld As Field
Set dbs = DBEngine(0)(0)
'Drop existing tables first
BeginTrans
dbs.Execute ("DROP TABLE tblOrder;") 'Drop child tables 1st
dbs.Execute ("DROP TABLE tblCustomer;") 'Then drop parent
CommitTrans
'Import tables from other database                DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\temp\mydb.mdb", acTable, "tblOrder", "tblOrder", False
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\temp\mydb.mdb", acTable, "tblCustomer", "tblCustomer", False
'Create relationship
Set rel = dbs.CreateRelation("CustomerID1", "tblCustomer", "tblOrder", dbRelationUpdateCascade + dbRelationDeleteCascade)
Set fld = rel.CreateField("CustomerID")
fld.ForeignName = "CustomerID"
rel.Fields.Append fld
dbs.Relations.Append rel
dbs.Relations.Refresh
Set dbs = Nothing
DoCmd.SetWarnings True

http://www22.brinkster.com/accessory/

 

[MichaelRed]

Well, that creates the (programatically defined) relationship on the imported tables, it does NOT get the relationship from the db where the tables were (imported from). However the mod (or additional procedure) should not be difficult.

It is at least a curiosity to me as to WHY it is useful to IMPORT the tables? In general, the tables should perform as LINKED objects without much variation. To have a need to actually import them via CODE implies (or I infer) that the process is accomplished on a quite regular basis. This, in turn, give me the impression that there is some LARGE performance issue with the LINKED tables. My curiosity is invoked.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[kaiana]

My wife runs a small PC software support business in Australia. One of our client asked us to develope a small access database to provide reporting to the Government for the purpose of meeting funding requirments. We developed the DB and it works great. Although, over a short time we were asked to include many other advantages (eg Staff Photos) and more. The client then Decided to market the product Australia wide. We now have the program running in runtime and it relinks the tables automatically accross a network if the connection is broken. It has a regrstration process on first install where the client must call the call centre to recieve the activation code. The program Has Grown into a monster and we have learnt much.

Our Problem.

Through the call centre the clients have requested a few additions to the DB. This upgrade requires some new tables and some extra fields in the backe end and a new front end. Front end is fine however, we are lost on where to go with the upgrade of the backend. Do we import their data into the new backend or do we change the existing backend by programmatically creating the tables and fields.

Let me outline some of the things we have tried:

1. We can create the new tables with a Make table query but I can't seem to get it to create the relationship with the existing table.

2. I have tried to import the existing tables (with data) into the new backend using the transfer database function but because it only imports one table at a time it doesn't import the relationships

3. We have tried creating a module in VB using DAO and opendatabase but have been unable to open the backend.


We would really appreciate your opinion on the most efficient way to achieve a backend upgrade remotely (run off the new install disk).

Kind Regards


Jason & Tanya Spence

 

[mp9]

Maybe

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q128157

will help - it's an Access 97 example of how to use DAO to import relationships.

Alternatively,

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q304322

shows an Access 2002 example of how to use ADOX to do something similar.

http://www22.brinkster.com/accessory/

 

[MichaelRed]

Not having a need for it, I haven't actually 'done the math', but Ms. A. Help (ver 2K) includes the topic "Relationc Collection" as well as the specific topic "CreateRelation Method". As noted in the earlier post, the procedure by mp9 shows much of the overall approach.

I would probably just create the new tables and any relationhips directly in code supplied in a seperate module and have a set-up type program which is run by the user, rather than create the tables and relationships in another db and then attemt to copy them through code. My 'rationale' for this is that you need approximatly the same functionallity wheather the tables/fields/ relationships are copied or created, so the actual existance seems to only increase the size of the update. In either case, you (obviously) need to do a lot of testing of the actual installation routine and include many chaeks on the configuration of the db which is being updated, as users may have made some changes which interfeer with the update.

An alternative, which I would consider. Consider the changes as a completly NEW release (ver X + 1). Provide an entirely NEW db (Tables/Fields/Relations/Indicies ... ). Install the NEW system and simply append the DATA from the older version (ver X). As long as your customers retain the older ver, there is a reasonable chance that you cannot be caught in the mix-up of only getting part of the "update" installed and having something go awry. It seems "SAFER" to me.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places