StarQuest Technical Documents

Using StarSQL with Microsoft Access FAQs

Last Update: 26 June 2008
Product: StarSQL
Versions: 5.x
Article ID: SQV00SQ041

Abstract

This technical document presents solutions for common problems encountered with StarSQL and Microsoft Access 2.0, 7.0 and 8.0.

The following problems and error messages are covered in this document:

• Table data is #Deleted or #Name?
  
• Table locks
  
• Unable to update tables with timestamp columns
  
• "[-815] the execute statement does not identify a valid prepared statement"
  
• Unable to update tables
  
• "The definition of a table is incomplete because it lacks a primary index"
  
• Access is slow to return data
  
• No table list is returned
  
• #Error? or #Name? appears in a control.
  

If you have a problem with StarSQL and Microsoft Access that is not covered in this FAQ, please check the Microsoft Access help, the Microsoft Knowledge Base (support.microsoft.com), or contact StarQuest support at contact@starquest.com.

---

Solution

Symptoms

You scroll or skip to last field in an attached table. The cells in the table are filled by #Deleted or #Name?

Cause

See the Microsoft knowledge base article, "#Deleted" Errors with Attached ODBC Tables (Article ID: Q128809).

Resolution

• If you are connecting to an AS/400 or to DB2 for OS/390 (DB2/MVS), enable held cursors (in Advanced Options) for the StarSQL data source. You will need to reattach the table.
  Note: This workaround is not available for DB2 for VSE/VM (SQL/DS) or for DB2 Universal (Common Server).

• If you do not need to update the table, attach the table as a read-only table (See MSKB article Q100972). You can use this workaround for all DB2 platforms.

• If you know the name of the table you want to access, you can also use a SQL pass through query to view and/or modify the table data. You can use this workaround for all DB2 platforms.

Return to the top

---

Symptom

Microsoft Access holds table locks.

Resolution

• Access does not disconnect all the threads from DB2 until you close the application or issue a commit to free the table locks. If you are connecting to an AS/400 or to DB2 for OS/390 (DB2/MVS), enable held cursors (in Advanced Options) for the StarSQL data source. You will need to reattach the table.
  
  Note: This workaround is not available for DB2 for VSE/VM (SQL/DS) or for DB2 Universal (Common Server).

Return to the top

---

Symptoms

When scrolling through or updating an attached table that has a timestamp column, you get an error message: "This record has been changed by another user since you started reading it, if you save the record you will overwrite the changes made by the other user, copying the changes to the clipboard will let you look at the values the other user entered and then paste the changes back if you decide to use them"

Resolution

• If the attached table has a column that contains timestamps, enable the StarSQL workaround for timestamps.

• If you know the SQL statement you need to execute, use a SQL pass through query to update the table data.

Return to the top

---

Symptoms

You scroll through a table or skip to the last row of the table, and get the error message, "[-815] the execute statement does not identify a valid prepared statement"

Resolution

• If you are connecting to an AS/400 or to DB2 for OS/390 (DB2/MVS), enable held cursors (in Advanced Options) for the StarSQL data source. You will need to reattach the table.
  Note: This workaround is not available for DB2 for VSE/VM (SQL/DS) or for DB2 Universal (Common Server).

• If you do not need to update the table, attach the table as a read-only table (see MSKB Article ID Q100972). You can use this workaround for all DB2 platforms.

• If you know the name of the table you want to access, you can also use a SQL pass through query to view and/or modify the table data. You can use this workaround for all DB2 platforms.

Return to the top

---

Symptoms

You are unable to insert, delete or update records in an attached DB2 table.

Cause

Microsoft Access requires the attached table have a unique index in order to insert, delete or update it.

Resolution

• If the table has a unique index on DB2, make sure that you do not attach the table as a read-only table (see MSKB Article Q100972). For more information, see Remote ODBC Tables Are Read-Only Without a Unique Index, Article ID Q90100.

Return to the top

---

Symptoms

You are unable to update records in or open an attached DB2 table. You get an error message, "The definition of table <authid>.<table_name> is incomplete because it lacks a primary index or a required unique index (#-540)"

Cause

The Microsoft Jet engine is built around indexed access modes and requires unique indexes to perform inserts, modifications, and even to move pointers from record to record.

For more information about Microsoft Access and the JET engine, see: Jet & ODBC White Paper Available on MSL (Article ID Q128385).

Resolution

• If you would like to insert into, update or delete records from the table, the table must have a unique DB2 index.

• If the table has a unique index on DB2, make sure that you do not attach the table as a read-only table (see Remote ODBC Tables Are Read-Only Without a Unique Index, Article ID Q90100).

Return to the top

---

Symptom

Access is slow to return data.

Resolution

See the Microsoft knowledge base article Optimizing for Client/Server Performance (Article ID Q128808).

If you are attempting to join a local Access table with a DB2 table, see How to Join a DB2 table and a Local Microsoft Access Table.

Return to the top

---

Symptom

Access times out returning a table list.

Resolution

• Make sure that your network software is properly configured
• If you have a very long system table list, change the catalog qualifer for the StarSQL data source to limit the number of tables returned to Access. Here are recommended catalog qualifers:

  AS/400: QSYS2.<library_name>
  DB2: SYSIBM.<authorization_ID>
  SQL/DS: SYSTEM.<authorization_ID>
  Common Server: SYSCAT.<authorization_ID>

• You can also create a restricted view of the system table catalogs and use the name of the view as the catalog qualifier. StarAdmin is a StarSQL utility that helps you create restricted views.
• See Microsoft's knowledge base article, Upper Limit on QueryTimeout and LoginTimeout (Article ID Q100176).

Return to the top

Symptom

#Error? or #Name? may appear in a control for a number of reasons. To correct the problem, do the following:

Resolution
From the MS Access 97 Help Files

• Make sure that the field specified in the control's ControlSource property hasn't been removed from the underlying table, query, or SQL statement.
• Check the spelling of the field name in the control's ControlSource property.
• If you specified an expression in the control's ControlSource property, make sure that there is an equal sign preceding the expression.
• Make sure that there are brackets around references in expressions to control or field names that include spaces. For example, to subtract a Shipped Date field from a Required Data field, enter the following expression: =[Required Date]-[Shipped Date].
• If you are using one of the built-in functions, make sure that you're using the right number of arguments, that the arguments are in the right order, and that you haven't left out any necessary punctuation. To determine the correct syntax to use, see the Access Help topic for the function you're using.
• If you are referring to another control, make sure that you're using the correct syntax.
• Make sure that there isn't a circular reference to a control. For example, if you specify MyControl in the Name property of a control, and then type =[A]+[B]+[MyControl] in the ControlSource property box for the control, Microsoft Access can't process the expression.

Return to the top