none
Object Library Not registered - Access 2007 VBA

    Question

  •   I have a form that I am trying to get data from and use as date range validation and also a value in the combo box to use as a key to the other table and get the description.  Both are causing problems and when I try to debug to look at the values and see what they are, I am getting "Object Libraries Not Registered".  This is very frustrating, since it should not be this complicated.  Been trying for 3 days to get this to work and now I am reaching out for help.

      I need help with the date range criteria and also gettting the value from the Combo Box.  Using Access 2007.  The code is as follows and I would really like appreciate some guidance and help with this.  Here is the code:

    ============================


    Private Sub cmdCreateFile_Click()

        
        Dim db As Database
        Dim TblIns As String
        Set db = CurrentDb()
        Dim AdKey As String
        AdKey = Me.Email_Ad_Key.ItemData(1)
        
       
    TblIns = "INSERT INTO tblMailList ( Phone_Number, Email_To, Email_Body, Date_Paid_Bill )"
    TblIns = TblIns & " SELECT tblMain.Phone_Number, tblMain.Phone_Number & tblMain.Email_Provider, "
    TblIns = TblIns & " tblAds.Ad_Body, tblTrans.Date_Paid_Bill "
    TblIns = TblIns & " FROM tblAds, tblTrans, tblMain "
    TblIns = TblIns & " WHERE (tblMain.Send_Email=True) And (tblMain.Email_provider Is Not Null) "
    TblIns = TblIns & " And (tblMain.Phone_Number = tblTrans.Phone_Number) "
    TblIns = TblIns & " And (tblTrans.Date_Paid_Bill >= " & Me.Sdate_Sel & ") "
    TblIns = TblIns & " And  tblTrans.Date_Paid_Bill <= " & Me.Edate_Sel & ") "
    TblIns = TblIns & " AND (tblAds.Ad_Name  =  " & AdKey & ");"

        db.Execute TblIns
                  
        MsgBox "Update Complete"

    End Sub

    =====================================================

    Thanx in advance.

    Jerry

    Tuesday, October 05, 2010 11:32 AM

Answers

  • hi Jerry,

    This kind of error is normally caused due to a reference issue. Check whether you have a Microsoft DAO 3.6 Library reference (VBA IDE, Tools\References).

    Change the declaration of you database variable:

      Dim db As DAO.Database

    and use message box to test your statement:

       MsgBox TblIns
       db.Execute TblIns

    Use Ctrl+C to copy the content of this message box and insert it into a new query (SQL view). It should run without error.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 11:46 AM
  • Hi ballj,

    and what the line is highlighted when you press Debug button?

    I can be mistaken, but I see nothing to invoke some libraries in this piece of code except Dim db As Database. Maybe missing references? Microsoft DAO 3.6 Object Library, for example?


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 11:47 AM
  • As you are using A2007, don't bother with the Microsoft DAO 3.6 Object Library.

    The usual references for A2007 are:

    Visual Basic for Applications
    Micosoft Access 12.0 Object Library
    Microsoft Office 12.0 database engine Object Library


    You might also need
    Microsoft Office 12.0 Object Library
    OLE Automation


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 8:19 PM

All replies

  • hi Jerry,

    This kind of error is normally caused due to a reference issue. Check whether you have a Microsoft DAO 3.6 Library reference (VBA IDE, Tools\References).

    Change the declaration of you database variable:

      Dim db As DAO.Database

    and use message box to test your statement:

       MsgBox TblIns
       db.Execute TblIns

    Use Ctrl+C to copy the content of this message box and insert it into a new query (SQL view). It should run without error.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 11:46 AM
  • Hi ballj,

    and what the line is highlighted when you press Debug button?

    I can be mistaken, but I see nothing to invoke some libraries in this piece of code except Dim db As Database. Maybe missing references? Microsoft DAO 3.6 Object Library, for example?


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 11:47 AM
  • As you are using A2007, don't bother with the Microsoft DAO 3.6 Object Library.

    The usual references for A2007 are:

    Visual Basic for Applications
    Micosoft Access 12.0 Object Library
    Microsoft Office 12.0 database engine Object Library


    You might also need
    Microsoft Office 12.0 Object Library
    OLE Automation


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    • Marked as answer by Bessie Zhao Tuesday, October 12, 2010 10:11 AM
    Tuesday, October 05, 2010 8:19 PM
  • From where  i can install Micosoft Access 12.0 Object Library.

    Any where online

    thanks

    Monday, March 05, 2012 1:22 PM
  • @ Developer 100

    The Microsoft Access 12.0 Object Library comes with the Access 2007 version.

    When creating a new Access database, this Library is automatically ticked in your References.

    When in VBE Window, goto Tools > References >then you will find the Library there.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 05, 2012 4:48 PM