Results 1 to 14 of 14

Thread: ADO problem

  1. #1
    Join Date
    Jun 2002
    Posts
    72

    Unanswered: ADO problem

    Set FINDSESSIONS = New ADODB.Recordset

  2. #2
    Join Date
    Jun 2002
    Posts
    72
    What I meant to say was.......... that I have the following code:

    Set FINDSESSIONS = New ADODB.Recordset

    that causes the following run time error:

    Automation error, the specific module could not be found.

    I've had a look in tools/references to see if I could add it but I do not know which one to add, or maybe there's some other reason for this error.

    Any ideas, thanks

  3. #3
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Try:
    Code:
    Dim FINDSESSIONS as ADODB.Recordset
    You'll need a reference to "Microsoft ActiveX Data Object Recordset 2.x Library"

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  4. #4
    Join Date
    Jun 2002
    Posts
    72
    Thanks for that, it kind of worked but now I'm getting a 'USER DEFINED TYPE NOT DEFINED' ERROR on the following line:

    Dim rst As ADODB.Recordset

    Any ideas how to get around this?

    Cheers

  5. #5
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Have you got a refernce to the library I listed in my previous post?
    (Menu: Tools -> References)

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  6. #6
    Join Date
    Jun 2002
    Posts
    72
    Yes, I have included 'Microsoft ActiveX Data Objects Recordset 2.5 Library'

  7. #7
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Could you post the code for that procedure?

    /Andrew
    There have been many posts made throughout the world.
    This was one of them.

  8. #8
    Join Date
    Jun 2002
    Posts
    72
    Dim rst As ADODB.Recordset
    Dim findsessions As Variant
    Dim EXTRACTER As Variant
    Dim INTCOUNT As Integer
    Dim RECORD_COUNT As Integer

    Set findsessions = New ADODB.Recordset
    Set findsessions.ActiveConnection = CurrentProject.Connection

    findsessions.CursorType = adOpenForwardOnly
    findsessions.LockType = adLockReadOnly
    findsessions.Open "SELECT QTY,FULLNAME,CATEGORY,COMMENTS,AMOUNT,FULLDATE,ORD ERNNO FROM [EXTRA_CREDIT_SINGLE]"

    If findsessions.EOF = True Then
    findsessions.Close
    Set findsessions = Nothing
    Else
    EXTRACTER = findsessions.GetRows(Fields:=(Array("QTY", "FULLNAME", "CATEGORY", "COMMENTS", "AMOUNT", "FULLDATE", "ORDERNO")))
    RECORD_COUNT = UBound(EXTRACTER, 2)

    For INTCOUNT = 0 To RECORD_COUNT

    If IsNull(EXTRACTER(0, INTCOUNT)) Then
    Forms![Parent]!QTY = 1
    Else
    Forms![Parent]!QTY = EXTRACTER(0, INTCOUNT)
    End If

    If IsNull(EXTRACTER(1, INTCOUNT)) Then
    Forms![Parent]!FULLNAME = 1
    Else
    Forms![Parent]!FULLNAME = EXTRACTER(1, INTCOUNT)
    End If

    If IsNull(EXTRACTER(2, INTCOUNT)) Then
    Forms![Parent]!Category = 1
    Else
    Forms![Parent]!Category = EXTRACTER(2, INTCOUNT)
    End If

    If IsNull(EXTRACTER(3, INTCOUNT)) Then
    Forms![Parent]!Comments = 1
    Else
    Forms![Parent]!Comments = EXTRACTER(3, INTCOUNT)
    End If

    If IsNull(EXTRACTER(4, INTCOUNT)) Then
    Forms![Parent]!Amount = 1
    Else
    Forms![Parent]!Amount = EXTRACTER(4, INTCOUNT)
    End If

    If IsNull(EXTRACTER(5, INTCOUNT)) Then
    Forms![Parent]!FULLDATE2 = 1
    Else
    Forms![Parent]!FULLDATE2 = EXTRACTER(5, INTCOUNT)
    End If

    If IsNull(EXTRACTER(6, INTCOUNT)) Then
    Forms![Parent]!ORDERNO = 1
    Else
    Forms![Parent]!ORDERNO = EXTRACTER(6, INTCOUNT)
    End If

    DoCmd.RunSQL "INSERT INTO [INVOICE_ITEMS] ([INVOICE_NO], [QUANTITY], [CHILD_FULLNAME], [CATEGORY], [COMMENTS], [AMOUNT], [IND_DATE], [ORDER_NO] ) VALUES ( Forms![Parent]!INV,Forms![Parent]!QTY , Forms![Parent]!FULLNAME, Forms![Parent]!CATEGORY, Forms![Parent]!COMMENTS, Forms![Parent]!AMOUNT, Forms![Parent]!FULLDATE2, Forms![Parent]!ORDERNO)"

    Next INTCOUNT

    End If

  9. #9
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Try this. Note I've added some code to close the recordset after converting to getrows.

    Code:
    Dim findsessions As ADODB.Recordset
    Dim strSQL As String
    
    Dim EXTRACTER As Variant
    Dim INTCOUNT As Integer
    Dim RECORD_COUNT As Integer
    
    strSQL = "SELECT QTY,FULLNAME,CATEGORY,COMMENTS,AMOUNT," FULLDATE, ORDERNNO FROM [EXTRA_CREDIT_SINGLE]"
    
    findsessions.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    
    If findsessions.EOF = True Then
        'close recordset
        findsessions.Close
        Set findsessions = Nothing
       
    Else
        EXTRACTER = findsessions.GetRows(Fields:=(Array("QTY", "FULLNAME", "CATEGORY", "COMMENTS", "AMOUNT", "FULLDATE", "ORDERNO")))
        'close recordset
        findsessions.Close
        Set findsession = Nothing
        
        RECORD_COUNT = UBound(EXTRACTER, 2)
    
        For INTCOUNT = 0 To RECORD_COUNT
    
            If IsNull(EXTRACTER(0, INTCOUNT)) Then
                Forms![Parent]!QTY = 1
            Else
                Forms![Parent]!QTY = EXTRACTER(0, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(1, INTCOUNT)) Then
                Forms![Parent]!FullName = 1
            Else
                Forms![Parent]!FullName = EXTRACTER(1, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(2, INTCOUNT)) Then
                Forms![Parent]!Category = 1
            Else
                Forms![Parent]!Category = EXTRACTER(2, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(3, INTCOUNT)) Then
                        Forms![Parent]!Comments = 1
            Else
                Forms![Parent]!Comments = EXTRACTER(3, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(4, INTCOUNT)) Then
                Forms![Parent]!Amount = 1
            Else
                Forms![Parent]!Amount = EXTRACTER(4, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(5, INTCOUNT)) Then
                Forms![Parent]!FULLDATE2 = 1
            Else
                Forms![Parent]!FULLDATE2 = EXTRACTER(5, INTCOUNT)
            End If
    
            If IsNull(EXTRACTER(6, INTCOUNT)) Then
                Forms![Parent]!ORDERNO = 1
            Else
                Forms![Parent]!ORDERNO = EXTRACTER(6, INTCOUNT)
            End If
    
            DoCmd.RunSQL "INSERT INTO [INVOICE_ITEMS] ([INVOICE_NO], [QUANTITY], [CHILD_FULLNAME], [CATEGORY], [COMMENTS], [AMOUNT], [IND_DATE], [ORDER_NO] ) VALUES ( Forms![Parent]!INV,Forms![Parent]!QTY , Forms![Parent]!FULLNAME, Forms![Parent]!CATEGORY, Forms![Parent]!COMMENTS, Forms![Parent]!AMOUNT, Forms![Parent]!FULLDATE2, Forms![Parent]!ORDERNO)"
    
        Next INCOUNT
    
    End If
    Cheers,
    Andrew
    Last edited by Bunce; 07-13-02 at 20:00.
    There have been many posts made throughout the world.
    This was one of them.

  10. #10
    Join Date
    Jun 2002
    Posts
    72
    Thanks for you help, but I'm now getting a runtime error on the following line:

    findsessions.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

    Error: Runtime error '-2137024770 (8007007e)':

    Method 'Connection' of Object '_CurrentProject' failed.

    The OS I'm currently using is Windows XP, don't know if this has anything to do with problem. Also the PC I'm using has had both Office 97 and Office 2000 installed at some point and I'm thinking may be this has something to do with the problem. Tried uninstalling them and reinstalling Office 2000 only but still get error.

    If I run the database under Windows 98 I get a different error on the same line - 'Active X can't find project'

    Any other ideas much appreciated, I desperately need to get this database working. If it comes to it I will wipe the PC clean and reload Windows and Access on a clean system, and as a last resort I'll try ringing up Microsoft!

    Thanks any way.

  11. #11
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Wow, are you able to upload the DB for me to have a look at?

    If not try adding this to your dims:
    Code:
    dim cn as adodb.connection
    Then add this line before the findsessions.open call:
    Code:
    set cn = Currentproject.Connection
    And replace the Currentproject.Connection statement in the findsessions.open call with cn.

    Also, try changing back the first recordset dim to:
    Code:
    dim findsessions as New ADODB.Recordset
    Must be something obvious we're missing..

    Cheers,
    Andrew
    Last edited by Bunce; 07-14-02 at 19:26.
    There have been many posts made throughout the world.
    This was one of them.

  12. #12
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    Ahh, shit, I think I gave you the wrong reference in my initial post. Try adding a reference to:
    Microsoft Active Data Objects 2.x Library, rather than
    Microsoft Active Data Objects Recordset 2.x Library

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  13. #13
    Join Date
    Jun 2002
    Posts
    72
    At last, after formatting the hard drive and reinstalling the Windows OS and Office, it now works as it should.

    The code was fine it must have been a corrupt .dll file or library.


  14. #14
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    LOL The answer to all problems!

    Oh well, glad it's up and running.

    /Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •