Passing Excel Values back to a SQL Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to send rows of Excel data back to an existing table. What kind
of query do I need to write - could you direct me to an example.

Thanks

normad
 
Steve: I am stuck on an error message at the part where we are building the
array. When I look at vaData1 it has the correct information, vaDate2 also
has the correct information to be passed back to the DB. It does add a new
row to my table with with null values in both fields. The Error message I
received is "Identity cannot be determined for newly inserted rows". I am
attaching the code up to the point where it says successful - If you have any
suggestions I will appreciate it.

Thanks

normad

Sub Export_Data_Access()

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:M21")

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

stDB = ThisWorkbook.Path & "\" & "First Try"

'Create the connectionstring - The database is 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:k21")
'vaData = Application.Transpose(rnData.Value)

'Create the connection.

cnt.Open stConn
'Open the recordset.

rst.Open "Employees", cnt, adOpenKeyset, adLockOptimistic, adCmdTableDirect

THIS IS MY PROBLEM !!
'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
 
Back
Top