Access.2007.VBA.Bibl..

The “Inspect New Jobs from Text File” command button’s event procedure first checks that a text

file has been selected and then sets variables with the name of the target table for importing and the

import type. Next, a Select Casestatement handles the two types of import (comma-delimited

and fixed-width) separately, using the TransferTextstatement with different arguments:

Private Sub cmdInspectJobs_Click()

On Error GoTo ErrorHandler

Dim strText As String

Dim strTitle As String

Dim strPrompt As String

Dim txtData As Access.TextBox

Dim strTextFile As String

Dim strTable As String

Dim strSpec As String

Set txtData = Me![txtSelectedTextFile]

strTextFile = Nz(txtData.Value)

If strTextFile = “” Then

strTitle = “No text file selected”

strPrompt = “Please select a text file”

MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, title:=strTitle

GoTo ErrorHandlerExit

Else

Debug.Print “Text file: “ & strTextFile

End If

strTable = “tblNewJobs”

intTextType = Nz(Me![fraTextType].Value, 1)

Select Case intTextType

Case 1

Comma-delimited:

DoCmd.TransferText transfertype:=acImportDelim, _

TableName:=strTable, _

FileName:=strTextFile, _

hasfieldnames:=True

Case 2

Fixed-width

strSpec = “Import-Jobs 13-Aug-2006”

‘New style syntax

Part II Writing VBA Code to Exchange Data between Office Components