Multiple connections from ADP to SQL Server with OpenConnection

[Guest]

I have an ADP project with a back end SQL Server. I'm using the following
code to connect to the database. But this will only allow me one user to
connect using the front end ADP. Once the first user has logged on a second
user cannot seem to connect as it can't open anotehr connection. Does anyone
know how I can overcome this or is there a way of specifying/allowing
multiple connections?

Application.CurrentProject.OpenConnection _
"Provider=SQLOLEDB.1;" _
+ "Persist Security Info=false;" _
+ "Initial Catalog=" & strDatabase & ";" _
+ "Data Source=" & strServer & ";" _
+ "Integrated Security=SSPI;"

 

[Sylvain Lafontaine]

Each user should have his/her own copy of the ADP file.

 

[Guest]

Hi,

Thanks - This is what I thought, however, my client wants the ADP deployed
using Citrix for about 50 users. The ADP is approx 25 mb which is a great
overhead on the network (more HD space and more RAM) - is there anyway around
this or is it a hard and fast rule that ADP should always be deployed - 1
for each user?

 

[Norman Yuan]

When each user has his own copy of ADP:

More HD space, yes. More memory when run? no. The memory usage would be the
same whether many users load the same copy of ADP FILE or load it from its
own copy. MS Access runs in each user's context, they are not sharing a
running MS Access session.

HD space is quite cheap these days. 30MBx300 users is less than 10GB, which
is very small volume on a server.

However, I do not see your problem is caused by sharing ADP file. You need
to look into the back end SQL Server database, say using Profiler to trace
the data access to the SQL Server.

 

[Sylvain Lafontaine]

The connection problem is caused by the sharing file because changing the
connection put it into design mode and only one person at a time can open an
ADP file in design mode (like it is for MDB files since the 2000 version).

 

[Guest]

Thanks for all this information.

I ran a trace to see exactly where my problem is and it is when it actually
tries to connect using the 'Application.CurrentProject.OpenConnection'. Can I
assume that SQL server knows that there is already a connection from this
instance of the ADP and therefore will not allow another connection to be
created from the current ADP. If this is the case then I would indeed have to
make and distribute multiple copies of the front end ADP.

Alternatively is there any way I can create another connection from the Same
ADP to have more than one connection by altering my connection code?

Application.CurrentProject.OpenConnection _
"Provider=SQLOLEDB.1;" _
+ "Persist Security Info=false;" _
+ "Initial Catalog=" & strDatabase & ";" _
+ "Data Source=" & strServer & ";" _
+ "Integrated Security=SSPI;"

 

[Sylvain Lafontaine]

You are using Integrated Security, so it shouldn't be a problem to simply
remove the call to .OpenConnection.

However, I cannot tell you if you will have or not other problems on other
sections of your project.