Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Errors 3705 and 3709...
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 OLEDB:Database 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 -- God said, "div D = rho, div B = 0, curl E = - @B/@t, curl H = J + @D/@t," and there was light. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value errors | New Users to Excel | |||
sum everything but errors | Excel Worksheet Functions | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Look for Errors | Excel Programming | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |