none
Getting Compile error in Access 2010 Update query

    Question

  • Hi Developers,

    I am getting compile error "User defined type not defined" for following Access/VBA 2010 update query.

    Sub updatedata()
    Dim db As database


    Dim strsql As String
    strsql = " UPDATE Table1 RIGHT JOIN Table2 ON Table1.[SERIAL NUMBER] = Table2.[SERIAL NUMBER]" & _
    " SET Table1.[SERIAL NUMBER] = Table2.[SERIAL NUMBER], Table1.[User Name] = Table2.[User Name]"

    Set db = CurrentDb
    db.Execute strsql, dbFailOnError
    Set db = Nothing
    End Sub

    Thursday, June 07, 2012 1:51 PM

Answers

  • Do you get the error on the line

    Dim db As database

    If so:

    Select Tools | References... in the Visual Basic Editor.

    If your database is a .mdb database, locate Microsoft DAO 3.6 Object Library and tick its check box.

    If your database is a .accdb database, locate Microsoft Office n.0 Access database engine Object library and tick its check box, where n=12 for Access 2007 and n=14 for Access 2010.

    Finally click OK.


    Regards, Hans Vogelaar

    • Marked as answer by zaveri cc Thursday, June 07, 2012 3:59 PM
    Thursday, June 07, 2012 2:08 PM

All replies

  • Do you get the error on the line

    Dim db As database

    If so:

    Select Tools | References... in the Visual Basic Editor.

    If your database is a .mdb database, locate Microsoft DAO 3.6 Object Library and tick its check box.

    If your database is a .accdb database, locate Microsoft Office n.0 Access database engine Object library and tick its check box, where n=12 for Access 2007 and n=14 for Access 2010.

    Finally click OK.


    Regards, Hans Vogelaar

    • Marked as answer by zaveri cc Thursday, June 07, 2012 3:59 PM
    Thursday, June 07, 2012 2:08 PM
  • I believe Hans has correctly identified the problem. However, you might be able to resolve it by not declaring a Database object:

    Sub updatedata()
    Dim strsql As String
      strsql = " UPDATE Table1 RIGHT JOIN Table2 ON Table1.[SERIAL NUMBER] = Table2.[SERIAL NUMBER]" & _
    " SET Table1.[SERIAL NUMBER] = Table2.[SERIAL NUMBER], Table1.[User Name] = Table2.[User Name]"
      CurrentDb.Execute strsql, 128
    End Sub


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Thursday, June 07, 2012 2:14 PM
  • Thanks Hans and Douglas

    My problem is resolved.

    Thursday, June 07, 2012 3:59 PM