I have the following code, but am struggling with the syntax for the final part which needs to export the entire "tblTempFD" table data to the SQL Server "ES_RTR_Staging" Table.
The connection string is correct and the connection opens, other code to set up the table prior to importing the Excel table all works, just the final part is an issue.
Code
Sub ExportToSQLServer()
Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
Dim sConn As String, sSQL As String
sConn = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=ES_RTR_Staging;" & _
"Data Source=sqlnode23;" & _
"Use Procedure for Prepare=1;" & _
"Auto Translate=True;" & _
"Packet Size=4096;" & _
"Use Encryption for Data=False;" & _
"Tag with column collation when possible=False"
cnn.Open sConn
'Code here to set up ES_RTR_Staging table prior to import of new data (all works fine)
'// this is where I am having a problem with correct syntax to reference a table named "tblTempFD" on
'a sheet named "TempFcast" (both tab name and code name the same for that sheet)
sSQL = "INSERT INTO " & sTblID & _
" SELECT * FROM tblTempFD"
cnn.Execute sSQL
'// also tried
sSQL = "INSERT INTO " & sTblID & _
" SELECT * FROM [TempFcast$] tblTempFD"
cnn.Execute sSQL
'// Error is "Invalid Object Name"
End Sub
Display More
Any help or pointers greatly appreciated.
Also asked here: http://stackoverflow.com/questions/4201…n-to-sql-server