Write to Microsoft SQL Server Native Client 11.0 Database Table

Good Day, I am trying to write new entries into a SQL Server Database Table from Microsoft Excel using VBA. I have successfully connected and executed a SQL select query using VBA, correctly extracting to an Excel worksheet. However, I now need to write to a table within the Database as a backup (backup_test). Below are the current connection parameters used to select the data from the database and paste into excel (I have removed sensitive login info):

cn.ConnectionString = _
    "Driver={SQL Server Native Client 11.0};" & _
    "Server=tcp:name.database.windows.net;" & _
    "Database=name;" & _
    "User ID=user@id;" & _
    "Password=password123;"

I can't be sure if I am on the right track to select the table (backup_test) within the database, to then export the relevant data from Excel into it. So far, I have tried below VBA Code but I am not winning. I might be doing something incorrect, in which case it would be greatly appreciated if you could so kindly point that out for me:

Sub WriteDataToSQLServer()

Dim cn                    As ADODB.Connection
Dim rs                    As ADODB.Recordset
Dim n                     As Long
Dim strQuery              As String

Set cn = New ADODB.Connection

cn.ConnectionString = _
    "Driver={SQL Server Native Client 11.0};" & _
    "Server=tcp:name.database.windows.net;" & _
    "Database=name;" & _
    "User ID=user@id;" & _
    "Password=password123;"
    
    cn.Open

strQuery = "[name].[dbo].[backup_test]"

Set rs = New ADODB.Recordset

With rs
    .Open strQuery, cn, 1, 3
    For n = 1 To 10
        .AddNew
        
        .Fields("ID").Value = Cells(n, "A").Value
        .Fields("Firstname").Value = Cells(n, "B").Value

        .Update
    Next n
    .Close
End With

cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

Thank you for your assistance in this regard, I would appreciate any feedback on above if possible.

0 answers