SQL Server open connections

[katoc]

Hello.

I have an MS Access Database that contains linked tables from a SQL Server ODBC connection. 16 users have a copy of this database on their machines.

1. When the first form is loaded, a query runs (select * from Userlist where username = login). After I get the username, I close the connection. Code below.

Set rsUser = CurrentDb().OpenRecordset("select * from UserList where username = '" & user & "'")
usrType = rsUser!userType
rsUser.Connection.Close
rsUser = Nothing

However, on the SQL Server, the process doesn’t go away and it shows as ‘sleeping’ and ‘runnable’

2. My second problem is this. I have a table “DATA” containing about 700K rows. The user selects some search criteria and hits “Show DATA Records”. This opens a form with list of records that are filtered according to the user selections. In the SQL Server, this query shows are runnable and doesn’t close. The connection stays open. I changed the MS Access form to ‘Snapshot’ instead of ‘Dynaset’ and that didn’t seem to help. Are there any suggestions?

With 16 users, we have 16+ open connections slowing down the system. I’m new to using Access as a frontend to SQL Server, so any help will be greatly appreciated.

Thanks.

Sofia

 

[PHV]

you may try this instead:
rsUser.Close
Set rsUser = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[katoc]

I already made that change and still get the same behavior.

 

[cmmrfrds]

Try switching to ADO instead on using a DAO connection.

Example.

Public Function ReturnUser() As String

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString

'--- connect to sql server
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sql1 = " Select suser_sname() "
Set rs.ActiveConnection = cn
rs.Open sql1, cn, adOpenKeyset, adLockReadOnly
Debug.Print "return = "; rs(0)

ReturnUser = rs(0)
rs.Close
Set rs = Nothing
End Function

 

[katoc]

What does this do exactly? I need to explain it to our PM

 

[cmmrfrds]

I recommend going to the Carl Porthman site and reading about the various ways you can connect to sql server.

http://www.carlprothman.net/Default.aspx?tabid=81

In your example it looks like you are using a default DAO connection trhough ODBC. If you could close this I don't think you would want to. Use a direct ADO connection that you open and close yourself. Avoid using an ADO connection that also goes through ODBC, which you can read about. Post back if you still have questions after going through the documentation in the suggested site. If you are doing something different than it appears then post all the code showing how you made the connection.

 

[katoc]

In this database, we have 16 users with a copy of the .mde file using an ODBC connection defined on their individual machines. The database includes forms that make select calls to the SQL Server tables for reviewing data. One table is used for review, the other 2 tables are used to enter new records. Currently, I'm using a DAO connection to open recordsets and based on people's responses here, I'm researching ADO connections.

What is the best practice when making SQL Server select calls? Should I open an ADO connection when the form loads and then close it once the data is displayed to free the records? If so, how do I free those records? I set the RecordSet Type property to Snapshot in the Form properties, but it is not preventing the ODBC timeouts.

 

[cmmrfrds]

In your first post, 16 connections are not going to slow down the system, but if you have 16 large resultsets sitting on sql server bound to Form(s) that could slow down the system.

First, make sure no Forms are bound to a table. Make all forms bound to queries that return small resultsets.

Linked sql server tables are going to need to use ODBC and ODBC has a timeout property - set that as needed.

I would not use an individual ODBC dsn on each PC, it is too much maintenance (too much to go wrong). Instead use a DSN-Less ODBC connection. The Prothman site explains these.

 

[katoc]

Okay, here's the situation.

I have one linked table 'FOLDERS' that has 700,000 rows.
I have another linked table 'DOCS' that has 2,000 rows.

There is an ODBC connection defined in my System DSN. All recordsets are created using DAO

I have a form 'Search Folders', which is unbound and it has 4 drop-down lists. Each dropdown list has a sql statement bound to it:
"SELECT Distinct Name from FOLDERS"
"SELECT DISTINCT Department FROM FOLDERS" etc...

The user selects a value in each dropdown if he wants and the combination is stored in a string:
criteria = "Name = '" & nameFld.value & "' and Department = '" & deptFld.value & "'

This string is sent to the form 'List of Folders' in the form of:
DoCmd.OpenForm "List of Folders", , , criteria

"List of Folders" recordsource is:
SELECT * FROM FOLDERS;

So when criteria is sent with the OpenForm command, it filters the results by 'criteria'.

Now when you have 16 users hitting the same form, it may be the reason for the lockup. I checked the SQL Query Analyzer and noticed some of these Select statements are still in the process list as 'sleeping'.

If I were to use ADO, will it help free up process space if I was able to connect and disconnect? If so, how do I use ADO when setting up the recordsource for the form or the dropdown lists?

 

[cmmrfrds]

Change the 3 bound "select statement" to pass-thru queries, that should make a significant difference. I will be surprised if this does not clear up the issues you have been experiencing.

 

[katoc]

I changed the recordset connection of table containing 700,000 records from DAO to ADO and it's working much better now. I open the ADO connection to retrieve data and then break it once it's displayed. The users only use this data for reference. They don't add/edit/delete.

I'll have to do more research on ADO so I can update other query calls.

Thanks for all the help!