Copy tables between .mdb files via VB code?

[KB]

I have a split (prog & data) Access app, with many users. When I
release an update I have "checking" code at launch which determines
the datafile version, and adds fields as necessary (users only d/l the
prog.mdb to upgrade).

In my next release, I have two entirely new tables to add. These have
lookups involved, and my skills of creating tables w/lookups is
terrible.

Could I not just put copies of the two new tables in the prog.mdb, and
have my update code "copy" the tables to the data.mdb thus saving
myself the hassle.

How would one do this, can't find any code on the net, or is it
possible?

- Kevin TIA!

 

[Steve Schapel]

Kevin,

You can use DoCmd.TransferDatabase for this. The Help topic on this is
very good. However, I am not sure I understand... Don't you have
direct access to the data file, that you can add the new tables there?
If you add them via the process of distributing your "prog" file, the
first one will add the new tables to the backend file, and then in all
subsequent frontend updates this will be redundant, as the new tables
will already be there. Or have I missed your meaning?

 

[Albert D. Kallal]

I have a split (prog & data) Access app, with many users. When I
release an update I have "checking" code at launch which determines
the datafile version, and adds fields as necessary (users only d/l the
prog.mdb to upgrade).

Yes..I think just about all of us have made something like the above!!!

In my next release, I have two entirely new tables to add. These have
lookups involved, and my skills of creating tables w/lookups is
terrible.

Yikes...I hope you mean relations....and not the actual lookup feature in
the fields!! I will not even suggest solutions to lookups in tables.
(read the following..and take note of #2)

http://www.mvps.org/access/tencommandments.htm

So, lookup fields = bad

just build queries to pull in data form other tables...


But, anyway...lets assume you have to copy a table to the BE. I also put
that table in the new FE, and "transfer" it to the back end. This is MUCH
less code then trying to use DDL sql (create-table) statements to make the
table.

The code I used to copy the table is:


strFromDB = CurrentProject.FullName
strToDB = strBackEnd

DoCmd.TransferDatabase acExport, "Microsoft Access", strToDB, acTable,
"tblGroupRemindC", "tblGroupRemind", True

Note how I always name the tables in the front end with a "C" (for "C"
copy). Further, note that this FE already has the table tblGroupRmind as a
linked table. (I linked it on my system..since the BE table does exist on
the clients BE yet). When I deploy the FE, that table is NOT yet on the
BE..but the above transfer command transfers the table to the BE, and no
further code is required, since the link to tblGroupRemind is already in
place.

Now, my 2nd problem is that the above table was in fact related to the
contacts main table. I wanted relation, and I also wanted cascade deletes
setup. I used:

' add relatinsetup

Set nT = db.TableDefs("tblGroupRemind")
Set nR = db.CreateRelation("ContactIDRI", "Contacts",
"tblGroupRemind", dbRelationDeleteCascade +
dbRelationLeft)

nR.Fields.Append nR.CreateField("ContactID") ' parent table PK
nR.Fields("ContactID").ForeignName = "ContactID" ' child table FK
db.Relations.Append nR
db.Relations.Refresh

Set nR = Nothing