I am using two acess databases and asp.

How do I use this INSERT INTO statement to copy the distinct records from one to the other?  Do I open two connections?  I'll use execute to run the insert on the connection to the seconddatabase but how do I reference the firstdatabase or firstdatabase connection in the insert statement???  It may not be possible to do what I'm trying to do.  Insert into appears insert one row at a time.  Is it capable of inserting many rows with one statement?

"INSERT INTO [SecondDatabase] SELECT DISTINCTROW * FROM [FirstDatabase];"

When I run this it gives me the error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.

/ups.asp, line 14

There are more fields in the destination table temp.mdb than the table they are being pulled from.  Any Ideas??

Here is my code:

Dim Conn, Comm
Const DSN_NAME = "DSN=UPS Tracking"
Dim strsql

set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open DSN_NAME

strsql="INSERT INTO tempUPS IN 'C:\Shipping Database\temp.mdb' SELECT * FROM calPackage;"

Conn.Execute(strsql)

I have two fields that need to be renamed and the rest need to be inserted just as they are.  I've tried this:

Response.Write "CONVERTING PLEASE WAIT...  " & "<BR>"
server.scripttimeout = 60
Dim Conn, Comm
Const DSN_NAME = "DSN=UPS Tracking"
Dim strsql

set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open DSN_NAME

strsql="INSERT INTO tempUPS IN 'C:\Shipping Database\temp.mdb' SELECT  calPackage.m_primaryKey AS PkgPrimary, calPackageForeign AS PkgForeign, *  FROM calPackage;"

Conn.Execute(strsql)

And I got this Error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/ups.asp, line 14

When I list out all the fields (like below) the program works great (and is soooo fast by the way).

strsql="INSERT INTO tempUPS (PkgPrimary, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo, m_pkgCost, m_pkgCostActual, m_isPkgVoid, m_length, m_width, m_height, m_barcodeRefNum, Sm_merchandiseText, Sm_referenceText0, Sm_referenceText1, Sm_referenceText2, Sm_referenceText3, Sm_referenceText4, PkgForeign) IN 'C:\Shipping Database\temp.mdb' SELECT  m_primaryKey, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo, m_pkgCost, m_pkgCostActual, m_isPkgVoid, m_length, m_width, m_height, m_barcodeRefNum, Sm_merchandiseText, Sm_referenceText0, Sm_referenceText1, Sm_referenceText2, Sm_referenceText3, Sm_referenceText4, m_foreignKey  FROM calPackage;"

When I try to just tell it about the two fields that need to be aliased and then tell it to * the rest of the fields it says I have a Syntax error in INSERT INTO statement.   Is there any way to do this???  All of the fields match except for two which have different names.

strsql="INSERT INTO tempUPS (calPackage.m_primaryKey AS PkgPrimary, calPackage.m_foreignKey AS PkgForeign) IN 'C:\Shipping Database\temp.mdb' SELECT * FROM calPackage;"

Also Tried

strsql="INSERT INTO tempUPS ( PkgPrimary, PkgForeign, *) IN 'C:\Shipping Database\temp.mdb' SELECT calPackage.m_primaryKey, calPackage.m_foreignKey, * FROM calPackage;"

I need a statement that inserts my calShipment table into the tempUPS table when tempUPS.PkgForeign = calShipment.m_foreignKey.  Is INSERT INTO only for adding new records?  What SQL Statement do I use for matching up my insert with existing records and updating the blank fields with values from the calShipment table??  Thanks

strsql="INSERT INTO tempUPS (ShpPrimary, m_amount, m_amountActual, m_serviceType) IN 'C:\temp.mdb' SELECT calShipment.m_primaryKey, calShipment.m_amount, calShipment.m_amountActual, calShipment.m_serviceType  FROM temp.tempUPS, calShipment WHERE tempUPS.PkgForeign = calShipment.m_foreignKey;"

strsql="INSERT INTO  tempUPS (PkgPrimary, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo ) IN 'C:\temp.mdb' SELECT m_primaryKey, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo  FROM calPackage;"

How do I tell the INSERT INTO Statement to only insert the new record if it is unique.  If I run this statement once I have 1000 if I run it twice I have 2000. I don't want duplicates to be inserted but when something is added to the source database that isn't in the destination I want the destination to have have the new records inserted.  Thanks

strsql="INSERT INTO  tempUPS (PkgPrimary, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo ) IN 'C:\temp.mdb' SELECT m_primaryKey, m_packageId, m_weight, m_packageType, m_oversizeType, Sm_trackingNo  FROM calPackage WHERE m_primayrKey NOT IN (SELECT PkgPrimary AS m_primaryKey FROM tempUPS);"

should do the trick.