table relationship messing up my link

C

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
 
B

BeWyched

Hi Charlie

Use (in db1):

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

Cheers.

BW
 
C

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
 
B

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
 
C

Charlie

THANKS SO MUCH!
CK

BeWyched said:
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
 
C

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
 
B

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
 
C

Charlie

That should do it, thanks again!
ck


BeWyched said:
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
 
C

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???
 
B

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
 
J

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
..
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top