Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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...
    VB:
     'Create the connection
    Dim cnn As New ADODB.Connection 
    Set cnn = New Connection 
    cnn.Open DB_CONNECT_STRING 
    
    
    Thanks
    -marc

    Full :
    VB:
    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,539

    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 04: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,539

    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,677

    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

    Web Presence:
    Personal: Datasapien
    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,677

    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

    Web Presence:
    Personal: Datasapien
    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
    VB:
    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,539

    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.
    VB:
    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, 13:31
  2. FileSystemObject: User-Defined Type Not Defined
    By bigblu89 in forum EXCEL HELP
    Replies: 12
    Last Post: November 29th, 2006, 09: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, 00:15
  4. user defined type not defined
    By Blur in forum EXCEL HELP
    Replies: 3
    Last Post: October 14th, 2005, 16:47
  5. Replies: 3
    Last Post: September 27th, 2003, 01: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