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