asked on

ADODB SQL Recordset Error

Hello
Working with Access 2013 32-bit.
Current reference libraries:
- Visual Basic for Applications
- Microsoft Access 15.0 Object Library
- Microsoft ActiveX Data Objects 6.1 Library

I am having an issue trying to run a simple routine procedure using ADO. I keep getting errors and while I have searched high and low online for a workable solution, I cannot find anything that is credible enough to fix the problem.

I have tried the normal changing declarations "As New etc" , resetting the references, restarting PC blah, blah but still no joy. All my services and support packs are up to date from Microsoft.

Errors are typically along the lines of:
"Object variable not set"
"Runtime error 3709....it is either closed or invalid in this context"
"Library does not support...."

Here is my code:
(Note I am not using SQLServer)

Function AccountTransactionDateArray(uAccount As String) As Variant
   
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim strSQL As String
Dim dbFailOnError, dbSeeChanges

'declaring array ->>
Dim vAccountArray As Variant
ReDim vAccountArray(1 To 2)

'loading the selected sql into a recordset ->
strSQL = "SELECT Min(tbl_cashbook.tLongdate) AS CommenceDate From tbl_cashbook WHERE (((tbl_cashbook.tAccount)='" & uAccount & "'))"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
    rs.MoveFirst
    If IsNull(rs.Fields("CommenceDate")) = True Then
        vAccountArray(1) = 0
        vAccountArray(2) = 0
    Else
        vAccountArray(1) = rs.Fields("CommenceDate")
        vAccountArray(2) = dateToLong(rs.Fields("CommenceDate"))
    End If
Else
    vAccountArray(1) = 0
    vAccountArray(2) = 0
End If

'assign the rs values to the array ->
FirstAccountTransactionDate = vAccountArray

'peforming cleanup ->
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Function

Open in new window

I am wanting to have this sorted as I cannot continue with my work until it is!

Thanks

Paul Jackson

You don't seem to be defining your connection object conn, and you don't open it before using it when you open your recordset object.

Rey Obrero (Capricorn1)

you don't need to declare this

Dim dbFailOnError, dbSeeChanges

change this line

vAccountArray(2) = dateToLong(rs.Fields("CommenceDate"))

to

vAccountArray(2) = clng(rs.Fields("CommenceDate"))

which line is raising the error?

Rey Obrero (Capricorn1)

also set the connection

set conn=currentproject.connection

Function AccountTransactionDateArray(uAccount As String) As Variant
   
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim strSQL As String
'Dim dbFailOnError, dbSeeChanges

'declaring array ->>
Dim vAccountArray As Variant
ReDim vAccountArray(1 To 2)

set conn=currentproject.connection

'loading the selected sql into a recordset ->
strSQL = "SELECT Min(tbl_cashbook.tLongdate) AS CommenceDate From tbl_cashbook WHERE (((tbl_cashbook.tAccount)='" & uAccount & "'))"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
    rs.MoveFirst
    If IsNull(rs.Fields("CommenceDate")) = True Then
        vAccountArray(1) = 0
        vAccountArray(2) = 0
    Else
        vAccountArray(1) = rs.Fields("CommenceDate")
      '  vAccountArray(2) = dateToLong(rs.Fields("CommenceDate"))
		vAccountArray(2) = clng(rs.Fields("CommenceDate"))
    End If
Else
    vAccountArray(1) = 0
    vAccountArray(2) = 0
End If

'assign the rs values to the array ->
FirstAccountTransactionDate = vAccountArray

'peforming cleanup ->
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Function

Open in new window

Laila Jackson

ASKER

Hi thanks for the tips.

I did the suggestions and errors at Line 5 ("Class not registered") even after setting the connection using:

Set conn = CurrentProject.Connection
TA

Laila Jackson

ASKER

This is absolute insane. Worked with 2003, 2007 and 2010 no problems using ADO. Now this! What have they done in California?

aikimark

I changed your function name (FirstAccountTransactionDate) to match the returning value statement. I'm also a proponent of explicit property use instead of implicit property use, so I added .Value to the two rs.Fields referencing statements in an update of Roy's code.

Function FirstAccountTransactionDate(uAccount As String) As Variant
   
Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim strSQL As String
'Dim dbFailOnError, dbSeeChanges

'declaring array ->>
Dim vAccountArray As Variant
ReDim vAccountArray(1 To 2)

set conn=currentproject.connection

'loading the selected sql into a recordset ->
strSQL = "SELECT Min(tbl_cashbook.tLongdate) AS CommenceDate From tbl_cashbook WHERE (((tbl_cashbook.tAccount)='" & uAccount & "'))"
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
    rs.MoveFirst
    If IsNull(rs.Fields("CommenceDate")) = True Then
        vAccountArray(1) = 0
        vAccountArray(2) = 0
    Else
        vAccountArray(1) = rs.Fields("CommenceDate").Value
      '  vAccountArray(2) = dateToLong(rs.Fields("CommenceDate"))
		vAccountArray(2) = clng(rs.Fields("CommenceDate").Value)
    End If
Else
    vAccountArray(1) = 0
    vAccountArray(2) = 0
End If

'assign the rs values to the array ->
FirstAccountTransactionDate = vAccountArray

'peforming cleanup ->
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

End Function

Open in new window

Laila Jackson

ASKER

Hi,

I copied your code into my IDE and tested. An displays error stating "class not registered" on line 13.
TA

Laila Jackson

ASKER

I thought there might be an error with the library but there is nothing. Ran this code and all okay.

Sub testADO()
    'This sub-procedure loops through all current
    'references and looks for a ADO reference
    Dim A As Variant
    For Each A In Application.References
        If A.Name = "ADODB" Then
            MsgBox "ADO Library loaded!"
            Exit Sub
        End If
    Next
    MsgBox "ADO Library NOT loaded"
End Sub

Open in new window

Kelvin Sparks

Try using one of the Microsoft ActiveX Data Objects 2.x libraries. I note that my copy has 2.1 to 2.8 and 6.1. My code from an earlier database uses 2.1.

Has 6.1 some weird other use? Strange to jump from 2.8 to 6.1, but who knows what's going on at MS.

Kelvin

Laila Jackson

ASKER

@ Kelvin
I tried this just now and same error on line 13.

Run-time error "Class not registered"

What a mess. I agree, seems strange going from 2.8 to 6.1. The bad thing is, for the work I need this for, I cannot use DAO, otherwise I would have.

Thanks anyway

Kelvin Sparks

Do you have 2.1 as an option. Was the version last time I did this?

Laila Jackson

ASKER

Yeah. I have 2.0 through 2.8 then onto 6.1

I tried 2.1 just now and no joy either. This is VERY frustrating Microsoft. How can you get it wrong?

Kelvin Sparks

You have me scratching my head. Have only had Office 2013 for a couple of weeks. I've also had an issue that has blown up with me from older code that was pretty much stock standard and cannot find a solution for (https://www.experts-exchange.com/questions/28485126/Object-based-code-returning-Error-429-Active-X-component-cannot-create-object.html?anchorAnswerId=40223887#a40223887).

Is starting to feel like 2013 is flakey where you start trying Automation or non DAO processes.

I'll keep thinking on this. None of my ADODB code is currently setup to go at the moment - might be worth a try.

Kelvin

Laila Jackson

ASKER

Kelvin. Thanks for your comments.

Indeed, it looks like we are both in the same boat.

I upgraded to Office 2013 a few months ago and have completed two projects in Excel without any issues. Funnily enough, I have been using ADO procedures when developing in Excel with no issues at all, would you believe it?

The following code in Excel 2013 works fine using ADO!

Sub transfer_icbr_transaction_detail()
On Error GoTo Err_Handler
'Author: Shaun
'Last updated: 11/06/2014

'turn off updates for code execution ->
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'[1] Detailed transactions
'icbr_trans_import_start_row

'specifying db connection and declaring variables ->
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset

Dim rsCount As Integer, wkb As String, i As Integer, xChecking As Integer, xInsert As Integer, _
    vAccount As String, shtTo As Worksheet, rngTo As Range, rngFrom As Range, vEstateName As String, _
    vFromDateKey As Long, vToDateKey As Long, vEstateID As Integer, vDate As String, sRow As Integer
    
'setting target variables ->
wkb = Application.ThisWorkbook.FullName

Set shtTo = ThisWorkbook.Sheets("icbr")
vEstateID = shtTo.Range("icbr_estate_id")
vFromDateKey = dateToLong(shtTo.Range("icbr_from_date"))
vToDateKey = dateToLong(shtTo.Range("icbr_to_date"))
vAccount = Trim(shtTo.Range("icbr_account"))

'opening ADODB connection to the workbook ->
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & wkb & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes"";"

'loading the sql definition into a recordset ->
rs.Open "SELECT [Date], [Details], [Code], [Payments], [Receipts] FROM [tbl_cashbook] " & _
"WHERE ((([Cheque]) Is Null) AND (([DateKey])>=" & vFromDateKey & " And ([DateKey])<=" & vToDateKey & ") AND (([EstID])=" & vEstateID & ") AND (([Account])='" & vAccount & "'))", cnn, adOpenStatic
If rs.RecordCount > 0 Then
    rs.MoveFirst
        'collecting the record count and inserting report rows ->
        xChecking = getChqCount(vFromDateKey, vToDateKey, vEstateID, vAccount)
        xInsert = rs.RecordCount + xChecking
        Call delins(xInsert, "icbr_transaction_detail_start", 2, "icbr") 'code 2 is used for insert method
        
        i = 1
        
        'if record set EOF there are no records ->
        Do While Not rs.EOF
                     
            'writing the outputs of the sql directly to the report cells ->
            Range("icbr_trans_import_start_row").Offset(i, 0) = rs.Fields("Date").Value
            'Range("icbr_trans_import_start_row").Offset(i, 1) = rs.Fields("Cheque").value
            Range("icbr_trans_import_start_row").Offset(i, 2) = rs.Fields("Details").Value
            Range("icbr_trans_import_start_row").Offset(i, 3) = rs.Fields("Code").Value
            Range("icbr_trans_import_start_row").Offset(i, 4) = CDbl(rs.Fields("Payments").Value)
            Range("icbr_trans_import_start_row").Offset(i, 5) = CDbl(rs.Fields("Receipts").Value)
            Range("icbr_trans_import_start_row").Offset(i, 6).FormulaR1C1 = "=IF(ISNUMBER(R[-1]C)=TRUE,R[-1]C+RC[-1]-RC[-2],RC[-1])"
             
            'counter increment ->
            i = i + 1
            rs.MoveNext
        Loop
End If
        
'opening ADODB connection to the workbook ->
cnn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & wkb & ";" & _
                  "Extended Properties=""Excel 12.0;HDR=Yes"";"
                  

'loading the sql definition into a recordset ->
rs2.Open "SELECT [DrawnDate], [Cheque], [Details], [Code], [Amount] FROM [tbl_cheque] " & _
"WHERE ((([DateKey])>=" & vFromDateKey & " And ([DateKey])<=" & vToDateKey & ") AND (([EstID])=" & vEstateID & ") AND (([Account])='" & vAccount & "'))", cnn2, adOpenStatic
If rs2.RecordCount > 0 Then
    rs2.MoveFirst
        
        i = i
        
        'if record set EOF there are no records ->
        Do While Not rs2.EOF
                     
            'writing the outputs of the sql directly to the report cells ->
            Range("icbr_trans_import_start_row").Offset(i, 0) = CDate(rs2.Fields("DrawnDate").Value)
            Range("icbr_trans_import_start_row").Offset(i, 1) = rs2.Fields("Cheque").Value
            Range("icbr_trans_import_start_row").Offset(i, 2) = rs2.Fields("Details").Value
            Range("icbr_trans_import_start_row").Offset(i, 3) = rs2.Fields("Code").Value
            Range("icbr_trans_import_start_row").Offset(i, 4) = CDbl(rs2.Fields("Amount").Value)
            Range("icbr_trans_import_start_row").Offset(i, 5) = 0
            Range("icbr_trans_import_start_row").Offset(i, 6).FormulaR1C1 = "=IF(ISNUMBER(R[-1]C)=TRUE,R[-1]C+RC[-1]-RC[-2],RC[-1])"
             
            'counter increment ->
            i = i + 1
            rs2.MoveNext
        Loop
           
        'collecting range information and applying advanced data sortation on dates and cheques ->
        sRow = ReturnRangeRowCount("icbr_trans_import_start_row")
        sRow = 12 + sRow '
        shtTo.Sort.SortFields.clear
        shtTo.Sort.SortFields.Add Key:=Range("C14:C" & sRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        shtTo.Sort.SortFields.Add Key:=Range("D14:D" & sRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
        'apply sort
        With shtTo.Sort
            .SetRange Range("C13:I" & sRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        'add name to dataset for ado summary
        ActiveWorkbook.Names("ado_icbr").Delete
        ActiveWorkbook.Names.Add Name:="ado_icbr", RefersTo:=Range("C13:I" & sRow)

End If

'releasing system memory ->
Set rs = Nothing
cnn.Close
Set cnn = Nothing

'releasing system memory ->
Set rs2 = Nothing
cnn2.Close
Set cnn2 = Nothing

'turning on alerts and screen updates ->>
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With
End Sub

Open in new window

Just started on this new project and client has office 2013 installed. I thought, sure no problems. Little did I know!!

I think the biggest issue is that there is very little information about this problem in the public domain.
Maybe we can change that!

Shaun

Kelvin Sparks

FYI, I have managed to solve my problem. Sheer luck, and running out of other ideas.

Kelvin

Laila Jackson

ASKER

Great work! At least you got somewhere!
Adding time delay is not something common that I have seen in mainstream development (unless working with PDF print and command line objects) and most seasoned developers would not even suggest such a thing. Take my hat off to you for picking this out.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial