HELP!! import data and process in Access

[glmleilei]

Hi friends,I have 4 csv files, that need to be imported into Access.
1. I'm building 4 linked tables so users can update the table as needed. Then run queries based on these tables to produce report. But maybe there's a better way? the file name and location might be dymatic, so anything like 'getopenfilename' would be easier instead of linked tables?


2. These data need to be clean up first to become a 'database table'. (Delete first 2 rows, delete some columns, remove duplicates etc.) I know how to do them in VBA Excel, but never used Macro in Access before. Please advise
Thank you !!

[ranman256]

Have the user pick a file using the Microsoft pick app

usage:
txtBox = UserPick1File("c:\folder\")

Public Function UserPick1File( Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String


'===================
'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================

With Application.FileDialog(msoFileDialogFilePicker)   
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls;*.xlsx"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

[glmleilei]

Thanks so much!! worked perfectly!

then i can import file by
DoCmd.TransferSpreadsheet acImport, , "testspreasheet", UserPick1File, True