table relationship messing up my link

[Charlie]

I want to import (move, copy, or however/whatever I can do) a table from
db1.mdb to db2.mdb
The tables are the same structure. I just want to programmatically 'put'
all the data from table1 in db1.mdb into table1 of db2.mdb
The problem is that the table has relationships to other tables in db2.mdb
that I can't lose.
The DoCmd.TransferDatabase acLink, ... doesn't work, because it renames the
table with a 1 after it and I've lost my relationship.
Would there be some code that would go through the records in the first
table/database and copy them to the same table in second database? I'm not
familiar with working with recordsets in two different databases at the same
time.
Or can I just import the tables into db2 and then programmatically set up
the relationship?

This is frustrating...
thanks for any help
ck

 

[BeWyched]

Hi Charlie

Use (in db1):

Docmd.RunSQL "INSERT INTO Table1 IN 'c:\path to db2\db2.mdb' SELECT * FROM
Table1;"

Cheers.

BW

 

[Charlie]

hmmm, that worked, but when I'm finished with my project I won't really have
access to db1. I need the code to be in db2. is this possible.
thanks,
ck

 

[BeWyched]

OK- no probs. - just reverse it -
Use (from db2):

Docmd.RunSQL "INSERT INTO Table1 SELECT * FROM Table1 IN 'c:\path to
db1\db1.mdb';"

Cheers.

BW

 

[Charlie]

THANKS SO MUCH!
CK

OK- no probs. - just reverse it -
Use (from db2):

Docmd.RunSQL "INSERT INTO Table1 SELECT * FROM Table1 IN 'c:\path to
db1\db1.mdb';"

Cheers.

BW

 

[Charlie]

My file/folder name is a string:
I tried this:
DoCmd.RunSQL "INSERT INTO Table1 SELECT * FROM Table1 IN 'strFolder &
temp\Charlie.mdb';"

but it didn't work

the file name is Charlie.mdb
the folder is strFolder & "temp\"

How do I put that in the SQL?
thanks so much,
ck

 

[BeWyched]

You just have your 's in the wrong place !

I assume that strFolder is a variable? i.e. strFolder might be
"c:\somefolder\"

If so, then make sure it has a trailing \ or put a \ before 'temp' -
'\temp\Charley ...'.

Then use:

DoCmd.RunSQL "INSERT INTO Table1 SELECT * FROM Table1 IN '" & strFolder &
"temp\Charlie.mdb';"

Note that '" is, exagerated, ' ".

If you still have problems try looking at the SQL in a message box and make
sure it reads correctly. The important thing is that the path and file name
after the 'IN' needs to be in single inverted commas.

e.g. MsgBox "INSERT INTO Table1 SELECT * FROM Table1 IN '" & strFolder &
"temp\Charlie.mdb';"

should read, in the message box:

INSERT INTO Table1 SELECT * FROM Table1 IN 'c:\somefolder\temp\Charlie.mdb';"

Let me know how you get on.

Cheers.

BW

 

[Charlie]

That should do it, thanks again!
ck

You just have your 's in the wrong place !

I assume that strFolder is a variable? i.e. strFolder might be
"c:\somefolder\"

If so, then make sure it has a trailing \ or put a \ before 'temp' -
'\temp\Charley ...'.

Then use:

DoCmd.RunSQL "INSERT INTO Table1 SELECT * FROM Table1 IN '" & strFolder &
"temp\Charlie.mdb';"

Note that '" is, exagerated, ' ".

If you still have problems try looking at the SQL in a message box and make
sure it reads correctly. The important thing is that the path and file name
after the 'IN' needs to be in single inverted commas.

e.g. MsgBox "INSERT INTO Table1 SELECT * FROM Table1 IN '" & strFolder &
"temp\Charlie.mdb';"

should read, in the message box:

INSERT INTO Table1 SELECT * FROM Table1 IN 'c:\somefolder\temp\Charlie.mdb';"

Let me know how you get on.

Cheers.

BW

 

[Charlie]

thanks. It's working great!
ck

ps. if ctrl-G brings up the imm window, why isn't there a shortcut key to
make it go away???

 

[BeWyched]

Hi

Great to hear its sorted.

re Ctrl-G - irritating! Unfortunately I don't think there's a solution - its
the same with F2 for the Object Window etc. Typical Microsoft - dead easy to
take on board, but a pain in the proverbials if you want rid !

Good luck.

BW

 

[John Spencer]

re Ctrl+G

You could always type Alt+F4 (the F4 function key) while the VBA window has
the focus. That close the VBA window (actually closes the VBA app, but ..)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[BeWyched]

Thanks John

Trouble is, ALT-F4 destroys everything.

Its a surprise to me that the various Development Environment shortcut keys
don't toggle like they do in Office - CTR-B will togle Bold on and off in
Word etc.In the greater scheme of things it should be an easy improvement to
sort.

Perhaps something the MVP forum could put forward to MS.

Cheers.

BW