troubleshooting Question

Import csv files to a table in MS Access

Avatar of jsuanque
jsuanqueFlag for Australia asked on
Microsoft Access
9 Comments3 Solutions848 ViewsLast Modified:
Hello Experts,
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.
End Sub

Function LocateFile(strFileName As String)
   Dim csvFile As Variant
   With Application.FileSearch
      .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, ""
      Next csvFile
   End With

End Function

...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.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros