Problems with ADO from Excel

[Guest]

I am trying to run the following code and I get an error with the following
details:

Runtime Error -2147467259
Automation Error
Unspecified Error

The code looks like the following

Dim wrkRange As Range
Dim last As Integer
Dim i As Integer
Dim MyConn As ADODB.Connection
Dim strConn As String
Dim rstDocs As ADODB.Recordset

Sub ParseContent()
'Get the working Range
Set wrkRange = Application.InputBox("Enter the active range", "Range
Selector", , , , , , 8)
'Find the last cell number
last = wrkRange.Rows.Count
'Opening Database File
'Dim MyConn As New ADODB.Connection
'Dim strConn As String

Set MyConn = New ADODB.Connection
strConn = "Provider=SQLOLEDB;Server=Cronos;" & _
"Database=Papyrus;Integrated Security='SSPI';"

MyConn.Open strConn

'Dim rstDocs As ADODB.Recordset
Set rstDocs = New ADODB.Recordset

rstDocs.Open "Docs", MyConn, adOpenKeyset, adLockOptimistic, adCmdTable


'Start Filling the access database
'Columns A+B to Docs Table
For i = 1 To last
Dim strfield As String

strfield = wrkRange.Cells(i, "A")
rstDocs.MoveLast
rstDocs.AddNew Key_Id, strfield
rstDocs.Update



Next i

End Sub

Any ideas?

 

[onedaywhen]

Note that using the Jet OLE DB provider or the MS Access/Excel ODBC
driver you could do this in one hit and without a recordset e.g.

INSERT INTO
[ODBC;Driver={SQL Server};SERVER=Cronos;DATABASE=Papyrus;Integrated
Security=SSPI;]. Docs
(KeyID)
SELECT F1 AS KeyID
FROM
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$A:A]
;

The source workbook would have to be closed to avoid the dreaded memory
leak bug.

Jamie.

--

 

[krbcnufb]

http://www.ardice.com/Computers/Programming/Databases/ADO/