Extracting data from Access table with spaces embedded in the name 1

[rosedba]

I have a script that extracts data from an Access database. The tables that need to be extracted are listed in a .txt file. The script works just fine until a table is encountered that has a space embedded in the table name. When I tested my guts of my function that does the extract and I hard-code the table name, it works just fine as long as square brackets surround the table name. However, passing the table name into the function does not work. Any tips would be appreciated! Here's the code:

public function ExportData(objConnection, strTableName, strCSVDirPath)
Dim strCSVFileName, commandstring
strCSVFileName = strTableName & ".csv" 
commandstring = "SELECT * INTO [text;HDR=Yes;Database=" & _
strCSVDirPath & "]." & strCSVFileName & _
" FROM " & "[" & strTableName & "]"
objConnection.Execute commandstring
ExportData = strCSVFileName

End function


'Start of Program

Dim objMyFSO, objFile, strTableName, strFileName
Dim strDBPath, objConnection
Set objMyFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objMyFSO.OpenTextFile("C:\tmp\ARTables.txt", 1)
Set objConnection = CreateObject("ADODB.Connection")
strDBPath = "c:\tmp\db1.mdb"
objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = " & strDBPath 
Do Until objFile.AtEndOfStream
strTableName = objFile.ReadLine
strFileName = ExportData(objConnection, strTableName, "c:\tmp\")
Loop
objFile.Close
objConnection.Close

Thanks in advance for your help!

 

[PHV]

works just fine until ...
What happens ?
Any error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rosedba]

Thank you for your prompt response.

The error message I received is:

Line: 7
Char: 1
Error: Query input must contain at least one table or query
Code: 80004005
Sourcee: Microsoft Jet Database Engine

 

[PHV]

I'd replace this:
strCSVDirPath & "]." & strCSVFileName & _
with something like this:
strCSVDirPath & "].[" & strCSVFileName & "]" & _
or like this:
strCSVDirPath & "].""" & strCSVFileName & """" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rosedba]

Thank you!!! Your suggestion worked brilliantly. I was focused on the line referring to strTableName and did not even consider the problem could be elsewhere. Kudos.