Help with two *.txt files.

[krismcmillan]

Hi

I'm trying to import two *.txt files into one Access table. I have managed to find some coding, which opens a folder (containing the two .txt files).

Option Compare Database

Sub cmdImport_Click()
Dim strPath As String
Dim strFile As String
Dim strTable As String
Dim strSpecification As String
Dim intImportType As AcTextTransferType
Dim blnHasFieldNames As Boolean

strTable = "tblASN"
strSpecification = ".txt"
blnHasFieldNames = False
intImportType = acImportDelim

' Let user select a folder
With Application.FileDialog(4)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "You didn't select a folder", vbExclamation
Exit Sub
End If
End With
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If

DoCmd.OpenForm "frmMessage"
Forms!frmMessage.Repaint

' Loop through the text files
strFile = Dir(strPath & "*.txt")
Do While strFile <> ""
' Import text file
DoCmd.TransferText _
TransferType:=intImportType, _
SpecificationName:=strSpecification, _
TableName:=strTable, _
FileName:=strPath & strFile, _
HasFieldNames:=blnHasFieldNames
strFile = Dir
Loop

DoCmd.Close acForm, "frmMessage"
End Sub


But I get an Run-Time error 3625,The text file specification '.txt' does not exist. You can import, export, or link using the specification.

I'm not sure if I need to specify the correct file name or not.

Also is there anywhere on the internet, that has a list of codes and help to newbies - like myself?

 

[alansidman]

You will have to create an import specification and save it. You can then use it in a VBA code. To create an import specification, begin to import a txt file manually into Access. When you get to the screen that is defining the fields, click on Advanced. Determine the type of field for each you will import and check appropriately for those that you will not import.

Since you did not indicate which version of Access you are using, I have included a link for AC2003 that will demo this procedure.

Creating an Import Specification in Access 2003 - Access Programmers

Here is an alternative

ACC: How to Create a Schema.ini File Programmatically

 

[krismcmillan]

Hi, sorry I'm using Access 2010.

 

[alansidman]

Makes no difference. You will still need to create an import specification. You do it the same way in 2003 and 2007 and 2010. It is the same. Only the interface icons are different. Instead of File Import in 2003, on the ribbon, you will use External Data-->Import and the same interface will then open.