ACC: Cannot Set LogMessages Property Using Code

• Microsoft Access versions 2.0, 7.0, 97

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 QueryDef object's LogMessages property.

In Microsoft Access 7.0 or 97:

   Method or data member not found

   Name not found in this collection

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 set the LogMessages property setting using the query's property sheet, 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

   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 QueryDef object's LogMessages property.

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

   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

Keywords          : kberrmsg kbusage ODBCSPT QryPass
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided