+ Reply to Thread
Results 1 to 18 of 18

Error message: "Compile error. User-defined type not defined"

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Error message: "Compile error. User-defined type not defined"

    Hi,

    I am trying to get an Excel sheet to read/write to an Access DB. My code starts as shown below.
    When running it, I get the error: "Compile error. User-defined type not defined" it will not let me proceed until i have sorted out the "Global cnPubs As ADODB.Connection" line.
    I am also guessing that the line below it "Global rsPubs As ADODB.Recordset" will also throw up the same error as it generally doesn't like it.

    i initially had an error come up run time erro '429' active X component can't create object and figured out that the right library wasn't selected. In tools-references i now have Microsoft ActiveX data objects (multi-dimensional) 2.8 library is selected and that run time error has now gone away and the above has now happened.

    Although I have been doing VB coding for some years now I an brand spanking new to the linking into access thing, I am looking after any compatibility issues with excel 2003 sheets moving onto 2010 as we are about to upgrade and this is one that has thrown up a problem so i thought I would try and fix it, I am regretting that now! Just to confirm I didn't write this and the person that did is no longer with the business.

    Any thoughts on how to solve this would be welcome

    thanks


    Please Login or Register  to view this content.
    Last edited by freckles81; 06-13-2012 at 08:43 AM.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    You need a reference set to the Microsoft ActiveX Data Objects 2.n library (the n is a version number, likely 8)
    Remember what the dormouse said
    Feed your head

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    ah ok, I see what I have been missing now, would that be this bit .provider?

    Please Login or Register  to view this content.
    Last edited by freckles81; 06-13-2012 at 08:43 AM.

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    No, the provider is separate. The ADO library I mentioned is where the ADODB.Connection (and ADODB.Recordset) comes from.

  5. #5
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    ok, sorry for being a bit slow on this, I am regretting even touching this now but I am being stubbourn and I WILL fix it

    where and how would i put that in, would it be in the string I have mentioned, in front of the Global cnPubs As ADODB.Connection

    Please Login or Register  to view this content.
    or would i put it somewhere in the body of the VB, shown below

    Please Login or Register  to view this content.
    Last edited by freckles81; 06-13-2012 at 08:44 AM.

  6. #6
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Error message: "Compile error. User-defined type not defined"

    Open VBE. Go to Tool->References and check Microsoft ActiveX Data Objects 2.n Library (the n is a version number, likely 8)
    That's what romperstomper is telling you in his first reply.

    Buran

    And please use CODE tags around your code. Not doing so is against forum rules.
    If you are pleased with a member's answer then use the Star icon to rate it.

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    Quote Originally Posted by freckles81 View Post
    i initially had an error come up run time erro '429' active X component can't create object and figured out that the right library wasn't selected. In tools-references i now have Microsoft ActiveX data objects (multi-dimensional) 2.8 library is selected and that run time error has now gone away and the above has now happened.
    thanks for that and sorry I thought I had done but just realised my buttons weren't working. I have amended it now.

    I already have that selected, as per above. It corrected a runtime error I had and now it comes up with the Compile error, am I missing something somewhere as I don't know what has happened.
    This worked fine in 2003 but is falling flat on this now in 2010

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    That is not the same reference. You want:
    Microsoft ActiveX Data Objects 2.8 Library
    and not:
    Microsoft ActiveX Data Objects (Multi-dimensional) 2.8 library.

  9. #9
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    ooooopppsss sorry just seen it was different. Changed it and the run time error comes back
    sorry about this, I don't know if I am being stupid or getting myself confused because its Access, wither way I am stuck. I don't know if its the compatibility with the versions or a license issue and I won't be able to fix it or whether it literally is coding!

    oh and my runtime error comes up on

    Please Login or Register  to view this content.
    which is within the VB i posted in #5

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    where does the runtime error occur?

  11. #11
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    oh and my runtime error comes up on

    Please Login or Register  to view this content.
    which is within the VB i posted in #5

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    Do you actually have Access installed?

  13. #13
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    yes I do, it all opens fine and opens the file the vb asks to access too

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    If the code errors there, how does it open fine?

  15. #15
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    I opened Access and opened the file from within Access seperately to make sure it did work just in case that was the issue before I went on to try and fix anything else.

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    Sounds like a configuration problem on your machine - see if this MSKB article helps. I assume it is failing on the first instance of that line?

  17. #17
    Registered User
    Join Date
    06-13-2012
    Location
    Norwich
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Error message: "Compile error. User-defined type not defined"

    Thank you, that has set me off on a different path to look into. I will have a good read and see what i come up with.
    Once again, thank you and sorry I am sure I was frsutrating to deal with

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Error message: "Compile error. User-defined type not defined"

    No, you weren't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1