CODEWITHSUNDEEP
sqlsql-serverexcelvbams-access
Ployer
Ployer

Reputation: 13

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

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!

Upvotes: 0

Views: 1230

Answers (2)

Parfait
Parfait

Reputation: 107652

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

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

Upvotes: 4

Ployer
Ployer

Reputation: 13

I've modified my code with the loop from @Parfait to make it work for me. As my DAO connection was working I decided to stick with it.

Sub ToDbase()
'Modified by ployer. This sends values from an exel spreadsheet to your sql database
'with code from Parfait https://stackoverflow.com/questions/71817166/how-do-you-insert-a-range-from-excel-to-an-sql-table

   Dim db As DAO.Database 'sql database
   Dim rs As DAO.Recordset

   Set db = OpenDatabase("myDB", dbDriverNoPrompt, False, "ODBC;DATABASE=myDB_Backup;DSN=myDB")

Dim cell As Range

Dim Value1 As String 'First value to import
Dim Value2 As String 'Second value to import. Add more as needed with the correct types

Dim i As Integer 'for testing in my exel sheet before trying in db
Dim n As Integer 'for testing in my exel sheet before trying in db

i = 1
n = 1

sht = ActiveCell.Worksheet.Name
    With Worksheets(sht)
        For Each cell In .Range("A1", .Range("A1").End(xlDown))
        
        Value1 = cell.Offset(0, 0).Value 'Assign to variable "Value1" the value stored in Cell at position 0,0 (First time through would be A1)
        Value2 = cell.Offset(0, 1).Value 'Assign to variable "Value2" the value stored in Cell at position 0,0 (First time through would be B1)
        
        'For testing if iteration works.
        'Cells(i, 5).Value = Value1
        'Cells(n, 6).Value = Value2
        'i = i + 1
        'n = n + 1


'each time we go through the loop the Value1 and Value2 get sent to Col1 and Col2 in myTable. You need to define the value of Col1, for instance, if in the db it is called Customer it needs to be written Customer here.
        db.Execute "INSERT INTO myTable (Col1, Col2) Values ('" & Value1 & "','" & Value2 & "') ", dbFailOnError
        
        Next cell
    
End With
End Sub

Upvotes: 1

Related Questions