Variables within INSERT INTO statement?

  • Thread starter Thread starter John G
  • Start date Start date
J

John G

Hi,
I am trying to append records from one database to another. The target
database is located in a subfolder of 'My Documents' in XP ('Documents' in
Vista). Is it possible to use a variable path to the target database in the
INSERT INTO statement? The following code results in an error stating "Could
not find the file:C:\Documents and Settings\username\My Documents\strDBPath".
Error number 3024.

Dim strDBPath As String

strDBPath = SpecFolder(CSIDL_PERSONAL) & "\myDataFolder\DATA.mdb"

Dim SQL As String

SQL = "INSERT INTO tblChronLog ( LogDate, Activity, Initials ) IN
'strDBPath'" & _
"SELECT tblChronLog.LogDate, tblChronLog.Activity,
tblChronLog.Initials, *" & _
"FROM [tblChronologicalLog];"
DoCmd.RunSQL SQL

It is not reading "strDBPath" correctly because it is looking for a file
named strDBPath.

Do I need to specify the exact path to the target database or is it a
problem with the quotes within the statement?

Any advice would be appreciated.
John G
 
try

"INSERT INTO tblChronLog ( LogDate, Activity, Initials ) " _
& "IN " & strDBPath & " SELECT LogDate, " _
& "Activity, Initials, FROM tblChronologicalLog"

the middle line is where i corrected the syntax. the other changes are
correct, but necessary; i just made them to save space.

i'm not entirely sure that the filepath does not need to be in quotes. so if
the above errs out, try modifying the second line to

& "IN '" & strDBPath & "' SELECT LogDate, " _

hth
 
Thank you Tina! Just what I needed!
It requires the quotes as you had in this line.
& "IN '" & strDBPath & "' SELECT LogDate, "

John G

tina said:
try

"INSERT INTO tblChronLog ( LogDate, Activity, Initials ) " _
& "IN " & strDBPath & " SELECT LogDate, " _
& "Activity, Initials, FROM tblChronologicalLog"

the middle line is where i corrected the syntax. the other changes are
correct, but necessary; i just made them to save space.

i'm not entirely sure that the filepath does not need to be in quotes. so
if
the above errs out, try modifying the second line to

& "IN '" & strDBPath & "' SELECT LogDate, " _

hth


John G said:
Hi,
I am trying to append records from one database to another. The target
database is located in a subfolder of 'My Documents' in XP ('Documents'
in
Vista). Is it possible to use a variable path to the target database in the
INSERT INTO statement? The following code results in an error stating "Could
not find the file:C:\Documents and Settings\username\My Documents\strDBPath".
Error number 3024.

Dim strDBPath As String

strDBPath = SpecFolder(CSIDL_PERSONAL) & "\myDataFolder\DATA.mdb"

Dim SQL As String

SQL = "INSERT INTO tblChronLog ( LogDate, Activity, Initials ) IN
'strDBPath'" & _
"SELECT tblChronLog.LogDate, tblChronLog.Activity,
tblChronLog.Initials, *" & _
"FROM [tblChronologicalLog];"
DoCmd.RunSQL SQL

It is not reading "strDBPath" correctly because it is looking for a file
named strDBPath.

Do I need to specify the exact path to the target database or is it a
problem with the quotes within the statement?

Any advice would be appreciated.
John G
Click to expand...
Click to expand...
 
Back
Top