Announcement

Collapse
No announcement yet.

ADO Connection Error User type not defined

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • ADO Connection Error User type not defined

    Hi all -

    Using Win XP pro / Office 2003
    Modified some ADO code I found on Dennis' site
    To retrieve some records from Access database.

    Compile error:
    User-defined type not defined

    Debug comes to line Dim cnn...
    Code:
    'Create the connection
    Dim cnn As New ADODB.Connection
    Set cnn = New Connection
    cnn.Open DB_CONNECT_STRING
    Thanks
    -marc

    Full :
    Code:
    Sub GetARData()
    
    On Error GoTo ErrHandler
    
    '//Decalrations
        Dim wbBook As Workbook
        Dim wsData As Worksheet
        Dim rg As Range
        Dim strDBPath As String
        Dim strDBName As String
        
    '//Environment
        With Application
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
            .DisplayAlerts = True
        End With
        
    '//Objects
        Set wbBook = ThisWorkbook
        Set wsData = wbBook.Worksheets("Data")
    
    '//Processes
    
    '======================================
    '==============Early Binding===========
    '======================================
    'Early binding during development stage
        Dim XLObj As Excel.Application
            Set XLObj = New Excel.Application
    
    '======================================
    '==============Late Binding===========
    '======================================
    'Switch to Late Binding prior to deployment
    'Dim XLObj As Object
    'Set XLObj = CreateObject("Excel.Application")
    
     
     
    
    'Fully quality the path to the .db
    
    Dim db_Name As String
        'Change the path below to refernece .db on server prior to deployment
        strDBPath = "C:\Documents and Settings\wsnyder\My Documents\ASU_New\ar\"
        strDBName = "Invoices_xp.mdb"
        db_Name = strDBPath & strDBName
        Dim DB_CONNECT_STRING As String
    
    
    DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "data Source=" & db_Name & ";" & ", , , adConnectAsync;"
    
    
    'Create the connection
    Dim cnn As New ADODB.Connection
    Set cnn = New Connection
    cnn.Open DB_CONNECT_STRING
    
    
    'Create the recordset
    Dim rs As ADODB.Recordset
    Set rs = New Recordset
    
    
    'Determines what records to show
    Dim strSQL As String
    strSQL = "SELECT Inv_Nmbr" & _
    "FROM Tbl_Invoices ORDER BY Inv_Nmbr"
    
    
    'Retreive the records
    rs.CursorLocation = adUseClient
    rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic
    
    
    'Test to see if we are connected and have records
    Dim num As Integer
    num = rs.RecordCount
    
    
    Dim num1 As Integer
    num1 = rs.Fields.Count
    
    
    'If cnn.State = adStateOpen Then
    '    MsgBox "Welcome to!  " & db_Name & "   Records =  " & num & "  Fields  = "" & num1, vbInformation,"Good Luck -ws"
    '   Else
    '     MsgBox "Sorry. No Data today."
    '   End If
    
    
    'Copy recordset to the range
    With wsData
        Set rg = .Range("A1")
    End With
    rs.MoveLast
    rs.MoveFirst
    rg.CopyFromRecordset rs
    rg.CurrentRegion.Columns.AutoFit
    
     '//Cleanup=======================================
        'Close connection
            cnn.Close
            Set cnn = Nothing
            Set rs = Nothing
            
            Set wbBook = Nothing
            Set wsData = Nothing
            Set C = Nothing
        
            With Application
                .ScreenUpdating = True
                .Calculation = xlCalculationAutomatic
                .DisplayAlerts = True
            End With
    
    Exit Sub
    
    
    ErrHandler:
        MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
    End Sub

  • #2
    Re: ADO Connection Error User type not defined

    Under Tools>References... have you set a reference to the appropriate object library?

    Is setting such a reference not mentioned on Dennis' site? I can't imagine it isn't.

    The reference on my machine is Microsoft ActiveX Data Objects 2.5 Library.
    Last edited by norie; November 26th, 2005, 05:09. Reason: Added info on library
    Boo!

    Comment


    • #3
      Re: ADO Connection Error User type not defined

      Thanks Norie -

      I thought I read somewhere, but forgot, how to set the reference through code.

      I would prefer not to have to set the reference on each target pc I deploy
      the app to.

      Thanks
      -marc

      Comment


      • #4
        Re: ADO Connection Error User type not defined

        Well what you want to do is use Late binding, there's an example of it (commented) out in the code you posted.

        It actually pertains to the Excel object but you should be able to apply the same principles to the other objects.

        Perhaps the site you got the code from has more information.
        Boo!

        Comment


        • #5
          Re: ADO Connection Error User type not defined

          If you want to use early binding but programmatically add the reference(s), check out this thread

          http://www.ozgrid.com/forum/showthread.php?t=22483
          Kind Regards, Will Riley

          LinkedIn: Will Riley

          Comment


          • #6
            Re: ADO Connection Error User type not defined

            Thanks Will -

            Can you point me or remind me to the advantages/disadvantages of eraly/late binding.

            I was looking into ADO some months ago, but got sidetracked.
            I pulled down Dennis; code and commented out the Late Binding
            And added the comments :

            Early Binding for Development
            Late Binding for Deployment

            But I do not recall why.

            Thanks
            -marc

            Comment


            • #7
              Re: ADO Connection Error User type not defined

              Rather than reinventing the wheel, here are a few good articles on the subject by people with more knowledge than me

              http://word.mvps.org/FAQs/InterDev/E...ateBinding.htm - Dave Rado(MVP)

              http://www.dicks-clicks.com/excel/olBinding.htm - Dick Kusleika (MVP)

              http://peltiertech.com/Excel/EarlyLateBinding.html - Jon Peltier (MVP)
              Kind Regards, Will Riley

              LinkedIn: Will Riley

              Comment


              • #8
                Re: ADO Connection Error User type not defined

                Thanks Will -

                I attempted to run "Grab_References" from lasw10
                I first checked MS ADO 2.5 Library
                GrabReferences added the worksheet, but nothing entered onto worksheet

                Saved >> Rebooted >> same outcome

                Left the GUID as is in the Workbook_open event in the code.
                The comment refers to the ADO Ref Library which is what I want, though it does not specify version.

                Tools >> references >> lists up to 2.8 on the development system (Office 2003).

                I unchecked the reference, Saved, Closed, Restarted the workbook.
                I ran the code to get the data from Access. Received same "User Data Type..." error message as previously.

                I commented out Open and Before_Close Events, I checked the ADO 2.5 Library Reference.

                Ran my code, I receive this error message:
                Sorry,an error occured. Syntax error (missing operator) in query expression
                Quite polite.

                Here is the SQL section of my code
                Code:
                Dim strSQL As String
                strSQL = "SELECT Inv_Nmbr" & _
                "FROM Tbl_Invoices ORDER BY Inv_Nmbr"
                Sorry so long
                Thanks for the great help
                -marc

                Comment


                • #9
                  Re: ADO Connection Error User type not defined

                  marc

                  I don't think this has anything to do with references or early/late binding.

                  It might be a typo but as far as I can see you have a mistake in the SQL string.

                  There should be a space between Nmbr and FROM.
                  Code:
                  Dim strSQL As String 
                  strSQL = "SELECT Inv_Nmbr " & _ 
                               "FROM Tbl_Invoices ORDER BY Inv_Nmbr"
                  Boo!

                  Comment


                  • #10
                    Re: ADO Connection Error User type not defined

                    Thanks Norie -

                    The space was indeed the culprit.
                    Any thoughts on the binding stuff as well?

                    WillR pointed me to this thread
                    ADO Bind Programmatically

                    Can't seem to get it to work.
                    I suspect because the GUID is incorrect.

                    Thanks
                    -marc

                    Comment


                    • #11
                      Re: ADO Connection Error User type not defined

                      Did you look at any of the links Will posted regarding late binding?

                      If what you are doing is going to be distributed to other people you probably want to use late binding rather than programmatically set a reference to the library.

                      The reason for this is because different versions of Excel might require different versions of the library.

                      With late binding you shouldn't have to worry about the reference at all.
                      Boo!

                      Comment


                      • #12
                        Re: ADO Connection Error User type not defined

                        Yes -

                        The first link by Dave Rado pointed out that Late Binding would cause slower execution because not compiled but version independent

                        I'm almost 100% positive all users have Office 2000 or better which means they should have ADO Library 2.5 which is what I'd like to set the reference to.

                        Thanks
                        -marc

                        Comment


                        • #13
                          Re: ADO Connection Error User type not defined

                          Bump

                          Hi all.
                          Still unable to get Grab References to work from this thread Will pointed me to
                          Grab References

                          Thanks all
                          -marc

                          Comment

                          Working...
                          X