Import Text File Using VBA

[bbrendan]

Hi All,

I need some help regarding importing a fixed width text file.

Does anyone know how to programatically import a fixed width flat file into an MSSQL table.

Note I cannot use the docmd.transfertext, as I am using an Access Data Project ADP. As in ADP it doesnt allow the use of the access2000 Specification files


Note I need it to be in ADO

I have looked at this example but cannot get it to work in ADO.
-----------------------------------------
Dim Dbs as Database
Dim Rst as Recordset
Dim FileName

Set Dbs = CurrentDb
Set Rst=dbs.OpenRecordset"TableToAddInfo",dbOpenDynaset)

Open "C:\Temp\" & FileName & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, LineData
Rst.AddNew
Rst!FieldName = LineData
Rst.Update
Loop
Close #1
Rst.Close

--------------------------------------------

any help would be great!

 

[p27br]

I think you need to declare an integer (call it iFileNumber for example) and add the instruction
iFileNum = FreeFile
Open sFileName For Input As iFileNum