Errors 3705 and 3709...

[Steve Thompson]

Hello again all,

Using Excel 2003,

In the following code (watch out for line wrap)...

Sub ImportData()
Dim conConnection As ADODB.Connection
Dim lngColIndex As Long
Dim rstRecordset As ADODB.Recordset
Dim strFullPath As String
Dim strTableName As String
Dim rngTarget As Range

'Set rngTarget = rngTarget.Cells(1, 1)
strFullPath = "C:\Source\VB6\DMR\Valero.mdb"
strTableName = "MonthlyDataForExcel"


Set conConnection = New ADODB.Connection
conConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDBatabase
Password=mypasswordgoeshere; Data Source=" & strFullPath & ";Persist
Security Info=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Engine
Type=5"

Set rstRecordset = New ADODB.Recordset

With rstRecordset
.Open strTableName, conConnection, adOpenStatic, , adCmdTable

.Open "SELECT * FROM " & strTableName & ";" & conConnection & " , ,
adCmdText" ' < -- Get error(s) here

rngTarget = "A1"

For lngColIndex = 0 To rstRecordset.Fields.Count - 1
rngTarget.Offset(0, lngColIndex).Value =
rstRecordset.Fields(lngColIndex).Name
Next

rngTarget.Offset(1, 0).CopyFromRecordset rstRecordset
End With

rstRecordset.Close
Set rstRecordset = Nothing

conConnection.Close
Set conConnection = Nothing
End Sub



Where I have the comment, "< -- Get error(s) here" I get a runtime error(s)

Run-time error '3705':
Operation is not allowed when the object is open.


Now, I'm assuming that VBA is complaning about the first .Open
statement, ".Open strTableName, conConnection, adOpenStatic, , adCmdTable"

I comment it out. But when I do I get the following error:

Run-time error '3709'

The connection cannot be used to perform this operation. It is either closed
of invalid in this context.

Ron de Bruin was kind enough to point me to Ole P. Erlandsen's site where
the above snippet was found. I added the references...

Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library

in Excel. But I'm still stumped here. Icouldreally used some help here.

Thanks,

Steve