Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 November 16th, 2005, 09:09 AM
Registered User
 
Join Date: Feb 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Active Connections to SQLServer database

I'm developing an Access application which, when deployed, could have 10-15 instances of the executable running at any given time. What factors determine how many active connections are possible and/or practical against a single SQL source.

The application calls for the user to either sign on or out and then choose from a few options which will result in either the creation or update of 1 to 3 records in a single table. (There are assorted other tables for administrative purposes, but the bulk of the traffic will be against one table.)

In the application, prior to each database operation, I create a connection object, perform the operation, and then close the connection. By virtue of the application being linked to the SQL database, is there already a persistent connection? If so, then would there be 2 connections active whenever a database operation is performed?

Any info would be appreciated.
Thanks :)

 
Old November 21st, 2005, 10:01 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

From what I gather from your post, you have tables linked to the SQL backend? This won't cause locking problems with your other transactions.

When you create your connections, you can reference the DSN you are using for the links.

Anyway, SQL should not have a problem with this. That's probably why you are using SQL, cause Access had a problem with it.

Have you run into any problems?


mmcdonal
 
Old November 21st, 2005, 11:35 AM
Registered User
 
Join Date: Feb 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I haven't deployed the application yet. I'm linking to a SQL database for stability and security reasons. What I'm trying to understand is how to best manage connections so as to avoid possible locking conflicts between multiple users. I'm not real clear how connections are handled by Access.

Since my original post, I've further defined my application. I have 3 listboxes (list1, list2, list3) on one form. List1 is pre-filled with codes from a SQL table (table1) at form_load. The user selects a code from list1 which fills list2 with associated codes from a second SQL table (table2). The user selects a code from list2 which fills list3 with records from a third SQL table (table3) based on the values selected in list1 and list2. List1 and list2 don't need to be updateable, but list3 does.

QUESTIONS
1) Does Access maintain a persistent connection with SQLServer the whole time it's running? If so, can I use this connection for my data calls?

2)How many connections are possible/practical before response times are noticeably affected?

3) When retrieving the data to populate list1 and list2, I'm creating a static connection with batchoptimistic locking since updating isn't necessary. To populate list3, I'm creating a dynamic connection with pessimistic locking, since updating is necessary. Does this approach make sense, or am I completely misunderstanding these concepts?

 
Old November 21st, 2005, 12:50 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would suggest the Guru's guides to SQL Server.

1. When you link tables to SQL, you are telling Access HOW to get to the SQL server using a system DSN. Do you know what this is? This is just connection information that Access uses ONLY when it needs to do something with the server.

This connection information can be used by any application that wants to get to the SQL server. It is just information for apps to use, not actually a connection. DSN's are a little slower than writing the code yourself, but they are rock solid.

So you can use the SQL server using this DSN, or any other method, simultaneously with the Access linked tables.

2. I would say in your application, that you will never notice a speed change with SQL access, regardless of how many users you will throw at it in your environement. You will notice a HUGE speed increase over the jet engine, though.

3. I would not use batchoptimistic since this is for batch updates, not list population. But since your tables are linked, just do a look up to the linked tables, and let Access and SQL manage the connections. Normally you might rely on a view on the server, since this will be optimized and much faster, but your app isn't large enough to notice a difference, it sounds like.

You could use an optimistic lock on list3 and allow SQL to manage the transaction. I don't think you need to do anything more than the default cursors and locks with this small of an app.

See the Wrox books on programming Access 2003, and SQL server on these topics. As long as your users have the proper rights on the server, the links should do everything you need and transactions shouldn't be a problem.

mmcdonal
 
Old November 21st, 2005, 12:57 PM
Registered User
 
Join Date: Feb 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, I appreciate your input. I've been reading up on connections and the whole ADO model, and what you say makes sense. What I didn't realize was that SQLServer automatically creates and manages transactions to avoid collisions and ensure data integrity.

Thanks again! :)

 
Old November 21st, 2005, 01:29 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oh golly, it sure does. How do you think you can get 1,000's of online transactions going at once. Your app won't even tickle it. I know you will like it much better than Jet. BTW, we will all have to go that way soon anyway. MS is dumbing down Access, and integrating VS/SQL like Access/VBA. So we will know more than the SP/Trigger SQL folks and should be in greater demand as people upsize.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
List out all the SQLServer is currently active shiva10 C# 0 May 26th, 2006 06:39 AM
Database - connections gkn Oracle 1 December 20th, 2005 08:25 AM
Database - connections gkn Oracle 0 December 20th, 2005 07:27 AM
multiple database connections markhardiman Classic ASP Databases 7 September 9th, 2004 05:15 AM





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