More Access Tips

  • Read-only capability
    When creating read-only forms based on ODBC queries, make sure the Allow Edits property of the form is disabled.

  • Creating reports
    When viewing static ODBC data in Access, create a report based on the query instead of viewing the query directly. All Access reports are snapshots instead of dynasets and execute faster than their query counterpart.

  • Executing queries
    When executing queries that take longer that 60 seconds, be sure to set the ODBC time-out property of the query to zero.

[ODBC]
QUERYTIMEOUT=0

 

With version 8.6, when you are executing queries, the default behaviour is to run the query asynchronously. This means that after every ODBC api call, control is returned to Access so that Access may repaint itself so that users do not get the impression that Access has stopped running. This handoff of control means queries take longer to execute and return all data.

To force CONNX to run the queries synchronously, a setting needs to be added via the Configuration Manager. Under the CONNX heading, a new value called ASYNCACCESS needs to be created with the value data of 0. To restore asynchronous functionality, change the value data to 1, or remove the ASYNCACCESS value.  

 

[HKEY_LOCAL_MACHINE\SOFTWARE\CONNX SOLUTIONS\CONNX]

ASYNCACCESS=0

NUMBER (REG_DWORD)

 

With version 8.7, when you are executing queries, the default behavior is to run the query synchronously. This means that CONNX will not return control to Access until the query has completed execution. While the query is executing, Access will not respond to mouse clicks or key presses. This means that Access will not repaint its window and you will not be able to cancel the query with Ctrl+Break. When Access does not repaint the window, you may get the impression that Access has hung, but this is not the case.

To force CONNX to execute querues asynchronously, a setting needs to be added via the Configuration Manager. Under the CONNX heading, a new value called "ASYNCACCESS" needs to be created with the value data of "1". This will allow you to cancel the query with Ctrl+Break, and allows Access to repaint its window properly. There can be a severe impact to performance when the query is executing asynchronously, due to the excessive transferring of control between applications.

 

[HKEY_LOCAL_MACHINE\SOFTWARE\CONNX SOLUTIONS\CONNX]

ASYNCACCESS=1

NUMBER (REG_DWORD)

 

Another caveat of the query executing asynchronously is the fact that you will need to set the "ODBCTimeout" value for queries that require more than 60 seconds to execute.

 

[ODBC]

QUERYTIMEOUT=0

 

To restore synchronous functionality, change the value data of ASYNCACCESS to 0, or remove the value from the registry.

  • Catalog support
    Microsoft Access supports two-part table names that include only the schema and object name.
    The three Customers tables in the Link Table dialog box below cannot be successfully linked because Access cannot identify the data source.

    image\AccessSame.jpg

    Identify the data source by renaming your tables before attempting to link them within Microsoft Access.