My client fails to connect to the Pervasive Server engine when an attempt is made to open a combo box on an Access form.
 
Background:
 
Server: Pervasive ODBC server setup, DBA, Access Project which has linked tables to DBA
Client: Access installed, Pervasive ODBC Client setup.
 
The client goes across the network to open the Access Project which is saved on the server, this is successful, but when a combo box of customer information is selected on a form then this message appears:
“ODBC – connection to ‘(company)DB failed.”
 
Also the client fails to open the shortcut I have created on the server to the Access Project with this message:
 
“Microsoft Access can’t find the shortcut database ‘C:\DBAMFG\Access Reports\Sales Order Project.mdb’.  Re-create the shortcut with the correct location of the database.”
 
When I test the client connection within ODBC Admin the connection is successful.
 
I setup the server & client ODBC DSN following the instructions on the DBA ODBC Connectivity Center.
 
Has anyone had this problem before?

The problem is not ODBC. It's the combo box's query.  
 
As an alternative, you can use an 'On Open Event' on the form or 'On Got Focus Event' on the Combo Box to create a temporary table.
 
ciao,
 
Kelloggs
 

Kelloggs is atlking about using some VBA code.
 
As to how to give users direct access to the form: there are a couple of options. Both a QUITE involved. If you like you can email me direct and I can give you some high-level guidance.

Kelloggs,
 
Sorry about that. You're right. It's in the properties of the combo box for the query.
 
And GOOD QUESTION about the Access Project!! I hadn't even caught that. I just saw Access and assumed that davson was using a regular Access DB. I didn't think that an Access 2003 project would even connect to Pervasive.....but apparently it worked....how about that.....

An Access project is called a project because it contains only code-based or HTML-based database objects: forms, reports, the name and location of data access pages, macros, and modules. These are the database objects that you use to create an application. Unlike a Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.), an Access project does not contain any data or data definition based objects: tables (table: A database object that stores data in records (rows) and fields (columns). The data is usually about a particular category of things, such as employees or orders.), views (view (object): In an Access project, a type of query that is a virtual table based on an SQL SELECT statement. For example, a view might contain only 3 out of 10 available columns in a join of two tables, in order to limit access to certain data.), database diagrams (database diagram: A graphical representation of any portion of a database schema. It can be either a whole or partial picture of the structure of the database. It includes tables, the columns they contain, and the relationships between the tables.), stored procedures (stored procedure: A precompiled collection of SQL statements and optional control-of-flow statements that is stored under a name and processed as a unit. The collection is stored in an SQL database and can be run with one call from an application.), or user-defined functions (user-defined function: A query that takes input parameters and returns a result like a stored procedure. Types: scalar (multistatement; returns one value), inline (one statement; an updateable table value), and table (multistatement; table value).). Instead, these database objects are stored in the SQL Server database.
 
You can tell the difference by looking at the extension on the file you created. A Project has an .ADP extension and an Access database has an .MDB extension.

Kelloggs-I have hit a few problems trying to follow your steps. Hope you can help. Thanks.
 
On the query this part of the SQL statement 'TMP_MyFormMyCombo' - should there be a '.' between form&combo?
On the form properties I don't see 'Open Event' which tab is this? Is is 'Event' and if so then which section do I add the marco to?
On the combobox 'Data\Row Source' properties Access doesn't like 'TMP_MyformMycombo' should there be a '.' between it?
 
Thanks

Thanks Kelloggs. Well I eventually managed to get that set-up on the server and the combo box gets the infomation from the temp table. Tomorrow I will be able to check out if the ODBC error message disappears on the client. Finger crossed.

There is something that isnt clear to me.
 
You have created a MS Access Application.  
You have divided it into back-end database (Server DB) and front-end database (Client DB)
 
The back-end database (Server DB) has all the linked tables, queries, forms??
And the front-end database (Client DB) has what??
 
You cannt have a Client DB that links to a link
 
The Server DB should have all the tables (Not Linked Tables)
And the Client DB should have all the forms, queries, modules, and reports.
 
Kelloggs,