Private Enum ADOConstants
    adUseServer = 2
    adCmdText = 1
    adModeWrite = 2
    adPromptAlways = 1
End Enum

Private Function ExportToSQLServer()
Dim mpConnection As Object
Dim mpCommand As Object
Dim mpSQL As String
Dim i As Long
    
    Const mpConn As String = "Provider=SQLOLEDB;" & _
                             "Data Source=MyServer;" & _
                             "Initial Catalog=MyDatabase;" & _
                             "User Id=MyUser;" & _
                             "Password=MyPassword"
        
    mpSQL = "SELECT * FROM 'My Table'"
        
    Set mpConnection = CreateObject("ADODB.Connection")
    With mpConnection
    
        .ConnectionString = mpConn
        .CursorLocation = adUseServer
        .Mode = adModeWrite
    End With
    
    Set mpCommand = CreateObject("ADODB.Command")
    With mpCommand
    
        mpConnection.Open
        .ActiveConnection = mpConnection
        .CommandType = adCmdTextommandText = mpSQL
        .Execute
        
        'do stuff
        
        .ActiveConnection.Close
    End With
    
    Set mpCommand = Nothing
    Set mpConnection = Nothing
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Function