Variable Table Names in a Module

[Jessica]

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

[pbaldy]

Sure. Presuming you get the value into a variable, like this:

"..INTO DAY1_FOR_UPLOAD FROM " & VariableName & " WHERE..."

Personally I wouldn't bother adding the table name to the field names in the SELECT clause, given the simplicity of the SQL, but you can do the same thing there if desired.