Access-SQL Server Connections

Romashka · May 23rd, 2007, 01:55 PM

We have an Access front-end, SQL Server back-end application that uses DAO library. The connection is also created using DAO. All users of the front-end connect to SQL svr as members of one pwrd protected group.

The problem with that is that multiple connections are created for every recordset for every user -- so having open 2-3 forms can cause about 10 simultaneous connections per user, and if two users are logged in then it would be about 20 connections just for 2-3 forms. We have 80 users for the application -- it will be unbearable for the server (and for our DBA staff) to handle about 500-1000 connections for one application only.

I am wondering is there a way to create a single global DAO connection for every user (1 connection -- 1 user) that would handle multiple recordset?
If this is not possible with DAO, can I create ADO global connection object and use it for my DAO recordsets?

I would appreciate any advice on this issue!
Thanks in advance.

mmcdonal · May 24th, 2007, 06:35 AM

When you say DAO connection, what do you mean? DAO doesn't work with SQL Server. Use ADO. DAO is for Jet, ADO is for multi platforms.

What code is making the connections? Are the tables all connected directly to the SQL Server using a DSN? If so, this is not the best design. You should have the users either do a local dump when they log in and close the connection then process transactions one at a time, or have them connect only when they need to per transaction.

What is it you are doing now?

mmcdonal

Romashka · May 25th, 2007, 12:34 AM

Thanks for your reply!

All tables were originally upsized to Sserver using DSN file, later we added a procedure that refreshes table connections on application start (so DSN is not used any longer). Here is the code that takes care of refreshing connections:

Code:

...
Set dbPUBS = CurrentDb
For Each tdfPUBS In dbPUBS.TableDefs
    ' Only attempt to refresh link on tables that already
    ' have a connect string (linked tables only)
    If Len(tdfPUBS.Connect) > 0 Then
        strTable = tdfPUBS.Name
        ' Set the tables connection string
        tdfPUBS.Connect = strConnect 'strConnect is built using data from INI file
        Me.Repaint
        tdfPUBS.RefreshLink
     End If
Next

Thanks!

mmcdonal · May 25th, 2007, 06:26 AM

I think you will have to move to ADO. I am not sure if there is an ADO global connection object. I have never had this sort of problem using ADO, so I think by switching you will eliminate this issue.

I am not sure how you got a DAO connection in the first place.

Anyway, DAO is for Jet only. ADO must be used for Access / SQL, or Access / anything else.

mmcdonal