Solved

ADODB SQL Recordset Error

Posted on 2014-07-30
17
289 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
ID: 40230933
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40230935
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40230939
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Laila Jackson
ID: 40230958
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
ID: 40230962
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
ID: 40230973
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
ID: 40230979
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
ID: 40230981
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40230993
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
ID: 40231005
@ 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
ID: 40231012
Do you have 2.1 as an option. Was the version last time I did this?
0
 

Author Comment

by:Laila Jackson
ID: 40231028
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
ID: 40231116
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
ID: 40231133
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
ID: 40231276
FYI, I have managed to solve my problem. Sheer luck, and running out of other ideas.

Kelvin
0
 

Author Comment

by:Laila Jackson
ID: 40231448
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40231532
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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