Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 23rd, 2007, 01:55 PM
Registered User
 
Join Date: Feb 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access-SQL Server Connections

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.
 
Old May 24th, 2007, 06:35 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old May 25th, 2007, 12:34 AM
Registered User
 
Join Date: Feb 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old May 25th, 2007, 06:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Connections in a Sql Server project mlw C# 2 September 15th, 2008 09:32 AM
MS Access/Go Daddy Connections jroxit Classic ASP Basics 1 September 18th, 2006 12:32 PM
Wacky ASP Connections to SQL Server-PROBLEM SOLVED buddyz Classic ASP Databases 13 August 24th, 2006 08:29 AM
MS Access database connections proud900 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 June 22nd, 2005 08:21 PM
ASP and access connections ittorget Classic ASP Databases 0 February 18th, 2004 12:43 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.