Import text file fixed-width to access 2003 1

[dinicholls]

Hi,

I have a text file that I need to import into access. The table is big. I know I can import it via the wizard, but I need to do this on a regular basis. I know the lenth of the fields and the position. I believe you can use transfertext, in VB, but I'm not familiar with VB, can someone just give me a brief idea, and I'll get it from there.

Thanks

 

[Remou]

First import by hand and, at the last screen when you have the import set-up the way it should be, choose Advanced and save the Import Specification. Choose a suitable name, you will need it in a second. Next, decide whether you want a macro or code, I would nearly always prefer code, but it depends on how this is to be used. Finally, create the code or macro using TransferText.

Here are the details:

http://msdn.microsoft.com/en-us/library/aa193080(office.10).aspx

What the code would probably look like:

[tt]DoCmd.TransferText acImport, "BigFileImportSpec", "NewOrExistingTableName", "C:\Docs\BigFile.txt", True[/tt]

There are a few difficulties that you may encounter, such as Access can be fussy about extensions, but try it and see how you get on.