ACC: Cannot Use Code to Set the LogMessages Property (118367)

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you create an SQL pass-through query in a Visual Basic for Applications procedure, you may receive the following error message when you try to set the LogMessages property of the QueryDef object.

In Microsoft Access 7.0 or 97:

Method or data member not found

In Microsoft Access version 2.0:

Name not found in this collection

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

CAUSE

LogMessages is an extended property that cannot be addressed directly as a property of the QueryDef object because an extended property does not exist within the QueryDefs collection. You can use the property sheet of a query to set the LogMessages property setting, or you can use the CreateProperty function in code to append the LogMessages property to the query object. Then you can set the property in your procedure.

RESOLUTION

Because the LogMessages property is an extended property, it must be defined, and then appended to the QueryDef object before you can set the value of the property. For example, given a QueryDef object dimensioned as MyQD, you need to add the following code to your Visual Basic for Applications procedure before you set the LogMessages property:

   Dim MyProp as Property
   ...
   Set MyProp = MyQD.CreateProperty("LogMessages", DB_BOOLEAN, True)
   MyQD.Properties.Append MyProp

After MyProp is appended to the Properties collection of MyQD, use the following syntax when you reference the LogMessages property:

   MyQD.Properties("LogMessages") = True

MORE INFORMATION

Steps to Reproduce Behavior

The following sample code demonstrates the error message that occurs when you try to set the LogMessages property of the QueryDef object.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Option Explicit
   Function MyFunct ()
      Dim MyDB as Database
      Dim MyQD as QueryDef
      Set MyDB = CurrentDb()
      Set MyQD = MyDB.CreateQueryDef("QueryName")
      MyQD.connect = "ODBC;DSN=Server_Name;UID=UserName; _
         PWD=MyPassword; DATABASE=DB_Name"
      MyQD.sql = "SELECT * from TableName"
      MyQD.returnsrecords = True
      ' The following line generates the error message
      MyQD.LogMessages = True
      MyQD.Close
   End Function

The following sample code demonstrates how to change the sample code above to set the LogMessages property correctly:

    Option Explicit
   Function MyFunct2 ()
      Dim MyDB as Database
      Dim MyQD as QueryDef

      ' Dimension a variable as type Property.
      Dim MyProp as Property
      Set MyDB = DBEngine.Workspaces(0).Databases(0)
      Set MyQD = MyDB.CreateQueryDef("QueryName")

      ' Create a Boolean property called LogMessages.
      Set MyProp = MyQD.CreateProperty("LogMessages",DB_BOOLEAN,True)

      ' Append the property to the QueryDef variable.
      MyQD.Properties.Append MyProp
      MyQD.connect = "ODBC;DSN=Server_Name;UID=UserName; _
         PWD=MyPassword; DATABASE=DB_Name"
      MyQD.sql = "SELECT * from TableName"
      MyQD.returnsrecords = True

      ' Set a value for the property you just created
      MyQD.Properties("LogMessages") = True
      MyQD.Close
   End Function