Updating an MS-SQL 2K table directly from Excel 2K

  • Thread starter Thread starter AlanN
  • Start date Start date
A

AlanN

Can any one suggest how I can populate a 2 column table in range a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
 
Here's one way of doing it. No error checking included. Reads data from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in database Test on a local server having first emptied the table. Assumes that the data is compatible with F1 and F2 datatypes. Requires a reference to Microsoft ActiveX Data Objects 2.x Library.

Dim cnn As ADODB.Connection
Dim r As Long
Dim rst As ADODB.Recordset
Dim str As String
str = "Provider=SQLOLEDB;Server=(local);"
str = str & "Database=Test;"
str = str & "Trusted_Connection=yes;"
Set cnn = New ADODB.Connection
cnn.Open str
cnn.Execute "delete from Table1"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With Worksheets("Sheet1")
For r = 1 To 10
rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
rst.Update
Next r
End With
rst.Close
Set rst = Nothing

Can any one suggest how I can populate a 2 column table in range a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
 
Many thanks for taking the time...

I get an error from the first line- "Compile error: User defined type not defined."
I assume it relates to a reference to Microsoft ActiveX Data Objects 2.x Library not being available.
How does one go about installing this?

TIA, AlanN
Here's one way of doing it. No error checking included. Reads data from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in database Test on a local server having first emptied the table. Assumes that the data is compatible with F1 and F2 datatypes. Requires a reference to Microsoft ActiveX Data Objects 2.x Library.

Dim cnn As ADODB.Connection
Dim r As Long
Dim rst As ADODB.Recordset
Dim str As String
str = "Provider=SQLOLEDB;Server=(local);"
str = str & "Database=Test;"
str = str & "Trusted_Connection=yes;"
Set cnn = New ADODB.Connection
cnn.Open str
cnn.Execute "delete from Table1"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With Worksheets("Sheet1")
For r = 1 To 10
rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
rst.Update
Next r
End With
rst.Close
Set rst = Nothing

Can any one suggest how I can populate a 2 column table in range a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
 
Nevermind, figured it out.
Thanks AlanN
Many thanks for taking the time...

I get an error from the first line- "Compile error: User defined type not defined."
I assume it relates to a reference to Microsoft ActiveX Data Objects 2.x Library not being available.
How does one go about installing this?

TIA, AlanN
Here's one way of doing it. No error checking included. Reads data from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in database Test on a local server having first emptied the table. Assumes that the data is compatible with F1 and F2 datatypes. Requires a reference to Microsoft ActiveX Data Objects 2.x Library.

Dim cnn As ADODB.Connection
Dim r As Long
Dim rst As ADODB.Recordset
Dim str As String
str = "Provider=SQLOLEDB;Server=(local);"
str = str & "Database=Test;"
str = str & "Trusted_Connection=yes;"
Set cnn = New ADODB.Connection
cnn.Open str
cnn.Execute "delete from Table1"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With Worksheets("Sheet1")
For r = 1 To 10
rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
rst.Update
Next r
End With
rst.Close
Set rst = Nothing

Can any one suggest how I can populate a 2 column table in range a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
 
In the VB editor select Tools > References. If you have ADO installed you will see one or more Microsoft ActiveX Data Objects Libraries listed in the Available References box. If so, tick the latest version. If not, you can get ADO by downloading the Microsoft Data Access Components (MDAC) from their site (www.microsoft.com) or, if you don't want to do this, you could try another approach. For example, use DAO instead of ADO (I've not done this myself), or, if you have Access you could link your Excel table and your SQL Server table to an Access database and copy the data with an Access query.
Many thanks for taking the time...

I get an error from the first line- "Compile error: User defined type not defined."
I assume it relates to a reference to Microsoft ActiveX Data Objects 2.x Library not being available.
How does one go about installing this?

TIA, AlanN
Here's one way of doing it. No error checking included. Reads data from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in database Test on a local server having first emptied the table. Assumes that the data is compatible with F1 and F2 datatypes. Requires a reference to Microsoft ActiveX Data Objects 2.x Library.

Dim cnn As ADODB.Connection
Dim r As Long
Dim rst As ADODB.Recordset
Dim str As String
str = "Provider=SQLOLEDB;Server=(local);"
str = str & "Database=Test;"
str = str & "Trusted_Connection=yes;"
Set cnn = New ADODB.Connection
cnn.Open str
cnn.Execute "delete from Table1"
Set rst = New ADODB.Recordset
rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With Worksheets("Sheet1")
For r = 1 To 10
rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
rst.Update
Next r
End With
rst.Close
Set rst = Nothing

Can any one suggest how I can populate a 2 column table in range a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
 
Back
Top