Help with SQL

[Kitty]

I am attempting to convert a query into a SQL statement in
code, then feed a variable for part of the SQL statement.

The original query appended records from a table in one
database to a table in another database. Multiple people
will be appending records to the receiving database and
the receiving database could be in different locations
depending on the users.

The user can select the drive letter where the receiving
database is located. That letter is in a variable
called "TransferDrive."

The SQL from the append query is:
INSERT INTO tblSample IN 'E:\TEMP.MDB'
SELECT DISTINCTROW tblSample.*
FROM tblSample;


My current code results in an error message that, "The
SELECT statement includes a reserved word or an argument
name that is mispelled or missing, or the punctuation is
incorrect."

Current Code...
Dim SQL As String

SQL = "Select distinctrow tblSample.*" & _
"into tblSample In (TransferDrive
& 'Temp.mdb')" & _
"From tblSample"
DoCmd.RunSQL SQL
MsgBox "The records have been successfully exported.",
vbOKOnly


If I remove TransferDrive and hard code E:\, the code
runs. However, it inserts the records once and does not
append them if I rerun the code, which I want it to do
(one person wouldn't append twice, but others need to
append).

Thanks for your help.

Kitty

 

[Albert D. Kallal]

The SQL from the append query is:
INSERT INTO tblSample IN 'E:\TEMP.MDB'
SELECT DISTINCTROW tblSample.*
FROM tblSample;


Current Code...
Dim SQL As String

SQL = "Select distinctrow tblSample.*" & _
"into tblSample In (TransferDrive
& 'Temp.mdb')" & _
"From tblSample"
DoCmd.RunSQL SQL
MsgBox "The records have been successfully exported.",
vbOKOnly

You need as the first example shoews to have single quotes around it. So,
try:

SQL = "Select distinctrow tblSample.*" & _
"into tblSample In '" & TransferDrive & _
"Temp.mdb' From tblSample"

msgbox SQL

Also, place a msgbox in your code for testing untill it works

 

[Kitty]

Thanks!