Question How to Import New Data from MS Access Front End with SQL Server back end

[Mari111]

Hello,

I need to find a way to import new data into a database which comprises of a SQL Server back end and an MS Access 2010 front end, from the front end.

Ideally, this would involve a user clicking a button on a form in the front end and selecting an excel spreadsheet with new data to import, which would then be saved in the back end SQL Server tables.

Is there a way to do this?

I am currently using the Import/Export Wizard in SSMS 2016 but this is laborious when needed to be repeated several times in a process. We also want to keep access to SSMS restricted to senior users, whilst letting more junior users run the process.

Any suggestions?

 

[Ranman256]

If you import the same format sheet everytime, then take 1 file and
save as to the same file everytime: c:\temp\File2Import.xlsx

attach this file as an external linked table: tFile2Import

build an append query to add this data to the internal data table
then run the query

then the steps will be:
1. overwrite the file, c:\temp\File2Import.xlsx, with the new data
2. run query
done


button click to begin import...

sub btnImport_Click()
const kTARG = "c:\temp\File2Import.xlsx"

vFile = UserPick1File("c:\folder\")

if vFile <> "" then 
   filecopy vFile, kTARG
   docmd.openquery "qaImportXL"
   msgbox "Done"
endif

end sub


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

If IsMissing(pvPath) Then pvPath = "c:\"

With Application.FileDialog(msoFileDialogFilePicker)   'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add sDecr, sExt
    .InitialFileName = pvPath
    .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

 

[arnelgp]

firstly, you need to create a link (link table)
of the table in SQL server to your access db.

next create the form that will do the import
to the linked table.

add a button (for import).

create a reference (VBA->Tools->Reference) to
Microsoft Office XX.XX Object Library. (XX,XX is the verson you have).

copy and paste this code in a Standard Module (Vba):

Public Function ExcelPicker(Optional strFileName As String, _
    Optional ByVal strWindowTitle As String = "Select an excel file") As String

    Dim fd As Office.FileDialog
    
    'Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Title = strWindowTitle
        .Filters.Add "All files", "*.*", 1
        .Filters.Add "Excel Workbooks", "*.xls;*.xlsx;*.xlsm", 2
        .AllowMultiSelect = False
        If .Show = -1 Then
            FilePicker = (.SelectedItems(1))
        Else
            FilePicker = vbNullString
        End If
    End With
    Set fd = Nothing
End Function

Back to your form, on the click event of the button:

Private Sub button_Click()
Dim sExcelFile As String
sExcelFile = ExcelPicker()
'' if sExcelFile is not blank then
'' import the file to MSSQL linked table
If sExcelFile <> "" Then
	Docmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel12Xml,"yourLinkedTableNameHere",sExcelFile,True
End If
End Sub

 

[Mari111]

Thanks for this, I will give it a try.

 

[Mari111]

Arnelgp,

MS Access 2010 is throwing up an error with the code behind the button.

The error says: Compile error: Sub or Function not defined

It is highlighting in yellow the ExcelPicker ()

What do I need to do to solve this?

Thanks for your help so far, it looks like the code is trying to do what I want it to.

 

[arnelgp]

Soryy, from the code replace filepicker string with ExcelPicker

 

[Mari111]

Um, so I replaced ExcelPicker() with FilePicker() but the same error happened.

Could you be clearer and write it out please? My current code is:

Private Sub cmdImportFilterResults_Click()
Dim sExcelFile As String
sExcelFile = ExcelPicker()
'' if sExcelFile is not blank then
'' import the file to MSSQL linked table
If sExcelFile <> "" Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "[Database].Table_FilterResult", sExcelFile, True
End If
End Sub

It's highlighting the FilePicker() as an error and also ExcelPicker() as an error on the first try. Thanks.

 

[Mari111]

Ranman256, do you have any other suggestions? Your solution looks good although I don't see how it links to the sql server end?
Thanks

 

[arnelgp]

Replace also on the other code i gave

 

[Mari111]

Ok thanks. I did this but the same compile error comes up and it highlights the ExcelPicker() in the Access vba editor again.

Where is it going wrong?

 

[Mari111]

So, I've managed to get the file picker dialog box to come up. Now it's throwing a run time 2522 error on the DoCmd.TransferSpreadsheet line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "[DatabaseName].Table_Result", sExcelFile, True

Can anyone correct this code?

 

[arnelgp]

Removd the [databasename].