Storing a Variable Using SQL in VBA

[CC]

I am importing files and tyring to store each file name in a table named
FileNameHistory. The code is finding the .txt files and importing them to
the correct table, but I can't get the SQL in the code to store the file
names in the FileNameHistory table. Can someone help? The SQL is:

DoCmd.RunSQL "UPDATE FileNameHistory SET FileName = strFileName"

strFileName is the variable. This is in a loop so each file will be
imported. When I get the file names to update the FileNameHistory table, I
will check it and only import file names that have not already been imported.

 

[Stefan Hoffmann]

hi,

names in the FileNameHistory table. Can someone help? The SQL is:

DoCmd.RunSQL "UPDATE FileNameHistory SET FileName = strFileName"

Use

Dim SQL As String

SQL = "INSERT INTO FileNameHistory (FileName) " & _
"VALUES('" & Replace(strFileName, "'", "''") & "')"
CurrentDb.Execute SQL, dbFailOnError

to append a record, otherwise

SQL = "UPDATE FileNameHistory " & _
"SET FileName = '" & Replace(strFileName, "'", "''") & "'"


mfG
--> stefan <--

 

[CC]

Your code works perfect. Thanks!

hi,

Use

Dim SQL As String

SQL = "INSERT INTO FileNameHistory (FileName) " & _
"VALUES('" & Replace(strFileName, "'", "''") & "')"
CurrentDb.Execute SQL, dbFailOnError

to append a record, otherwise

SQL = "UPDATE FileNameHistory " & _
"SET FileName = '" & Replace(strFileName, "'", "''") & "'"


mfG
--> stefan <--