G
Guest
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_Connection=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
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_Connection=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