Hi there

I'm using ADO from within an Excel VBA procedure to import a fairly large
text file (1GB+) into an Access database.

Everything's running smoothly (thanks to the help from this group!), except
when I get to the final INSERT INTO statement. My code (a bit truncated) is
as follows ...

Dim cnNew As ADODB.Connection
Dim catNew As ADOX.Catalog
Dim tblNew As ADOX.Table
Dim colNew As ADOX.Column

strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DestinationFile.mdb;"
Set cnNew = New ADODB.Connection
cnNew.Open strConnect

Set catNew = New ADOX.Catalog
Set catNew.ActiveConnection = cnNew

Set tblNew = New ADOX.Table
tblNew.Name = tblName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\PathToTextFile\TextFile.tx t", 1)
strColHeaders = objFile.ReadLine
objFile.Close

arrColHeaders = Split(strColHeaders, "|", -1, 1)

For Each strColHeader in arrColHeaders
Set colNew = New ADOX.Column
colNew.Name = strColHeader
colNew.Type = adVarWChar
colNew.DefinedSize = 24
colNew.Attributes = adColNullable
tblNew.Columns.Append colNew.Name, colNew.Type, colNew.DefinedSize
Next

catNew.Tables.Append tblNew

cnNew.Execute "INSERT INTO " & tblName & " SELECT * FROM
[Text;Database=C:\PathToTextFile\;HDR=YES].[" & txtFileName & ".txt]"

At this point I get a run-time error '-2147217900 (80040e14)' ~ "The INSERT
INTO statement contains the following unknown field name: 'Inv# To'. Make
sure you have typed the name correctly, and try the operation again."

I don't know where the "#" character arises... For instance, when I manually
import the text file into an Access database, the field/column name (in
design view) is given as "Inv To" (words separated by a space), and the same
result is achieved if I import the text file (at least the first 2^16 rows
of the text file) into Excel.

However, if I view the text file in Windows Commander (the file is too big
to view in Notepad etc.), the field name is given as "Inv. To" (both a space
and a period), while the error message bizarrely suggests that the field
name contains a "#".

I'm about ready to fly off the handle!

Please help
Loane