Help - Update a SQL table from a specific range in Excel
 

Help - I am trying to update a SQL table from a specific range: Here is the
code I am using. The array is returning null values. I will appreciate any
help I can get.

Sub OneMoreTry()
Dim cnt As ADODB.connection
Dim rst As ADODB.recordset
Dim xlCalc As XlCalculation
Dim rnData As Range, rnCell As Range
Dim stDB As String, stConn As String
Dim vaData() As Variant
Dim i As Long

'Change settings in order to increase the performance.

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Set rnData = ActiveSheet.Range("J21:L23")


'Instantiate the ADO COM's objects.
Set cnt = New ADODB.connection
Set rst = New ADODB.recordset

'Create the connectionstring - The database is not protected with a
password.
stConn =
"Provider=SQLOLEDB;Server=CorpDyndb;Trusted_Connec tion=Yes;Initial
Catalog=GPReports;UID=;"

'Populate the array with data from the range.
vaData = rnData.Value

'If the data is stored in rows instead of columns then the
'solution would be the following:
Set rnData = ActiveSheet.Range("J21:L23")
'vaData = Application.Transpose(rnData.Value)


'Create the connection.
cnt.Open stConn

'Open the recordset.
rst.Open "My_Employees", cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Read data, add new data and update the recordset.

For i = 1 To UBound(vaData)
With rst
.AddNew
.Update VBA.Array("ID", "FName"), VBA.Array(vaData(i, 1), vaData(i, 2))
End With
Next i


MsgBox "Successfully updated the table!", vbInformation

'Close recordset and connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

'Clear inputrange.
rnData.ClearContents

'Restore the settings.

With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

Thank you

Norma

Help - Update a SQL table from a specific range in Excel
 

"NormaD" wrote:

Help - I am trying to update a SQL table from a specific range: Here is the
code I am using. The array is returning null values. I will appreciate any
help I can get.

Sub OneMoreTry()
Dim cnt As ADODB.connection
Dim rst As ADODB.recordset
Dim xlCalc As XlCalculation
Dim rnData As Range, rnCell As Range
Dim stDB As String, stConn As String
Dim vaData() As Variant
Dim i As Long

'Change settings in order to increase the performance.

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Set rnData = ActiveSheet.Range("J21:L23")


'Instantiate the ADO COM's objects.
Set cnt = New ADODB.connection
Set rst = New ADODB.recordset

'Create the connectionstring - The database is not protected with a
password.
stConn =
"Provider=SQLOLEDB;Server=CorpDyndb;Trusted_Connec tion=Yes;Initial
Catalog=GPReports;UID=;"

'Populate the array with data from the range.
vaData = rnData.Value

'If the data is stored in rows instead of columns then the
'solution would be the following:
Set rnData = ActiveSheet.Range("J21:L23")
'vaData = Application.Transpose(rnData.Value)


'Create the connection.
cnt.Open stConn

'Open the recordset.
rst.Open "My_Employees", cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Read data, add new data and update the recordset.

For i = 1 To UBound(vaData)
With rst
.AddNew
.Update VBA.Array("ID", "FName"), VBA.Array(vaData(i, 1), vaData(i, 2))
End With
Next i


MsgBox "Successfully updated the table!", vbInformation

'Close recordset and connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

'Clear inputrange.
rnData.ClearContents

'Restore the settings.

With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

Thank you

Norma



Does this work?


For i = 1 To UBound(vaData)
With rst
..AddNew
..Fields("ID") = vaData(i, 1)
..Fields("FName") = vaData(i, 2)
..Update
End With
Next i


--
urkec

Help - Update a SQL table from a specific range in Excel
 

Thank you for your help. I works beautifully. Sometimes you can't see the
obvious. Again, thank you I really appreciated the help.

Normad

"urkec" wrote:

"NormaD" wrote:

Help - I am trying to update a SQL table from a specific range: Here is the
code I am using. The array is returning null values. I will appreciate any
help I can get.

Sub OneMoreTry()
Dim cnt As ADODB.connection
Dim rst As ADODB.recordset
Dim xlCalc As XlCalculation
Dim rnData As Range, rnCell As Range
Dim stDB As String, stConn As String
Dim vaData() As Variant
Dim i As Long

'Change settings in order to increase the performance.

With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

Set rnData = ActiveSheet.Range("J21:L23")


'Instantiate the ADO COM's objects.
Set cnt = New ADODB.connection
Set rst = New ADODB.recordset

'Create the connectionstring - The database is not protected with a
password.
stConn =
"Provider=SQLOLEDB;Server=CorpDyndb;Trusted_Connec tion=Yes;Initial
Catalog=GPReports;UID=;"

'Populate the array with data from the range.
vaData = rnData.Value

'If the data is stored in rows instead of columns then the
'solution would be the following:
Set rnData = ActiveSheet.Range("J21:L23")
'vaData = Application.Transpose(rnData.Value)


'Create the connection.
cnt.Open stConn

'Open the recordset.
rst.Open "My_Employees", cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect

'Read data, add new data and update the recordset.

For i = 1 To UBound(vaData)
With rst
.AddNew
.Update VBA.Array("ID", "FName"), VBA.Array(vaData(i, 1), vaData(i, 2))
End With
Next i


MsgBox "Successfully updated the table!", vbInformation

'Close recordset and connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

'Clear inputrange.
rnData.ClearContents

'Restore the settings.

With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

Thank you

Norma



Does this work?


For i = 1 To UBound(vaData)
With rst
.AddNew
.Fields("ID") = vaData(i, 1)
.Fields("FName") = vaData(i, 2)
.Update
End With
Next i


--
urkec