Read Text Files - Newbie Question

[sibleytr]

I have a series of Text files that I need to read into an Access Table.

I've used DoCmd.transferDatabase in the past to import data from a single Mdb however the DoCmd.TransferText acImportFixed is kicking my butt. Especially the SpecificationName element and the need to scan multiple files.

All text files are contained in a single folder on the network. I need to be able to import the data from each text file. (Single column, 10 CHR)

'single file import attempt for example
DoCmd.TransferText acImportFixed, "", "tblProjectName", "\\Tnpsetup\archive\@Txt\2005-A.Txt", True, ""
' result is error

Any example code would be helpful.

 

[jbpez]

First you will need to import one file manually to create the specification element without actually importing the file.
After you set up all the importing spec click 'Advanced' and click 'Save As' and save as whatever name.
That name will be your SpecificationName for the DoCmd.TransferText acImportFixed, "whatever name",....

Then try the following:

Dim fso As Object
Dim filecoll As Object
Dim fFolder As Object
Dim fil
Dim strFile As String
Dim strFolder As String

Set fso = CreateObject("Scripting.FileSystemObject")
strFolder = "\\Tnpsetup\archive\@Txt"
Set fFolder = fso.GetFolder(strFolder)

Set filecoll = fFolder.Files

For Each fil In filecoll
strFile = fil.Name
strFile = strFolder & "\" & strFile
DoCmd.TransferText acImportFixed, "whatever name", "tblProjectName", strFile, True, ""
Next

John Borges

 

[sibleytr]

Excelent! Works like a charm. Thanks.

It will take me a little while to dysect the code to determine all the object commands, but that's part of the learning curve.

My follow up questions:
Do you know where Access stores the SpecificationName?
(I can see the name I saved it as under the Advanced button on the import data, but nowhere else do I see the name)

I'm receiving a ...Run-time error 3027. Cannot update. Database or object is read-only... Is that do to no endOf statement, or do I need to build an error trap to take me out of the module when this error occurs?

Thanks again

 

[HitechUser]

Check out this Freeware tool. It has an import/export specifcations tool

V-Tools

HTH......

 

[jbpez]

To the question:
Do you know where Access stores the SpecificationName?

System table: MsysIMEXSpecs

John Borges

 

[jbpez]

Are you importing text files .txt ? or what ?

John Borges

 

[jbpez]

This could be your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;304206

John Borges