Is This Right?

[DS]

Do I need to refer to the outside dB twice or is this ok?
Thanks
DS

AllSQL = "INSERT INTO tblTransferDetails (IN '" & REDACT() & "'" & _
"TDQuantity, TDItemID )" & _
"SELECT tblCheckDetailsTMP.CDQuantity, tblCheckDetailsTMP.CDItemID " & _
"FROM tblCheckDetailsTMP IN '" & REDACT() & "'" & _
"WHERE tblCheckDetailsTMP.CDCheckID=Forms!frmFXTransferSelect!TxtOldCheckID
" & _
"AND tblCheckDetailsTMP.CDLineID = Forms!frmFXTransferItem!TxtLeftLine;"
DoCmd.RunSQL (AllSQL)

REDACT() is a function with the path to the outside DB

 

[Douglas J. Steele]

Assuming that REDACT() refers to a Jet database (i.e.: an MDB, MDE, ACCDB or
ACCDE), why not create a reference to the other database and use the Execute
method?

Dim db As DAO.Database
Dim AllSQL As String

Set db = OpenDatabase(REDACT())
AllSQL = "INSERT INTO tblTransferDetails " & _
"(TDQuantity, TDItemID )" & _
"SELECT tblCheckDetailsTMP.CDQuantity, tblCheckDetailsTMP.CDItemID " & _
"FROM tblCheckDetailsTMP " & _
"WHERE tblCheckDetailsTMP.CDCheckID=" & _
Forms!frmFXTransferSelect!TxtOldCheckID & _
" AND tblCheckDetailsTMP.CDLineID = " & _
Forms!frmFXTransferItem!TxtLeftLine;"
db.Execute AllSQL, dbFailOnError

I'm assuming both CDCheckID and CDLineID are numeric fields. If they're
text, you'll need additional quotes.

 

[DS]

Douglas,
Sounds interesting. How does one create a reference?
Thanks
DS

 

[Rick Brandt]

Douglas,
Sounds interesting. How does one create a reference?
Thanks
DS

Exactly the way Douglas showed you in his response.