Microsoft KB Archive/109711

INF: OpenTable Method on Attached Table Causes Error (1.x)

PSS ID Number: Q109711 Article last modified on 10-25-1995

1.00 1.10

WINDOWS

The information in this article applies to:

- Microsoft Access versions 1.0, 1.1

SUMMARY

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

Because an attached table does not actually exist in the current database, it cannot be opened with Access Basic code as a table object from the current database. Trying to do so will generate the following error message:

Can’t perform operation; it is illegal.

-or-

Invalid operation.

MORE INFORMATION

If the attached table is a Microsoft Access table and does not use an ISAM driver (such as the dBASE, Paradox, Btrieve, or Fox ISAM) or an ODBC driver, you can first use the OpenDatabase method to open the database that contains the attached table. Then, you can use the OpenTable method to open the table. This technique is demonstrated in the sample code below:

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the “Introduction to Programming” manual.

1. Create a module and type the following line in the Declarations section:

   Option Explicit

2. Type the following procedure:

   Function OpenEmployee() Dim MyDB as Database Dim MyTable as Table Set MyDB = OpenDatabase(“NWIND.MDB”) Set MyTable = MyDB.OpenTable(“Employees”) MsgBox MyTable[First Name] End Function

3. To test this function, type the following line in the Immediate window, and then press ENTER:

   ? OpenEmployee()

   Note that the message box opens with the first name of the first employee record.

If the table uses an ISAM or ODBC driver, you must use the CreateDynaset method instead. The following example demonstrates this method. In this example, dbo_Authors is an attached SQL Server table:

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the “Introduction to Programming” manual.

1. Create a module and type the following line in the Declarations section:

   Option Explicit

2. Type the following procedure:

   Function OpenAuthors() Dim MyDB as Database Dim MySet as Dynaset Set MyDB = CurrentDB() Set MySet = MyDB.CreateDynaset(“dbo_Authors”) MsgBox MySet[au_lname]

   End Function

3. To test this function, type the following line in the Immediate window, and then press ENTER:

   ? OpenAuthors()

   Note that the message box opens.

Steps to Reproduce Behavior

1. Open the sample database NWIND.MDB.

2. From the File menu, choose Attach Table.

3. In the Data Source box, select dBASE III, and then choose OK.

4. In the File Name box, select NEWCUST.DBF, and then choose Attach.

5. In the Select Index Files dialog box, choose Close. Choose OK in the confirmation dialog box. In the Select File dialog box, choose Close.

6. Create a new module with the following sample code:

   ‘*********************************************************** ’Declarations section of the module’***********************************************************

   Option Explicit

   ‘============================================================’ This function will set the database to the current database ’ and try to open an attached table. ’============================================================

   Function AttachTest() Dim MyDB as Database Dim MyTable as Table Set MyDB = CurrentDB() Set MyTable = MyDB.OpenTable(“NEWCUST”) End Function

7. Type the following line in the Immediate window, and then press ENTER:

   ? AttachTest()

   Note that an error message appears. To correct the problem, use the CreateDynaset method rather than the OpenTable method.

REFERENCES

Microsoft Access “Introduction to Programming,” version 1.0, Chapter 8, “Manipulating Data,” pages 97-138

Additional reference words: 1.00 1.10 KBCategory: kbprg KBSubcategory: PgmOthr ============================================================================= Copyright Microsoft Corporation 1995.