Pull and push data from and into sql databases using Excel VBA without pasting the data in Excel sheets

556 views Asked by At

I want to use Excel vba to pull data from one database (server) and then push the same to another (local). However, I don't want to paste the data in any excel sheets during the process as this slows down the Excel a lot. How can I do this? Below is my work so far. I have highlighted where my code stops with error - Wrong number of arguments or invalid property assignment

Sub get_data_temp()
    Dim cn As Object
    Dim rs As Object
    Dim strConnection As String
    Dim server, port, id, pass As String
    Dim strSQL As String

    'get data from server (MS SQL based)
    strSQL = Range("query_1").Value
    server = Range("db_server").Value
    port = Range("db_port").Value
    id = Range("db_id").Value
    pass = Range("db_pass").Value

    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=SQLOLEDB;Data Source=" & server & "," & port & ";User ID=" & id & ";Password=" & pass & ";"
    cn.Open strConnection
    Set rs = cn.Execute("SET NOCOUNT ON;" & strSQL)

    'connect to ms access to import data
    Dim xcn As Object
    Dim xrs As Object
    Dim xdbpath As String
    Dim xstrConnection As String
    Dim xdb_name As String
    Dim xstrSQL As String

    ''''''''the code stops at the line below'''''''''''''
    xstrSQL = "insert into crm_main select * from " & rs
    ''''''''the code stops at the line above'''''''''''''

    xdb_name = "temp.accdb"
    xdbpath = ThisWorkbook.Path & Application.PathSeparator & xdb_name
    Set xcn = CreateObject("ADODB.Connection")
    xstrConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbpath
    xcn.Open xstrConnection
    xcn.Execute(xstrSQL)
    cn.Close
    Set cn = Nothing
    xcn.Close
    Set xcn = Nothing

End Sub
0

There are 0 answers