Put the following code in a standard module name basImport.
Code:
Option Compare Database
Option Explicit
'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder1(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer
With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With
dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder1 = left$(szPath, wPos - 1)
Else
BrowseFolder1 = vbNullString
End If
End Function
'*********** Code End *****************
Public Sub GetScores()
'-- Import all of the student scores to the tblScores table.
Dim OurFolder As String
Dim OurFile As String
'-- First have the user select the folder
OurFolder = BrowseFolder1("Select the folder that contains the scores.")
'-- Dir returns a ZLS ("") when no more files match the criteria
OurFile = Dir(OurFolder & "\*.txt")
Do While OurFile <> ""
'-- I created and saved the ScoreImportSpecs earlier.
'-- I also created the tblScores table with the following
'-- four text fields:
'-- FirstName, LastName, ClassCode, Score
DoCmd.TransferText acImportDelim, "ScoreImportSpecs", "tblScores", OurFile
'-- Get the next *.txt filename
OurFile = Dir
Loop
End Sub
Then call GetScores from the Click event of a CommandButton.
You will need to create the tblScores first and the Import Specification and save it as ScoreImportSpecs.