importing data from csv file into an access table

[ransg]

Hello,

Code beneath worked perfectly with Access 2003.
Now we switched to Access 2010 and it generates a table where data isn't put into my 77 fields like before, but seperated into 1 field, separated by some ;;;.
I am not good with programming. So can someone trow an eye on this?


Dim rst_data As Recordset
Dim oldname As String, newname As String
DoCmd.DeleteObject acTable, "TBL_import_TPXP_Radi_Evvd"
DoCmd.TransferText acImportDelim, , "TBL_import_TPXP_Radi_Evvd", "N:\APPLSHARE\PRD\QSIG\MKTDISTPWB\USPB1815\RADIEV\IMPACT.CSV", False, ""

'name conversion'changefieldnames
Set rst_data = CurrentDb.OpenRecordset("TBL_field_names")
With rst_data
.MoveFirst
Do Until .EOF
oldname = .Fields(0).Value
newname = .Fields(1).Value
changefieldnames oldname, newname
.MoveNext
Loop
End With
Set rst_data = Nothing
DoCmd.RunSQL "ALTER TABLE [TBL_import_TPXP_Radi_Evvd] ALTER COLUMN [Ontlener] text"
DoCmd.RunSQL "UPDATE TBL_import_TPXP_Radi_Evvd SET TBL_import_TPXP_Radi_Evvd.Ontlener = Right('000000000000' & [TBL_import_TPXP_Radi_Evvd]![Ontlener],12);"
MsgBox "New data has been imported"
End sub

 

[ransg]

Is there someone who can look at this one?
Regards
Geert

 

[way2bord]

Is there someone who can look at this one?
Regards
Geert

If I remember correctly, DoCmd.TransferText defaults to comma as a delimiter (aka. comma separated values - .csv)

Add in a VBA string to replace all semicolon's in your source text file with commas and try again.

Link to function you want
http://www.exceltip.com/st/Replace_text_in_a_text_file_using_VBA_in_Microsoft_Excel/468.html