Solved

ADODB SQL Recordset Error

Posted on 2014-07-30
17
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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 46

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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