Change a linked table to a local table (External)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I use VB to change a linked table into a local table, from an external
database?

To Clarify:
DATABASE 1: This database will run the VB Code.
DATABASE 2: This table contains a link to a table in DATABASE 3.
DATABASE 3: A back-end database, holding only tables.

By running VB Code in DATABASE 1, I want to copy a table that shows up as a
"Linked Table" in DATABASE 2, such that it becomes a local table in DATABASE
2.

Thank you for your help.
 
I just reread the previous post and I do not think that code would work. Can
you please send in the exact code that you wrote that was from my first
suggestion with the 2 extra steps. I can help with any issues if I see the
exact code.

I do not think that you can run a query between databases without having
linked tables or using ADO to create connections. I have to say that I am not
an expert at ADO (big fan of DAO), so I cannot help further if you want to
pursue that, but I am fairly confident that the code that I gave you along
with another line to export the table back to db2 and then delete the table
from db 1 should work fine.
 
I do not think that you can run a query between databases without having
linked tables or using ADO to create connections.
Click to expand...

You can e.g. something like this should work in ADO or DAO (assuming
you can use the connection's .mdw to access both):

INSERT INTO [MS Access;DATABASE=C:\db1.mdb;].Employees (SSN,
last_name, first_name)
SELECT SSN, last_name, first_name
FROM [MS Access;DATABASE=C:\db2.mdb;].Employees;

Jamie.

--
 
I tried what you suggested, and received the same error message.

Prior to your reply, I posted the question again in this Discussion Group,
but worded in a different manner. The new post is:

Subject: Cannot find a linked table Posted By: dalbin

I received an answer on that posting that I believe to be true. The answer
is: "It can't be done". What can't be done is referring to a linked table
in another database. If it were not a linked table, everything that has been
suggested in this posting would work.

Nevertheless, thank you for your help. I would really like to be able to
accomplish this task from Database 1, but it appears to be hopeless. I have
accomplished what I wanted to do in an alternative (and less desirable)
manner (i.e. by running the code in Database 2).

Jamie Collins said:
I do not think that you can run a query between databases without having
linked tables or using ADO to create connections.
Click to expand...

You can e.g. something like this should work in ADO or DAO (assuming
you can use the connection's .mdw to access both):

INSERT INTO [MS Access;DATABASE=C:\db1.mdb;].Employees (SSN,
last_name, first_name)
SELECT SSN, last_name, first_name
FROM [MS Access;DATABASE=C:\db2.mdb;].Employees;

Jamie.
Click to expand...
 
You can e.g. something like this should work in ADO or DAO (assuming
you can use the connection's .mdw to access both):

INSERT INTO [MS Access;DATABASE=C:\db1.mdb;].Employees (SSN,
last_name, first_name)
SELECT SSN, last_name, first_name
FROM [MS Access;DATABASE=C:\db2.mdb;].Employees;
Click to expand...

Even this worked for me:

SELECT * INTO LocalTable IN 'C:\Temp1\db2.mdb'
FROM LinkedTable IN 'C:\Temp1\db2.mdb';

BTW, Jamie, does DDL offer a way of renaming a table?
 
I have another clue as to what the problem might be.

I tried John's SQL on multiple tables. The SQL is:

SELECT * INTO LocalTable IN 'C:\Temp1\db2.mdb'
FROM LinkedTable IN 'C:\Temp1\db2.mdb';

On tables linked in from Microsoft Excel, it did not work.
On tables linked in from another Access Database, it worked!

Unfortunately, I've been trying this on a table that I linked in from
Microsoft Excel.
 
John,

If you copy the table (using SELECT * INTO) and then delete the first table
(using DROP TABLE), it achieves the same effect as renaming.

John Nurick said:
You can e.g. something like this should work in ADO or DAO (assuming
you can use the connection's .mdw to access both):

INSERT INTO [MS Access;DATABASE=C:\db1.mdb;].Employees (SSN,
last_name, first_name)
SELECT SSN, last_name, first_name
FROM [MS Access;DATABASE=C:\db2.mdb;].Employees;
Click to expand...

Even this worked for me:

SELECT * INTO LocalTable IN 'C:\Temp1\db2.mdb'
FROM LinkedTable IN 'C:\Temp1\db2.mdb';

BTW, Jamie, does DDL offer a way of renaming a table?
Click to expand...
 
Except that it bloats the database.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dalbin said:
John,

If you copy the table (using SELECT * INTO) and then delete the first
table
(using DROP TABLE), it achieves the same effect as renaming.

John Nurick said:
You can e.g. something like this should work in ADO or DAO (assuming
you can use the connection's .mdw to access both):

INSERT INTO [MS Access;DATABASE=C:\db1.mdb;].Employees (SSN,
last_name, first_name)
SELECT SSN, last_name, first_name
FROM [MS Access;DATABASE=C:\db2.mdb;].Employees;
Click to expand...

Even this worked for me:

SELECT * INTO LocalTable IN 'C:\Temp1\db2.mdb'
FROM LinkedTable IN 'C:\Temp1\db2.mdb';

BTW, Jamie, does DDL offer a way of renaming a table?
Click to expand...
Click to expand...
 
Back
Top