Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decid
e
> sql server connection strategy. The application uses by 15-20 Windows clie
nt
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is sh
ut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:[vbcol=seagreen]
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the sing
le
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SADIRODLU" wrote:
>
Thanks John for all answers.

Connections

Hi Folks,
I have a question. I've developed a desktop application. But i can't decide
sql server connection strategy. The application uses by 15-20 Windows client
and it connects to same sql server.
What am i do ?
1- I can open connection start of the program and close when program is shut
down.
2- I can open connection when i need a connection and i can close
connection when i executed sql statements.
Do you have any idea?
Thanks.Hi
In general two-tier applications tend to use a single connection for the
application runtime, web applications tend to use connection pooling and
close the connection. Your application seems to be best suited to the single
connection strategy. See my other reply for some links about these.
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I have a question. I've developed a desktop application. But i can't decide
> sql server connection strategy. The application uses by 15-20 Windows client
> and it connects to same sql server.
> What am i do ?
> 1- I can open connection start of the program and close when program is shut
> down.
> 2- I can open connection when i need a connection and i can close
> connection when i executed sql statements.
> Do you have any idea?
> Thanks.
>
>|||John Bell wrote:
> Hi
> In general two-tier applications tend to use a single connection for the
> application runtime, web applications tend to use connection pooling and
> close the connection. Your application seems to be best suited to the single
> connection strategy. See my other reply for some links about these.
> John
> "Erencan SAÃ?IROÃ?LU" wrote:
>> Hi Folks,
>> I have a question. I've developed a desktop application. But i can't decide
>> sql server connection strategy. The application uses by 15-20 Windows client
>> and it connects to same sql server.
>> What am i do ?
>> 1- I can open connection start of the program and close when program is shut
>> down.
>> 2- I can open connection when i need a connection and i can close
>> connection when i executed sql statements.
>> Do you have any idea?
>> Thanks.
>>
Thanks John for all answers. :)

Connection problem from Access front end application to SQL Desktop Engine backend

Hi there,

I sincerely hope that someone out there can help. I have two
instances of the SQL 2000 Desktop Engine running. One is on my local
machine for development and the other is on another machine on our
network which is the production environment. I have built an Access
2003 front end application which connects to this database. This
works fine locally, as you would expect. I successfully installed the
database on the production machine and am able to connect to it via
Access 2003 (using the Data Link Properties window) and from third
party database manager software (similar to Enterprise Manager). I am
not able to to connect to the database via my application.

I am using the "sa" account with a strong password. This is my
connection string:

strConnection = "Provider=sqloledb;DataSource=server02;User
Id=sa;Password=strong;Initial Catalog=Test"

The error I'm getting is:

"Connection cannot be used to perform this operation. It is either
closed or invalid in this context."

The connection string is the only thing that changes in my code when I
switch from my local to my production database. Is there some reason
that I can't use the "sa" account in this fashion that I'm not aware
of? I'd rather not use integrated security for simplicity's sake as
this is a small, internal application. Also, I would have thought
that if that was the issue, I couldn't use "sa" at all, even locally.

I'm going to post to the Access group as well but thought someone here
might have some advice to offer as well.

Thanks,

BarbHi

You may want to try connecting from your client using osql. It is not clear
if the manager software is on your client. If not then there seems to be a
network problem. Did you install it with the DISABLENETWORKPROTOCOLS
option?
http://support.microsoft.com/defaul...121120121120120
to enable them see
http://support.microsoft.com/defaul...kb;en-us;827204

John

"Barb" <funkybarb@.gmail.com> wrote in message
news:2ea50a4d.0504131151.43b8dbda@.posting.google.c om...
> Hi there,
> I sincerely hope that someone out there can help. I have two
> instances of the SQL 2000 Desktop Engine running. One is on my local
> machine for development and the other is on another machine on our
> network which is the production environment. I have built an Access
> 2003 front end application which connects to this database. This
> works fine locally, as you would expect. I successfully installed the
> database on the production machine and am able to connect to it via
> Access 2003 (using the Data Link Properties window) and from third
> party database manager software (similar to Enterprise Manager). I am
> not able to to connect to the database via my application.
> I am using the "sa" account with a strong password. This is my
> connection string:
> strConnection = "Provider=sqloledb;DataSource=server02;User
> Id=sa;Password=strong;Initial Catalog=Test"
> The error I'm getting is:
> "Connection cannot be used to perform this operation. It is either
> closed or invalid in this context."
> The connection string is the only thing that changes in my code when I
> switch from my local to my production database. Is there some reason
> that I can't use the "sa" account in this fashion that I'm not aware
> of? I'd rather not use integrated security for simplicity's sake as
> this is a small, internal application. Also, I would have thought
> that if that was the issue, I couldn't use "sa" at all, even locally.
> I'm going to post to the Access group as well but thought someone here
> might have some advice to offer as well.
> Thanks,
> Barb|||Hi John,

Thanks for your response. Since I posted my query, I did manage to
connect to my remote database using integrated security instead of the
"sa" account. I added my user account to a SQL server group that I
created and then gave that group login rights to the database. That
works fine from my development machine but not if I package up Access
with the Access runtime and try to run on another machine that doesn't
have Access 2003 installed. So, I've solved one problem but now have
another. I've posted again to the Access group to see if anyone can
help me there.

Barb

John Bell wrote:
> Hi
> You may want to try connecting from your client using osql. It is not
clear
> if the manager software is on your client. If not then there seems to
be a
> network problem. Did you install it with the DISABLENETWORKPROTOCOLS
> option?
http://support.microsoft.com/defaul...121120121120120

> to enable them see
> http://support.microsoft.com/defaul...kb;en-us;827204
> John
>
> "Barb" <funkybarb@.gmail.com> wrote in message
> news:2ea50a4d.0504131151.43b8dbda@.posting.google.c om...
> > Hi there,
> > I sincerely hope that someone out there can help. I have two
> > instances of the SQL 2000 Desktop Engine running. One is on my
local
> > machine for development and the other is on another machine on our
> > network which is the production environment. I have built an
Access
> > 2003 front end application which connects to this database. This
> > works fine locally, as you would expect. I successfully installed
the
> > database on the production machine and am able to connect to it via
> > Access 2003 (using the Data Link Properties window) and from third
> > party database manager software (similar to Enterprise Manager). I
am
> > not able to to connect to the database via my application.
> > I am using the "sa" account with a strong password. This is my
> > connection string:
> > strConnection = "Provider=sqloledb;DataSource=server02;User
> > Id=sa;Password=strong;Initial Catalog=Test"
> > The error I'm getting is:
> > "Connection cannot be used to perform this operation. It is either
> > closed or invalid in this context."
> > The connection string is the only thing that changes in my code
when I
> > switch from my local to my production database. Is there some
reason
> > that I can't use the "sa" account in this fashion that I'm not
aware
> > of? I'd rather not use integrated security for simplicity's sake
as
> > this is a small, internal application. Also, I would have thought
> > that if that was the issue, I couldn't use "sa" at all, even
locally.
> > I'm going to post to the Access group as well but thought someone
here
> > might have some advice to offer as well.
> > Thanks,
> > Barb