Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: ADO Connection Error User type not defined

  1. #1
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,542

    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 at 05:09. Reason: Added info on library

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,542

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    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

  6. #6
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    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

  8. #8
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    14th July 2004
    Posts
    10,542

    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"

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. User-defined Type Not Defined Error
    By touficdb in forum EXCEL HELP
    Replies: 11
    Last Post: June 28th, 2008, 14:31
  2. FileSystemObject: User-Defined Type Not Defined
    By bigblu89 in forum EXCEL HELP
    Replies: 12
    Last Post: November 29th, 2006, 10:12
  3. "Compile error: user-defined type not defined" in Excel VB6 Macro
    By desiboy in forum Excel and/or Access Help
    Replies: 3
    Last Post: December 28th, 2005, 01:15
  4. user defined type not defined
    By Blur in forum EXCEL HELP
    Replies: 3
    Last Post: October 14th, 2005, 17:47
  5. Replies: 3
    Last Post: September 27th, 2003, 02:35

Bookmarks

Posting Permissions

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