Refer to Variable in strSQL

[Guest]

Trying to run an Update query immediately after the file import in order to
capture the name of the file associated with the data. I must have a syntax
problem because I'm getting the word strFileName in the table, instead of the
actual file name. Where am I going wrong?

Dim strPath As String
Dim strFileName As String
Dim strSQL As String
Dim dbf As Database

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelATL_PEN_DETAIL", acViewNormal, acEdit ' Clear the
Pen_Detail table

Set dbf = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\PEN\2007\07\Atlantic\Import\ALL REGIONS\"
strFileName = Dir$(strPath & "*_Detail.csv") ' Retrieve the first entry.
Do While Len(strFileName) > 0 ' Start the loop.
DoCmd.TransferText acImportDelim, "ATL_PEN_Detail Import Spec",
"tblATL_PEN_DETAIL", strPath & strFileName, True
strSQL = "UPDATE tblATL_PEN_DETAIL SET FileName = 'strFileName' WHERE
FileName Is Null"

CurrentDb.Execute strSQL, dbFailOnError
strFileName = Dir$() ' Get next entry.
Loop

MsgBox "Objects imported successfully!", vbInformation, "Import Status"

 

[Dirk Goldgar]

In

Trying to run an Update query immediately after the file import in
order to capture the name of the file associated with the data. I
must have a syntax problem because I'm getting the word strFileName
in the table, instead of the actual file name. Where am I going
wrong?

Dim strPath As String
Dim strFileName As String
Dim strSQL As String
Dim dbf As Database

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdelATL_PEN_DETAIL", acViewNormal, acEdit ' Clear
the Pen_Detail table

Set dbf = CurrentDb
' Set the path to the directory where the files will be.
strPath = "C:\Documents and Settings\kphillips\My
Documents\Kirk\PEN\2007\07\Atlantic\Import\ALL REGIONS\"
strFileName = Dir$(strPath & "*_Detail.csv") ' Retrieve the first
entry.
Do While Len(strFileName) > 0 ' Start the loop.
DoCmd.TransferText acImportDelim, "ATL_PEN_Detail Import Spec",
"tblATL_PEN_DETAIL", strPath & strFileName, True
strSQL = "UPDATE tblATL_PEN_DETAIL SET FileName = 'strFileName'
WHERE FileName Is Null"

CurrentDb.Execute strSQL, dbFailOnError
strFileName = Dir$() ' Get next entry.
Loop

MsgBox "Objects imported successfully!", vbInformation, "Import
Status"

You need to use concatenation to embed the value of the variable into
the SQL string, rather than its name. Try this:

strSQL = _
"UPDATE tblATL_PEN_DETAIL SET FileName = '" & _
strFileName & _
"' WHERE FileName Is Null"

 

[Guest]

Thanks Dirk

In

You need to use concatenation to embed the value of the variable into
the SQL string, rather than its name. Try this:

strSQL = _
"UPDATE tblATL_PEN_DETAIL SET FileName = '" & _
strFileName & _
"' WHERE FileName Is Null"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)