SQL Other Database

[Douglas J. Steele]

Your current SQL is actually invalid (your database name is outside of the
quotes), but if you want both tables to be in the external database, you
need IN statements for both of them.

ALLSQL = "INSERT INTO tblTransferDetails IN 'C:\RedAct.mdb' " & _
"( TDQuantity, TDItemID )" & _
"SELECT tblCheckDetailsTMP.CDQuantity, tblCheckDetailsTMP.CDItemID " & _
"FROM tblCheckDetailsTMP IN 'C:\RedAct.mdb' " & _
"WHERE
tblCheckDetailsTMP.CDCheckID=[Forms]![frmTransferItem]![TxtRightID];"
DoCmd.RunSQL (ALLSQL)

You could also use

ALLSQL = "INSERT INTO [;Database=C:\RedAct.mdb].tblTransferDetails " & _
"( TDQuantity, TDItemID )" & _
"SELECT tblCheckDetailsTMP.CDQuantity, tblCheckDetailsTMP.CDItemID " & _
"FROM [;Database=C:\RedAct.mdb].tblCheckDetailsTMP " & _
"WHERE
tblCheckDetailsTMP.CDCheckID=[Forms]![frmTransferItem]![TxtRightID];"
DoCmd.RunSQL (ALLSQL)

 

[DS]

I'm Inserting records from one table into another table, however this is
happening in another database, not the current one I'm in.
I'm using the IN syntax, My question is do I have to use the IN again
after the FROM part (As it is now is it selecting records from the
current database or the RedAct database?) I need it to select records
from the RedAct database.

Thanks
DS



ALLSQL = "INSERT INTO tblTransferDetails IN '" & C:\RedAct.mdb & "' " & _
"( TDQuantity, TDItemID )" & _
"SELECT tblCheckDetailsTMP.CDQuantity, tblCheckDetailsTMP.CDItemID " & _
"FROM tblCheckDetailsTMP " & _
"WHERE
(((tblCheckDetailsTMP.CDCheckID)=[Forms]![frmTransferItem]![TxtRightID]));"
DoCmd.RunSQL (ALLSQL)

 

[DS]

Thanks Doug!
DS