Storing a Variable Using SQL in VBA

  • Thread starter Thread starter CC
  • Start date Start date
C

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.
 
hi,
names in the FileNameHistory table. Can someone help? The SQL is:

DoCmd.RunSQL "UPDATE FileNameHistory SET FileName = strFileName"
Click to expand...
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 <--
 
Your code works perfect. Thanks!

Stefan Hoffmann said:
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 <--
Click to expand...
 
Back
Top