I am trying to import and run a SQL query for multiple DBF files located in one directory. I have been able to get all of the code to work except the RunSQL line. I need "CurrentTable" to be the result of the Import line, not a table name. Is this variable table reference possible?
Any help would be greatly appreciated. Thanks!
Function ImportDBFData()
Dim strPath As String
Dim strFileName As String
strPath = "W:\MS_Lists\TEST\DBFS\"
strTableName = Dir(strPath)
strFileName = Dir(strPath)
Do
DoCmd.TransferDatabase acImport, "dBase III", strPath, acTable, strFileName, Left(strFileName, 7)
DoCmd.RunSQL "SELECT CurrentTable.PC_ABS_NO, CurrentTable.POSTNET INTO DAY1_FOR_UPLOAD FROM CurrentTable WHERE (CurrentTable.PC_ABS_NO) ORDER BY CurrentTable.PC_ABS_NO;"
strFileName = Dir ' Get next entry.
Loop Until strFileName = ""
RetValue = Msgbox("DBF Files Imported")
End Function