Import csv files to a table in MS Access
Posted on 2012-03-18
I'm planning to build an MS Access db that imports files on button click. Sounds simple but unfortunately i'm quite lost as to where or how to start it...Anyway, below are the things i wanted to happen or what my access db would do.
1.0 On ButtonClick.
2.0 Import all the csv files which follow the fielname convention (C:\Temp\Request_*.csv) to Request table in access. Note all csv's have the same number of columns.
So far my search for solution yielded me this piece of code(see attached) which somehow matches my requirements.
Private Sub cmdButton_Click()
LocateFile ("Request_*.csv") ' uses * for wildcard for strfilename to match.
Function LocateFile(strFileName As String)
Dim csvFile As Variant
.FileName = strFileName
.LookIn = "c:\Temp\"
.SearchSubFolders = False 'do not search in lower folders.
For Each csvFile In .FoundFiles
DoCmd.TransferSpreadsheet acImport, MySpecs, Request,csvFile, True, ""
...Would you be able to help me simplify/modify the code so that it would work on my solution if this would address my requirements otherwise other suggested avenues is welcome.