Import from TXT (different no./name of columns everytime)

[e.badin]

Hi guys,



I have this problem for almost 2 weeks and I don't seem to figure it out.
I need to import from a text file to access data that changes either the name of the columns or the number of columns or both. Also the columns have "spaces" in the name (that's a secondary problem I have). If I make a import spec, I always have the same column names. Is there a way of doing it in VBA?

Thanks!

[e.badin]

Ok, so I've done some research and here is what i made so far:

To create the table using first row of data:

=================================================

Function ifTableExists(TableName As String) As Boolean
Dim rs As Recordset, Db As Database ' DAO Vars
'USAGE: ifTableExists("TABLENAME")
On Error GoTo NoTable 'If there is no table capture the error.
Set Db = CurrentDb()
'If Table is there open it
Set rs = Db.OpenRecordset("Select * from " & TableName & ";")
ifTableExists = True
rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing
Exit Function
NoTable:
ifTableExists = False
End Function

Function TxtReadLn(wPath As String)
On Error GoTo Err_Handle
Const wMode = 1& 'Read Only Mode - Long Value
Dim fso As Object
Dim oFile As Object
Dim tStream As Object
Dim temp As String

Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.GetFile(wPath)
Set tStream = oFile.OpenAsTextStream(wMode)


If ifTableExists("Output1") Then
CurrentDb.Execute "Drop table Output1"
End If

If Not tStream.atendofstream Then
TxtReadLn = tStream.ReadLine
TxtReadLn = Replace(TxtReadLn, "-", "_")
TxtReadLn = Replace(TxtReadLn, " ", "_")
temp = TxtReadLn
TxtReadLn = Replace(TxtReadLn, ",", " text,")
TxtReadLn = "create table Output1(" & TxtReadLn & " text)"
CurrentDb.Execute TxtReadLn

End If


tStream.Close
Set fso = Nothing
Set oFile = Nothing
Set tStream = Nothing

Exit Function
Err_Handle:
If Err.Number = 53 Then
MsgBox "Invalid file or path"
End If
Set fso = Nothing
Set oFile = Nothing
Set tStream = Nothing
End Function

================================================== ===

Then I made a button to that runs the function:
================================================== ===

Private Sub Command0_Click()

TxtReadLn ("C:\Documents and Settings\Administrator\Desktop\output1.txt")
MsgBox TxtReadLn("C:\Documents and Settings\Administrator\Desktop\output1.txt"), vbOKOnly, "Test"

End Sub
================================================== ===


Now I ran into another problem. My fields are separated by "," and I need to find a way of making the insert. You could say it is easy to do after the above code, but I have the scenario where some columns are empty... ",," How can I script the insert to insert null where there is no data?

================================================== ===
================================================== ===
================================================== ===

SOLVED