simple import form in Access

Question

how to create an access form which has import excel file button. and after selecting excel file it automatically creates a table in the database with collumn headers as excel first row and data as excel other rows. if you think i am not putting any effort please give me suggestion or reference and ill do it on my own.

Answer 1

If we assume that you are able to create a form and wire up a button you have two issues:

1. The file open dialog.
2. Triggering the import.

For 1 you should be able to use the standard Microsoft file dialogs - my VB.OLD and Access are spectacularly rusty (no access 2007) but you can reference the appropriate COM assemblies from Access after which it becomes fairly easy.

2 is a bit more interesting - I beleive you can pretty much do this by menu selection from within access in which case, at least as a first step, you should be able to automate the same steps - pretty much anything you can do from a menu you can also do by calling the relevant command from VBA. The more complex solution would be to create VBA logic to create a linked table that links to the Excel file and then do a create table query and then drop the link.

In terms of effort, the form is something one would expect you to be able to do without much help - however automating something like an import from excel is not necessarily obvious.

Answer 2

For versions of Access since 2003, you can use the File Dialog to allow the user to browse for the file they want, prior to that, you can use API calls. If this is overkill for you, you can have the user type in the file name and path, but you will have to check that it exists using code (Dir may suit).

It would be best to use TransferSpreadsheet method of the DoCmd object (available in any version of Access from, AFAIK, 1997 onward) to import the spreadsheet. This can be run as VBA (code) or a macro.

Answer 3

An example using Access 2003 would be as follows for selecting a file:

Dim fDialog  As Office.FileDialog
Dim strFile As String
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .InitialFileName = "C:\temp\*.xls"
    .Filters.Clear
    .Filters.Add "Excel file", "*.xls"
    .Filters.Add "All Files", "*.*"
    If .Show = True Then
        strFile = .SelectedItems(1)
    End If

End With

Debug.Print strFile

Note you would need to add a Reference to the Office 12 Object Library

To Import the file you can use the TransferSpreadsheet Function of the DoCmd Object. For E.g.

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ExcelImport", strFile, True

The Access table called ExcelImport would have to already exist in the database.