Import Button OnClick VBA Code

[Mari111]

Hello,

I have some Access VBA code behind a button in a front end MS Access 2010 to select excel files and import them (as new data) into a linked SQL Server table (the back end).

Opening up the file selector dialog box is working well, but code throws a 2522 run time error 'Needs a file name argument' on the DoCmd.TransferSpreadsheet code:

Private Sub cmdImportResults_Click()
Dim sExcelFile As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = True
'Set the title of the dialog box
.Title = "Please select one or more files"
'Show the dialog box. If the .Show method returns True, the user picked at least one file.
If .Show = True Then
'Loop through each file selected and add it to the table
'' import the file to MSSQL linked table
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "[Database].Table_Results", sExcelFile, True
End If
End With
End Sub

Can anyone solve the run time error and my code?

Thanks

 

[Ranman256]

you did not assign the filename to : sExcelFile

If .Show = True Then
   sExcelFile =.SelectedItems(1)
   DoCmd.TransferSpreadsheet....
endif

but you have .AllowMultiSelect = True
so that means users can select many files to import?
if so you must cycle thru the .SelectedItems.count

 

[MarkK]

Here's some code you might consider. This loops thru the results returned by the FileDialog if there are many.

Private Sub cmdImportResults_Click()
    Dim vFile
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Title = "Please select one or more files"
        If .Show Then
            For Each vFile In .SelectedItems
                Debug.Print vFile
            Next
        Else
            Debug.Print "Import Cancelled!!!"
        End If
    End With
End Sub

Then you'll want to do the import in the loop.
hth
Mark

 

[Mari111]

Hi Ranman,

Thanks that worked. However, Access created an Access table with the name I specified instead of appending the new data to the existing linked SQL Server table.

How do I now transfer the data in the newly created Access table to the linked SQL Server table?

 

[Mari111]

Hi MarkK,


Thanks for your suggestion too. I've changed my code so to select only one file for the moment, instead of many files.

My code is now:

Private Sub cmdImportFilterResults_Click()
Dim sExcelFile As String
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = False
'Set the title of the dialog box
.Title = "Please select a file"
'Show the dialog box. If the .Show method returns True, the user picked at least one file.
If .Show = True Then
sExcelFile = .SelectedItems(1)

'' import the file to the linked sql server table
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "[Database].Table_Result", sExcelFile, True
End If
End With
End Sub

How do I now transfer the new data from the (temp) created Access table to the SQL Server linked table?

 

[Mark_]

You would normally have an append query that goes through all records in your "Temp" table and appends them to the real table. This is normally not done during the import as most times you will want to review and validate the data prior to adding it. This avoids issues when you have bad data in an incoming spreadsheet.

 

[Mari111]

Thank you Mark,

I created an append query and it worked. Now I just need to find code for dropping the internal Access temp table so that it can be re-created next time the import button is clicked.

We have our own data checking processes so I'm not too concerned about the data validation.

 

[Mari111]

Hi,

I'm having trouble with my code to delete the temp table I've created.

My code is

DoCmd.Close acTable, "TblTemp_Table"
 
DoCmd.DeleteObject acTable, "TblTemp_Table"

It's recognising the DoCmd.Close command but not recognising the DoCmd.DeleteObject command line. It's throwing a run time error 7874 MS Access can't find object 'TblTemp_Table' even though the table definitely still exists, I've just closed it.

I've tried removing the DoCmd.Close acTable line whilst leaving the DoCmd.DeleteObject line, but the same error appears.

How do I make this code work

 

[Mark_]

We have our own data checking processes so I'm not too concerned about the data validation.

You may KNOW that the data is good coming in, but I'd still have a way to review it anyhow. This is a case much like where a gun owner checks to make sure the weapon is empty even when they KNOW there isn't a round in the chamber. Better to be safe than suffer the consequences.