PRB: ACCESS Cannot Import/Link Table if Index Contains Function (188401)
The information in this article applies to:
- Microsoft Visual FoxPro for Windows 3.0
- Microsoft Visual FoxPro for Windows 3.0b
- Microsoft Visual FoxPro for Windows 5.0
- Microsoft Visual FoxPro for Windows 5.0a
- Microsoft Data Access Components 2.5
This article was previously published under Q188401 SYMPTOMS
If a Visual FoxPro table has an index tag that calls a User Defined
Function or a FoxPro function like DELETED() or RECNO(), the table
cannot be imported or linked from Microsoft Access.
Microsoft Access does not seem to have a problem with string functions like
SUBSTR(), LEFT(), RIGHT(), AT(), STRTRAN(), TRIM(), ALLTRIM(), or STUFF().
NOTE: Under Microsoft Visual Basic version 5.0 and 6.0, using ODBC and connecting to a FoxPro table with an Index tag that calls a function causes an application error to occur.
RESOLUTIONWorkarounds
There are two ways to work around this behavior:
- Do not use an index tag that calls a function.
-or- - Create a local view in Visual FoxPro based on the table with the offending index and link to the view instead.
To accomplish this, use the following steps:
- From Visual FoxPro, create a new database container (DBC). This is not really required, but is preferable if you wish to leave your existing DBC unmodified, or if the table to which you want to link is a free table. Open the DBC in the Database Designer.
- Create a new Local View. The View Designer will open. When prompted for a table, select the table to which you want to link.
- In the View Designer, select the fields you want to link to in the Fields tab.
- This step is only required if you want the data to be updateable. Select the Update Criteria tab in the View Designer. Select the key field under the Key column, and select every field that you want to be updateable under the Pencil column in the Field name list. Select
the Send SQL updates check box. If you leave the Send SQL updates check box is cleared, the changes you make when you are linked to the view will not be saved back to the table.
- Save the view. The name you save it under will appear as the tablename in the list when you connect through ODBC.
- Close the View Designer and Database Designer.
- To connect, make an ODBC Datasource that uses the DBC you created in step 1. The list of tables will include the view you saved in step 5. If you created a view for each table in the customer's DBC (repeat steps 2-5 for each table) then you could link to all of them.
STATUS
Microsoft is researching this problem and will post new information here in
the Microsoft Knowledge Base as it becomes available.
Modification Type: | Minor | Last Reviewed: | 3/3/2005 |
---|
Keywords: | kbprb KB188401 |
---|
|