How to Upload Excel Data into SQL with Access Vba?

Question

I have large data in Excel that I need to upload to SQL Server but I am using Access as a Front End. Number of columns in Excel are around 90 and number of records goes above 700,000

Welcome to StackOverflow. Please read https://stackoverflow.com/help/how-to-ask, and try to improve your question with further information. What have you already tried, what is not working, etc. — Erik A, May 24 '17 at 07:19
Download SQL Server Management studio and query the file with the help of Access OLEDB provider using OPENROWSET. — PacoDePaco, May 24 '17 at 09:37
Hi Pawel, I tried below code but it gives me error or object not found since I am using "Match" function. First checking Columns of Excel data with SQL columns For col = 0 To .Fields.count - 1 index = xlApp.Application.Match(.Fields(col).Name, sourceRange.Rows(2), 0) If index > 0 Then exportFieldsCount = exportFieldsCount + 1 tableFields(exportFieldsCount) = col rangeFields(exportFieldsCount) = index End If Next And then adding data into Recordset. But Recordset.UpdateBatch is also not working — Renjith R, May 24 '17 at 09:45
For col = 0 To .Fields.count - 1 index = xlApp.Application.Match(.Fields(col).Name, sourceRange.Rows(2), 0) If index > 0 Then exportFieldsCount = exportFieldsCount + 1 tableFields(exportFieldsCount) = col rangeFields(exportFieldsCount) = index End If Next above code gives me Method Not found since I am using Access but function is of Excel — Renjith R, May 24 '17 at 09:52
[Here](https://stackoverflow.com/questions/24150739/code-to-read-xlsx-sheet-into-a-table-in-a-sql-server-database) you can find what you need once you are connected to your server using SSMS. You can also use data import tool in SSMS. — PacoDePaco, May 24 '17 at 10:33

score 0 · Answer 1 · answered Jun 23 '17 at 21:12

I just answered a question like this about an hour ago.

Sub Button_Click()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub

errH:
    MsgBox Err.Description
End Sub

Also, check out these links.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

http://tomaslind.net/2013/12/26/export-data-excel-to-sql-server/

How to Upload Excel Data into SQL with Access Vba?

1 Answers1