ADP + 3 connections per user !!!, how to have one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

ADP + 3 connections per user !!!, how to have one

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2003-11-27 : 10:46:47

Does somebody know how to decrease the number of connections per user in an ADP project (Access 2000+SQL 2000)?
Each user is taking three connections. Is there anyway to take two of them out or not started?
Thanks, Paul

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-01 : 06:34:30

Hm.. very strange your demand is..

Think it's impossible - to get rid of those 'bad' connections..

Seems they serve to internal needs of AD project..

Mathias
Posting Yak Master

119 Posts

Posted - 2003-12-01 : 09:38:08

I have about 40 users and they take 120 connections where 80 are not really used but just taken memory on the server. While I would agree on the ADP because it is uncompiled, the users are using compiled version ADE where the database window is hidden (-> this means, no need to send a SP_ procedures to know the server tables or procedures). Have somebody found a way of killing them (if we can not avoid the generation)? or this will break the ADE project?
Thanks, Paul

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-01 : 15:07:47

I have checked it. Really a bit strangely it is.

1. I open an adp (tab Modules). No any ms access connection in Profiler so far.
2. Go to the Tables tab. And in Profiler I see an open Connection with SPID=10.
3. Now I open and close a table. In Profiler I see a new (second) Connection
with SPID=11. And this new Connection does not break after closing the table.

4. OK, thinking that now this second Connection is absolutely needless I kill
the process with SPID=11 (in QA >> kill 11). And in Profiler I see Disconnect
of the second Connection (but note: the first Connection is still 'alive')....

5. Then in a VBA module I write:

Dim cn As ADODB.Connection, rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = cn.Execute("select * from books")
rs.MoveLast

But OOPS!! It does not work!! The first Connection turned to be broken too!!

Mathias
Posting Yak Master

119 Posts

Posted - 2003-12-02 : 03:27:00

I have moved one step further

1/Create the following procedure
create procedure usp_ADP_list_Access_conn
/*list all connections for the current user that are
are started from Access*/
as

set nocount off
select
spid, last_batch,lastwaittype, status, program_name,cmd, loginame from master.dbo.sysprocesses
where
program_name='Microsoft® Access' and
loginame=SYSTEM_USER
order by spid

GRANT EXECUTE ON [dbo].[usp_ADP_list_Access_conn] TO [Everyone]
GO

2/In access create the following sub

Sub KillExtraConnection()
If Right(CurrentProject.name, 3) <> "adp" Then
Dim i As Integer
Dim k As Integer
Dim cn As ADODB.Connection, rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = cn.Execute("select @@SPID")
i = rs.Fields(0)
Set rs = cn.Execute("exec dbo.usp_ADP_list_Access_conn")

While Not rs.EOF
k = rs.Fields("spid")
If k <> i Then
cn.Execute ("kill " & k)
Debug.Print k & " has been killed"
End If
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set cn = Nothing
End If
End Sub

3/ It does work, the tables, views ... are not seeable anymore and I have only one connection left, BUT you need sysadmin right to use KILL, so I can not deploy it, any alternative?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-02 : 06:00:19

Really, I can't understand this... Why do you fight ADP / ADE features

instead of developing your application in e.g. VB with full control

over those connections?

Mathias
Posting Yak Master

119 Posts

Posted - 2003-12-03 : 02:53:43

Simply because I don't have the VB development kit. It is also a very basic question.
You define a connection but the program creates 3 connections and only one is associated with the one specified in the ADP project.
I haven't found so far any documentation on the purpose of the two extra connections (outside of here) and I would like to understand the logic behind it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources