Keep odbc connection alive

[Gummie]

How can i keep my odbc connection alive?
Because when ia quey has run, the connection is lost after ? seconds. So every time i run that query the connection must be made. How can i prevent that.

Gummie

 

[crosmill]

To what and how are you connecting using odbc?

 

[Gummie]

I'm connecting to Navision Financinals. With the original Navision ODBC drivers.

 

[crosmill]

I'm connecting to Navision Financinals.

Which is what? Access/SQL Server/Oracle/Sybase.....

 

[Gummie]

Which is what? Access/SQL Server/Oracle/Sybase.....

Access 2000

 

[crosmill]

your making this hard for me. The more information you can give me the better.

so you back end is A2K, whats you front end.

 

[Gummie]

I'm sorry, my front end is also access 2000.
And the front end runs on win2k

 

[crosmill]

OK how are you linking the databases, do you have a linked table setup in your front end.......?

 

[Pat Hartman]

I am curious as to why you want to keep the ODBC connection open permanently. Most systems are designed to open and close the connections as rapidly as possible. The monthly charges for some databases are based on the number of active threads. So if you have 10 users with permanent connections you have a minimum of 10 active threads (usually more). Whereas, you might never have more than 5 people concurrently accessing tables so if you closed threads when they were finished, you would never have 10 active threads.

 

[Gummie]

I think that i do not really understand what you mean.

I've linked the tables from my back end with my front end.
And i've linked the Navision tables with my back end.

Wait i shall refraise my question.
How can i keep my odbc connection with the Navision tables alive. This to prevent that every time i want data from these table i have to make a new connection.
I''ve linked these tables from Navision with my front end.

 

[Pat Hartman]

I think what you are saying is - my Navision tables are linked to my back end db. Why when I open the front end db ami I not able to see them to link to them -

The answer is, you can't daisy chain links. The link is only between two databases. So, if you want to see the Navision tables in the fe, you will need to link to them from the fe. That way your fe will link to some Access tables (I presume) and some Navision tables. Your be should not need to link to the Navision tables because there should be no code running there that requires those tables.

 

[Gummie]

I am curious as to why you want to keep the ODBC connection open permanently. Most systems are designed to open and close the connections as rapidly as possible. The monthly charges for some databases are based on the number of active threads. So if you have 10 users with permanent connections you have a minimum of 10 active threads (usually more). Whereas, you might never have more than 5 people concurrently accessing tables so if you closed threads when they were finished, you would never have 10 active threads.

I complete agree. But this is now the main problem. I shall explain.
For example. We have 10 licenses for Navision. And sometimes 11 or more people want to log in Navision.
If you logged in Navision you stay logged in until you logged out.
In my case its necessary that the dbase of Access always access has to the linked tables of Navision.
So when the connection with Navision is lost and another one loged in Navision, and we have reached the 10 connections, then the dbase of access is unable to connect to the tables of Navision.
And this most be prevent.

 

[Pat Hartman]

I don't know if this will work but you can try. Create a dummy table in the Navision db and add a record to it. Create a query based on that table. Create a form with that query as its recordsource. Open this form and hide it as part of your start up form. I think as long as this hidden form is open, your connection will stay live. If that doesn't work, you might need to add a timer event that requeries the form every 60 seconds. There might also be a parameter in the connection string that you can use to keep the connection live. You won't find this information in Access, you'll need the documentation for the ODBC driver that you are using. I do know that you actually need to open a query or form/report to establish a connection. Just opening the database will not make the connection.

There is something called the ODBC Connection String Builder. This builder is available when you are building a pass-through query. Start a new query, change the query type to pass-through. Open the properties dialog. Click on the builder button at the right of the connection field. I don't have access to an ODBC source at the moment so I can't go any farther with this description.

 

[Gummie]

I don't know if this will work but you can try. Create a dummy table in the Navision db and add a record to it. Create a query based on that table. Create a form with that query as its recordsource. Open this form and hide it as part of your start up form. I think as long as this hidden form is open, your connection will stay live. If that doesn't work, you might need to add a timer event that requeries the form every 60 seconds.

Thankx. That was the solution.

 

[Pat Hartman]

Thank you for reporting in. It is useful for people searching the archives to know when a solution like mine actually works.