Managing connections to attached tables

[webdb_advisor]

What is the "best practice pattern" for executing in-line SQL code
against attached tables ?

Especially ... Managing connections to those attached tables ?


Architecture is multi-user VB App connecting to SQL Serve via tables
linked to MDB ... so the Connections are to the Access Database.

We have REPLACED legacy MDB TABLES by "upsizing" to SQL Server.

Now ... tables with identical names to SQL Server are linked to the MDB
that is the data source for our VB client.

There is NO ACCESS CLIENT ... we are migrating from VB/MDB app to SQL
server so the SQL in VB uses Jet/Access Syntax

I'm concerned about the consequences of mainaining a global DAO
connection to the MDB .... I'm sure that that practice has impacted
performance of the multi-user MDB application going against Access
tables, but now that we are replacing those tables with links to SQL
server ...

My client-server code usually open connections immediately before the
database call and close them when the result set is returned to the
client.

It will be a lot of work to clean up all the leacy queries to do that.


How much downside is there to maintaining that persistent connection
?????

Are there any "helper funcitons" available .. similar to the Nile
Database "ado API" that are available to encapsulate connection
managment ?? (If I could just pass the SQL Query String to a "well
behaved" set of DAO code that returns the result set that would be
great !)

Steve

 

[Alex Dybenko]

Hi,
Best way to get a read-only data - is to use pass-through queries. in this
case you have to build connection string based on ODBC connection string.

for editable foms - you can just use attached table

 

[webdb_advisor]

Alex -

This is a VB application that is being migrated from MDB to SQL Server
..... the main issue that concerns me is instantiating and setting the
connection object to NOTHING .... vs keeping a persistent global
connection open during the life of the application.

Steve

 

[Alex Dybenko]

Hi,
then it depends on your application architecture. If you have some grid
control, bound to a table - then you need a persistent connection for time
your form is opened. but in most cases - the best approach is to have
connection closed as soon as you get or update data