Need Help Using Select INTO from on mdb to another.

17 May 2006 4:01 PM

fracassa

I am trying to use a SELECT INTO statment (using VB 2005) to Insert
records from a "table1" in one mdb to a "table2" in second mdb located
in another location.

The problem is that the columns in the table2 have different names then
the ones in table1. (I have variables that describe the difference)

In the following Example, I want to take ONLY the IndexDate and IndexID
from Table 1 and insert them in to ControlGroupDate and ControlGroupID
(respectively) in table2

Table1 in c:\projects.mdb
IndexDate
IndexID
BatchNumber

Table2 in c:\finished.mdb
ControlGroupDate
ControlGroupID
Model
ScanDate

Can someone please help me with the syntax to do this?

18 May 2006 4:09 PM

Paul Clement

On 17 May 2006 09:01:50 -0700, fraca***@gmail.com wrote:

¤ I am trying to use a SELECT INTO statment (using VB 2005) to Insert
¤ records from a "table1" in one mdb to a "table2" in second mdb located
¤ in another location.
¤
¤ The problem is that the columns in the table2 have different names then
¤ the ones in table1. (I have variables that describe the difference)
¤
¤ In the following Example, I want to take ONLY the IndexDate and IndexID
¤ from Table 1 and insert them in to ControlGroupDate and ControlGroupID
¤ (respectively) in table2
¤
¤ Table1 in c:\projects.mdb
¤ IndexDate
¤ IndexID
¤ BatchNumber
¤
¤ Table2 in c:\finished.mdb
¤ ControlGroupDate
¤ ControlGroupID
¤ Model
¤ ScanDate
¤
¤ Can someone please help me with the syntax to do this?

Make your connection to the database containing Table2 and try something like the following:

INSERT INTO [Table2] (ControlGroupDate, ControlGroupID) SELECT IndexDate, IndexID FROM [MS
Access;DATABASE=<database path for table1 goes here>;].[Table1]

Paul
~~~~
Microsoft MVP (Visual Basic)