VBA: transferring data from Excel to SQL Server

[P_Schmitz]

Hi,

I need to process large numbers of data and am trying to link Excel (2010) to SQL server (I am using the Denali Express Edition). My objective is perform all calculations in Excel and to store all data in a SQL server database. I want to use VBA to transfer data from SQL Server to Excel and the other way around.

So far I have been able to generate the queries for retrieving data from SQL server, however I am not able to write data back to SQL Server. The code below provides my latest version: the connection to the SQL Server works but the data selection in Excel fails. The selection below assumes a dataset in a tab called 'export'; since I need to process a large number of records I would ultimately like to replace this fixed tab with a dynamic array and transfer all the data from that array to SQL server in a single step.

Basically I have two questions:
1) can someone help me with the connection to SQL server (the last part from the sSql statement)
2) can someone help me with the syntax for using a dynamic array as opposed to a tab

Your help would be much appreciated!

Sub ADO_test3()


Dim Server As String
Dim DB As String
Dim Loan As Long
Dim sConn As String
Dim sSql As String
Dim tbl As String
Dim export As Variant
Dim lngRecsAff As Long
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

Server = Range("Server")
DB = Range("DB")
tbl = "Tst1"
tbl2 = "Tst2"
export = Range("export")

sConn = "PROVIDER=SQLOLEDB;"
sConn = sConn & "DATA SOURCE=" & Server & ";INITIAL CATALOG=" & DB & ";"
sConn = sConn & " INTEGRATED SECURITY=sspi;"
cnPubs.Open sConn

sSql = "INSERT INTO """ & DB & """.dbo." & tbl & ""
sSql = sSql & " SELECT * from "
sSql = sSql & "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Users\Paul Schmitz\Documents\My Dropbox\SQL Server\ODBC test - thuis.xlsm;Extended Properties=Excel 8.0')...[Export$]"

cnPubs.Execute sSql, lngRecsAff, adExecuteNoRecords

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub