Solved

ADODB SQL Recordset Error

Posted on 2014-07-30
17
282 Views
Last Modified: 2015-02-23
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
0
Comment
Question by:Laila Jackson
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility
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.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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

0
 

Author Comment

by:Laila Jackson
Comment Utility
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
0
 

Author Comment

by:Laila Jackson
Comment Utility
This is absolute insane. Worked with 2003, 2007 and 2010 no problems using ADO. Now this! What have they done in California?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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

0
 

Author Comment

by:Laila Jackson
Comment Utility
Hi,

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

Author Comment

by:Laila Jackson
Comment Utility
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

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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
0
 

Author Comment

by:Laila Jackson
Comment Utility
@ 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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
Do you have 2.1 as an option. Was the version last time I did this?
0
 

Author Comment

by:Laila Jackson
Comment Utility
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?
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
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 (http://www.experts-exchange.com/Database/MS_Access/Q_28485126.html#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
0
 

Author Comment

by:Laila Jackson
Comment Utility
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
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
FYI, I have managed to solve my problem. Sheer luck, and running out of other ideas.

Kelvin
0
 

Author Comment

by:Laila Jackson
Comment Utility
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.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this revision

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

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

set conn=currentproject.Accessconnection

'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) = clng(rs.Fields("CommenceDate"))
    End If
Else
    vAccountArray(1) = 0
    vAccountArray(2) = 0
End If

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

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

End Function
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now