How do you INSERT a range from Excel to an SQL table-CodePudding

I am using an old macro that sends a defined range from Excel to an MS Access database, and would like to adapt it to send to an SQL Server database.

Old Code (works very well, I am not the author):

'ExportAccess

Dim db As DAO.Database
Dim Rst As DAO.Recordset

Dim localMDB As String 'this is the address of the access mdb, removed from this snippit

sht = ActiveCell.Worksheet.Name
With Worksheets(sht)
   .Range("L1:A" & .Range("A65536").End(xlUp).Row).Name = "Range"
End With

Set db = OpenDatabase(ActiveWorkbook.FullName, False, False, "excel 8.0")

db.Execute "INSERT INTO myTable IN '" & localMDB & "' SELECT * FROM [Range]", dbFailOnError

My attempt at modification:

Dim db As DAO.Database 'sql database
Dim rs As DAO.Recordset
  
Dim bd As DAO.Database 'excel sheet?
Dim Rst As DAO.Recordset
    
Set db = OpenDatabase("myDatabase", dbDriverNoPrompt, False, "ODBC;DATABASE=DB_Backup;DSN=myDatabase") 

sht = ActiveCell.Worksheet.Name
With Worksheets(sht)
   .Range("B1:A" & .Range("A65536").End(xlUp).Row).Name = "Range"
End With
    
db.Execute "INSERT INTO myTable SELECT * FROM [Range]", dbFailOnError

When I run my attempt, it gives the error that my "range" is not defined. Any help would be greatly appreciated, thanks!

CodePudding user response：

The reason the first code block worked successfully is that you connected to the Microsoft Access Jet/ACE Engine which can query Access database tables, Excel workbooks, even CSV text files. Notice how db is set directly to an Excel workbook and the append query externally interfaces to an Access database. This syntax is only supported with the Jet/ACE Engine.

However, in second code block you are connecting to an external database, namely SQL Server, and not the Jet/ACE Engine. Therefore, the analogous syntax is not supported. Specifically, as error indicates, [Range] does not exist because you are not connected to a workbook. You will need to specify all cell data of the range in VBA for appropriate data migration. Do not conflate SQL Server with MS Office even though they are products of same company.

Consider ADO (rather than DAO) for parameterization of values. Be sure to explicitly name columns in append SQL query. While your actual range is uncertain, below loops down the first column of range and uses .Offset to walk across the columns in current row. Adjust SQL, range limits, parameters, and types to align to actual data.

Sub SQLServerAppend()
    ' ADD REFERENCE FOR Microsoft ActiveX Data Objects #.# Library
    Dim con As ADODB.Connection, cmd As ADODB.Command
    Dim cell As Range
    Dim strSQL As String

    Set con = New ADODB.Connection
    con.Open "DSN=myDatabase"

    ' PREPARED STATEMENT WITH QMARK PLACEHOLDERS
    strSQL = "INSERT INTO myTable (Col1, Col2, Col3, ...) " _
           & " VALUES (?, ?, ?, ...)"

    sht = ActiveCell.Worksheet.Name
    With Worksheets(sht)
        For Each cell In .Range("A1", .Range("A1").End(xlDown))
            Set cmd = New ADODB.Command
            With cmd
                .ActiveConnection = con
                .CommandText = strSQL
                .CommandType = adCmdText

                ' BIND PARAMETERS WITH ? IN SQL (ALIGN VALUES TO ADO TYPES)
                ' FIRST COLUMN OF ROW RANGE
                .Parameters.Append .CreateParameter("col1param", adVarChar, adParamInput, , cell.Offset(0, 0).Value)
                ' SECOND COLUMN OF ROW RANGE
                .Parameters.Append .CreateParameter("col2param", adDate, adParamInput, , cell.Offset(0, 1).Value)
                ' THIRD COLUMN OF ROW RANGE
                .Parameters.Append .CreateParameter("col3param", adDecimal, adParamInput, , cell.Offset(0, 2).Value)
                '... ADD OTHER COLUMNS

                ' RUN APPEND ACTION
                .Execute
            End With
        Next cell

    cmd.close: con.Close
    Set cmd = Nothing: Set con = Nothing
End Sub