Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 23rd, 2007, 01:55 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old May 24th, 2007, 06:35 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old May 25th, 2007, 12:34 AM
Registered User
 
Join Date: Feb 2007
Location: , , .
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!

Reply With Quote
  #4 (permalink)  
Old May 25th, 2007, 06:26 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:32 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.