Access VBA: Adding 'filename' field when importing multiple files
<p>I have some working code that loops through a folder full of Excel files, and imports a table from each into the an Access table.  All I'm trying to do is add a field at the end of the table called <em>FileName</em> that has the name of the source Excel file.</p>

<p>I've done some Googling and found this solution:
<a href="https://stackoverflow.com/questions/37861815/how-to-add-file-name-when-importing-multiple-excel-files-to-one-access-table">How to add file name when importing multiple Excel files to one Access table</a></p>

<p>I've tried to incorporate the solution into my code, but when I reach the execute statement, I get:</p>

<blockquote>
  <p>Run-time error '3061' Too few parameters.  Expected 2.</p>
</blockquote>

<p>I think the problem is just with the <code>strSQL</code> statement and/or the way I'm executing it at the end.    </p>

<pre class="lang-vb prettyprint-override"><code>Public Sub Command0_Click()

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
'make the UPDATE a parameter query ...
strSQL = "UPDATE Test SET FileName=[pFileName] WHERE FileName Is Null OR
FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

path = "C:\Users\u005984\Desktop\Test\"

'Loop through the folder &amp; build file list
strFile = Dir(path &amp; "*.xlsx")

While strFile &lt;&gt; ""
   'add files to the list
   intFile = intFile + 1
   ReDim Preserve strFileList(1 To intFile)
   strFileList(intFile) = strFile
   strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
    MsgBox "No files found"
    Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
    filename = path &amp; strFileList(intFile)
    DoCmd.TransferSpreadsheet acImport, 9, "Test", filename, True

    'Add filename field
    qdf.Parameters("pFileName").Value = strFileList(intFile)
    qdf.Execute dbFailOnError   
Next intFile

End Sub
</code></pre>

<p>I'm new to Access VBA and SQL, and can't figure out why its expecting 2 parameters. Grateful for any help.</p>