ACC: Cannot Set Attributes Property in Visual Basic Code

ID: Q171626


The information in this article applies to:


SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When you use the CreateTableDef method to create a table that is linked to a table in another Microsoft Access database or to an ODBC data source, you cannot set the Attributes property to dbAttachedTable or dbAttachedODBC. These constants are always read-only. When you create the linked table, you must to set the Connect property and the SourceTableName property. This automatically sets the Attributes property to dbAttachedTable or to dbAttachedODBC, whichever is appropriate. However, you can set the Attributes property to dbAttachExclusive or dbAttachSavePWD.

Note that once you have appended the table to the database, the Attributes property is read-only.

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.


MORE INFORMATION

The following sample procedure creates a linked table whose Attributes property is dbAttachedTable:

  1. Start Microsoft Access and create a blank database.


  2. Create a new module and type the following line in the Declarations section if it is not already there:
    
          Option Explicit 


  3. Type the following procedure:
    
          Sub CreateLinkedTable()
             Dim dbLocal As Database
             Dim tbfNewAttached As TableDef
    
             Set dbLocal = CurrentDb()
             Set tbfNewAttached = dbLocal.CreateTableDef("MyEmp")
    
             With tbfNewAttached
               .Connect = ";database=<your path to Northwind>"
               .SourceTableName = "Employees"
             End With
    
             dbLocal.TableDefs.Append tbfNewAttached
          End Sub 


  4. To test this function, type the following line in the Debug window, and then press ENTER:
    CreateLinkedTable


Note that the new linked table is shared. To test the Attributes property of the new table, type the following line in the Debug window, and then press ENTER:
?currentdb.TableDefs("MyEmp").Attributes = dbAttachedTable

This returns the value True to the Debug window.

To create a table that is linked exclusively, change the With...End With statement in the procedure in step 3 to the following:

   With tbfNewAttached

      .Connect = ";database=<your path to Northwind>"
      .SourceTableName = "Employees"
      .Attributes = dbAttachExclusive

   End With 

To test the Attributes property of this table, type the following on a single line in the Debug window, and then press ENTER:
?currentdb.TableDefs("MyEmp").Attributes = dbAttachedTable + dbAttachExclusive

The following sample With...End With statement sets the properties of a table linked to an ODBC DSN named "sqltest"; the table is linked to a table named dbo_employee in the remote data source.

   With tbfNewAttached

      .Connect = "ODBC;DSN=sqltest;UID=sa;PWD="
      .SourceTableName = "employee"

   End With 

To test the Attributes property of this table, type the following line in the Debug window, and then press ENTER:
?currentdb.TableDefs("MyEmp").Attributes = dbAttachedODBC


REFERENCES

For more information about the Attributes property, search the Help Index for "Attributes property."

Additional query words: attached VBA code


Keywords          : kbcode MdlDao 
Version           : 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: April 26, 1999