Connection pooling question

[fniles]

I am using VS2003 and connecting to MS Access database.
When using a connection pooling (every time I open the OLEDBCONNECTION I use
the exact matching connection string),
1. how can I know how many connection has been used ?
2. If the maximum pool size has been reached, what happens when I call the
method Open to open the connection ? Will I get an error ? MSDN says the
request is queued, but will I get an error in the open method ?

ConnectionDemoO LE = New OleDb.OleDbConn ection
sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath & ";OLE DB
Services=-1"
With ConnectionDemoO LE
.ConnectionStri ng = sPath
.Open() --what happens here when the maximum pool size has been
reached ?

Thanks

[William \(Bill\) Vaughn]

Re: Connection pooling question

I don't think there are any system counters exposed by the JET provider or
by OLE DB to monitor the CP.
If you exhaust the pool you should get a timeout exception.
However, (and Ginny please correct me here), if you're working with JET and
an ASP application, you've got your wires crossed. JET is not designed to
provide data for more than one user. Sure, you can share a JET .MDB database
over a LAN, but each user gets its own JET engine to access the file. Using
it in a web application that requires one JET engine to access the data is
problematic at best. I suggest using a DBMS designed for the web--SQL
Express.

hth

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ejDHZzGgHH A.4368@TK2MSFTN GP03.phx.gbl...

>I am using VS2003 and connecting to MS Access database.
When using a connection pooling (every time I open the OLEDBCONNECTION I
use the exact matching connection string),
1. how can I know how many connection has been used ?
2. If the maximum pool size has been reached, what happens when I call the
method Open to open the connection ? Will I get an error ? MSDN says the
request is queued, but will I get an error in the open method ?
>
ConnectionDemoO LE = New OleDb.OleDbConn ection
sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath & ";OLE
DB
Services=-1"
With ConnectionDemoO LE
.ConnectionStri ng = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
>
Thanks
>
>

[fniles]

Re: Connection pooling question

I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
Access.
In SQL Server, in the connection string you can set the max pool size, can
you do that in MS Access ?

You mentioned that "If you exhaust the pool you should get a timeout
exception." So, you will get an error, right ?
I mean if you put try-catch-end try, it will go to the Cath section, right ?
In my other posting I posted that sometimes I get "Unspecifie d error" on the
Open method when opening up the OleDBConnection . This does not happen all
the time, only sometimes, which makes me think that maybe the maximum pool
size has been reached ? Is it possible that the "Unspecifie d error" on the
Open method caused by the maximum pool size has been reached ?

Dim swError As StreamWriter
Dim sSub As String
Try
sSub = "1"
ConnectionOLE = New OleDb.OleDbConn ection
OpenDBOLE = True
With ConnectionOLE
.ConnectionStri ng = g_dbPath
sSub = "2"
.Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
TO THE Catch section below ?
sSub = "3"
End With
Catch ex As Exception
swError = New StreamWriter(Ap plication.Start upPath &
"\AQErrorLo g" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
swError.Write(N ow & " OpenDBOLE - error = " & ex.Message & "
sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try

Thank you.



"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..

>I don't think there are any system counters exposed by the JET provider or
>by OLE DB to monitor the CP.
If you exhaust the pool you should get a timeout exception.
However, (and Ginny please correct me here), if you're working with JET
and an ASP application, you've got your wires crossed. JET is not designed
to provide data for more than one user. Sure, you can share a JET .MDB
database over a LAN, but each user gets its own JET engine to access the
file. Using it in a web application that requires one JET engine to access
the data is problematic at best. I suggest using a DBMS designed for the
web--SQL Express.
>
hth
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ejDHZzGgHH A.4368@TK2MSFTN GP03.phx.gbl...

>>I am using VS2003 and connecting to MS Access database.
>When using a connection pooling (every time I open the OLEDBCONNECTION I
>use the exact matching connection string),
>1. how can I know how many connection has been used ?
>2. If the maximum pool size has been reached, what happens when I call
>the method Open to open the connection ? Will I get an error ? MSDN says
>the request is queued, but will I get an error in the open method ?
>>
>ConnectionDemo OLE = New OleDb.OleDbConn ection
>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath & ";OLE
>DB
>Services=-1"
>With ConnectionDemoO LE
> .ConnectionStri ng = sPath
> .Open() --what happens here when the maximum pool size has been
>reached ?
>>
>Thanks
>>
>>

>
>

[William \(Bill\) Vaughn]

Re: Connection pooling question

See >>>>>

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:%23uACzGHg HHA.4260@TK2MSF TNGP03.phx.gbl. ..

>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>Access.
In SQL Server, in the connection string you can set the max pool size, can
you do that in MS Access ?

>>>>Nope.

>
You mentioned that "If you exhaust the pool you should get a timeout
exception." So, you will get an error, right ?
I mean if you put try-catch-end try, it will go to the Cath section, right
?

>>>>Right.

In my other posting I posted that sometimes I get "Unspecifie d error" on
the Open method when opening up the OleDBConnection . This does not happen
all the time, only sometimes, which makes me think that maybe the maximum
pool size has been reached ? Is it possible that the "Unspecifie d error"
on the Open method caused by the maximum pool size has been reached ?

>>>"Unspecified " errors generally means you don't have a Try/Catch block
>>>to trap the specific exception. In Access/JET, yes it might mean the
>>>pool is full or the database is corrupt, or almost anything else.

>
Dim swError As StreamWriter
Dim sSub As String
Try
sSub = "1"
ConnectionOLE = New OleDb.OleDbConn ection
OpenDBOLE = True
With ConnectionOLE
.ConnectionStri ng = g_dbPath
sSub = "2"
.Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
TO THE Catch section below ?

>>In theory... yes.

sSub = "3"
End With
Catch ex As Exception
swError = New StreamWriter(Ap plication.Start upPath &
"\AQErrorLo g" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
swError.Write(N ow & " OpenDBOLE - error = " & ex.Message &
" sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
>
Thank you.
>
>
>
"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..

>>I don't think there are any system counters exposed by the JET provider or
>>by OLE DB to monitor the CP.
>If you exhaust the pool you should get a timeout exception.
>However, (and Ginny please correct me here), if you're working with JET
>and an ASP application, you've got your wires crossed. JET is not
>designed to provide data for more than one user. Sure, you can share a
>JET .MDB database over a LAN, but each user gets its own JET engine to
>access the file. Using it in a web application that requires one JET
>engine to access the data is problematic at best. I suggest using a DBMS
>designed for the web--SQL Express.
>>
>hth
>>
>--
>William (Bill) Vaughn
>Author, Mentor, Consultant
>Microsoft MVP
>INETA Speaker
>www.betav.com/blog/billva
>www.betav.com
>Please reply only to the newsgroup so that others can benefit.
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>______________ _______________ _____
>Visit www.hitchhikerguides.net to get more information on my latest
>books:
>Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
>Hitchhiker's Guide to SQL Server 2005 Compact Edition
>>
>-----------------------------------------------------------------------------------------------------------------------
>"fniles" <fniles@pfmail. comwrote in message
>news:ejDHZzGgH HA.4368@TK2MSFT NGP03.phx.gbl.. .

>>>I am using VS2003 and connecting to MS Access database.
>>When using a connection pooling (every time I open the OLEDBCONNECTION I
>>use the exact matching connection string),
>>1. how can I know how many connection has been used ?
>>2. If the maximum pool size has been reached, what happens when I call
>>the method Open to open the connection ? Will I get an error ? MSDN says
>>the request is queued, but will I get an error in the open method ?
>>>
>>ConnectionDem oOLE = New OleDb.OleDbConn ection
>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>";OLE DB
>>Services=-1"
>>With ConnectionDemoO LE
>> .ConnectionStri ng = sPath
>> .Open() --what happens here when the maximum pool size has been
>>reached ?
>>>
>>Thanks
>>>
>>>

>>
>>

>
>

[Cor Ligthert [MVP]]

Re: Connection pooling question

Bill,

Although I agree with your conclusion.

Using Access over the Web means to have an ASP or an ASPX application which
runs on a Server. (Mostly the same as the webapplication runs on). In fact
there is one user (ASPUSER), which uses the ASP or ASPX application.

What is not possible is using Access over the web by using by instance its
IP address as it is by real databaseservers is possible (I thought that this
possibility is removed in SQLExpress) .

I assume that you understand that there is not any connection pooling
problem at all. Probably you know this, however to be complete for others.
Concurrency problems stay because the webprogram is sending data to its
clients, which works in a 3 tier way using the browsers as non intelligent
workstations as we called them in past (VT100 etc).

Cor


"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.coms chreef in bericht
news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..

>I don't think there are any system counters exposed by the JET provider or
>by OLE DB to monitor the CP.
If you exhaust the pool you should get a timeout exception.
However, (and Ginny please correct me here), if you're working with JET
and an ASP application, you've got your wires crossed. JET is not designed
to provide data for more than one user. Sure, you can share a JET .MDB
database over a LAN, but each user gets its own JET engine to access the
file. Using it in a web application that requires one JET engine to access
the data is problematic at best. I suggest using a DBMS designed for the
web--SQL Express.
>
hth
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ejDHZzGgHH A.4368@TK2MSFTN GP03.phx.gbl...

>>I am using VS2003 and connecting to MS Access database.
>When using a connection pooling (every time I open the OLEDBCONNECTION I
>use the exact matching connection string),
>1. how can I know how many connection has been used ?
>2. If the maximum pool size has been reached, what happens when I call
>the method Open to open the connection ? Will I get an error ? MSDN says
>the request is queued, but will I get an error in the open method ?
>>
>ConnectionDemo OLE = New OleDb.OleDbConn ection
>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath & ";OLE
>DB
>Services=-1"
>With ConnectionDemoO LE
> .ConnectionStri ng = sPath
> .Open() --what happens here when the maximum pool size has been
>reached ?
>>
>Thanks
>>
>>

>
>

[fniles]

Re: Connection pooling question

Thank you.
I do use try/catch, but I get the "Unspecifie d error".

When the maximum pool size has been reached and I get an error, how can I
loop and wait until a connection is available again ?

Try
ConnectionOLE = New OleDb.OleDbConn ection
OpenDBOLE = True
With ConnectionOLE
.ConnectionStri ng = g_dbPath
.Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
to the Catch. How can I loop and wait to open the db until a connection is
available again ?
End With
Catch ex As Exception
Try

Thank you.

"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:OlI3%236Hg HHA.4804@TK2MSF TNGP02.phx.gbl. ..

See >>>>>
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:%23uACzGHg HHA.4260@TK2MSF TNGP03.phx.gbl. ..

>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>Access.
>In SQL Server, in the connection string you can set the max pool size,
>can you do that in MS Access ?

>

>>>>>Nope.

>

>>
>You mentioned that "If you exhaust the pool you should get a timeout
>exception." So, you will get an error, right ?
>I mean if you put try-catch-end try, it will go to the Cath section,
>right ?

>

>>>>>Right.

>

>In my other posting I posted that sometimes I get "Unspecifie d error" on
>the Open method when opening up the OleDBConnection . This does not happen
>all the time, only sometimes, which makes me think that maybe the maximum
>pool size has been reached ? Is it possible that the "Unspecifie d error"
>on the Open method caused by the maximum pool size has been reached ?

>

>>>>"Unspecifie d" errors generally means you don't have a Try/Catch block
>>>>to trap the specific exception. In Access/JET, yes it might mean the
>>>>pool is full or the database is corrupt, or almost anything else.

>
>

>>
> Dim swError As StreamWriter
> Dim sSub As String
> Try
> sSub = "1"
> ConnectionOLE = New OleDb.OleDbConn ection
> OpenDBOLE = True
> With ConnectionOLE
> .ConnectionStri ng = g_dbPath
> sSub = "2"
> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
>TO THE Catch section below ?

>

>>>In theory... yes.

>
>

> sSub = "3"
> End With
> Catch ex As Exception
> swError = New StreamWriter(Ap plication.Start upPath &
>"\AQErrorLog " & Date.Now.ToStri ng("MMddyy") & ".txt", True)
> swError.Write(N ow & " OpenDBOLE - error = " & ex.Message &
>" sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
> swError.Close()
> swError = Nothing
> End Try
>>
>Thank you.
>>
>>
>>
>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>news:u2%23Nf5G gHHA.3388@TK2MS FTNGP02.phx.gbl ...

>>>I don't think there are any system counters exposed by the JET provider
>>>or by OLE DB to monitor the CP.
>>If you exhaust the pool you should get a timeout exception.
>>However, (and Ginny please correct me here), if you're working with JET
>>and an ASP application, you've got your wires crossed. JET is not
>>designed to provide data for more than one user. Sure, you can share a
>>JET .MDB database over a LAN, but each user gets its own JET engine to
>>access the file. Using it in a web application that requires one JET
>>engine to access the data is problematic at best. I suggest using a DBMS
>>designed for the web--SQL Express.
>>>
>>hth
>>>
>>--
>>William (Bill) Vaughn
>>Author, Mentor, Consultant
>>Microsoft MVP
>>INETA Speaker
>>www.betav.com/blog/billva
>>www.betav.com
>>Please reply only to the newsgroup so that others can benefit.
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>_____________ _______________ ______
>>Visit www.hitchhikerguides.net to get more information on my latest
>>books:
>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>
>>-----------------------------------------------------------------------------------------------------------------------
>>"fniles" <fniles@pfmail. comwrote in message
>>news:ejDHZzGg HHA.4368@TK2MSF TNGP03.phx.gbl. ..
>>>>I am using VS2003 and connecting to MS Access database.
>>>When using a connection pooling (every time I open the OLEDBCONNECTION
>>>I use the exact matching connection string),
>>>1. how can I know how many connection has been used ?
>>>2. If the maximum pool size has been reached, what happens when I call
>>>the method Open to open the connection ? Will I get an error ? MSDN
>>>says the request is queued, but will I get an error in the open method
>>>?
>>>>
>>>ConnectionDe moOLE = New OleDb.OleDbConn ection
>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>";OLE DB
>>>Services=-1"
>>>With ConnectionDemoO LE
>>> .ConnectionStri ng = sPath
>>> .Open() --what happens here when the maximum pool size has been
>>>reached ?
>>>>
>>>Thanks
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[William \(Bill\) Vaughn]

Re: Connection pooling question

Ah, I doubt if this will help. It assumes that the engine has enough idle
time to do it's work. The fundamental issue is clear. If the cause of your
problem is the CP and the pool is filling then something is overloading the
engine or your code is not releasing/closing connections in a timely
fashion. Again, JET is not designed for this kind of work. I think you're
beating a dead horse.

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:OUmc0jKgHH A.2640@TK2MSFTN GP06.phx.gbl...

Thank you.
I do use try/catch, but I get the "Unspecifie d error".
>
When the maximum pool size has been reached and I get an error, how can I
loop and wait until a connection is available again ?
>
Try
ConnectionOLE = New OleDb.OleDbConn ection
OpenDBOLE = True
With ConnectionOLE
.ConnectionStri ng = g_dbPath
.Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
to the Catch. How can I loop and wait to open the db until a connection is
available again ?
End With
Catch ex As Exception
Try
>
Thank you.
>
"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:OlI3%236Hg HHA.4804@TK2MSF TNGP02.phx.gbl. ..

>See >>>>>
>>
>--
>William (Bill) Vaughn
>Author, Mentor, Consultant
>Microsoft MVP
>INETA Speaker
>www.betav.com/blog/billva
>www.betav.com
>Please reply only to the newsgroup so that others can benefit.
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>______________ _______________ _____
>Visit www.hitchhikerguides.net to get more information on my latest
>books:
>Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
>Hitchhiker's Guide to SQL Server 2005 Compact Edition
>>
>-----------------------------------------------------------------------------------------------------------------------
>"fniles" <fniles@pfmail. comwrote in message
>news:%23uACzGH gHHA.4260@TK2MS FTNGP03.phx.gbl ...

>>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>>Access.
>>In SQL Server, in the connection string you can set the max pool size,
>>can you do that in MS Access ?

>>

>>>>>>Nope.

>>

>>>
>>You mentioned that "If you exhaust the pool you should get a timeout
>>exception." So, you will get an error, right ?
>>I mean if you put try-catch-end try, it will go to the Cath section,
>>right ?

>>

>>>>>>Right.

>>

>>In my other posting I posted that sometimes I get "Unspecifie d error" on
>>the Open method when opening up the OleDBConnection . This does not
>>happen all the time, only sometimes, which makes me think that maybe the
>>maximum pool size has been reached ? Is it possible that the
>>"Unspecifie d error" on the Open method caused by the maximum pool size
>>has been reached ?

>>

>>>>>"Unspecifi ed" errors generally means you don't have a Try/Catch block
>>>>>to trap the specific exception. In Access/JET, yes it might mean the
>>>>>pool is full or the database is corrupt, or almost anything else.

>>
>>

>>>
>> Dim swError As StreamWriter
>> Dim sSub As String
>> Try
>> sSub = "1"
>> ConnectionOLE = New OleDb.OleDbConn ection
>> OpenDBOLE = True
>> With ConnectionOLE
>> .ConnectionStri ng = g_dbPath
>> sSub = "2"
>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>GO TO THE Catch section below ?

>>

>>>>In theory... yes.

>>
>>

>> sSub = "3"
>> End With
>> Catch ex As Exception
>> swError = New StreamWriter(Ap plication.Start upPath &
>>"\AQErrorLo g" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
>> swError.Write(N ow & " OpenDBOLE - error = " & ex.Message
>>& " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>> swError.Close()
>> swError = Nothing
>> End Try
>>>
>>Thank you.
>>>
>>>
>>>
>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>>news:u2%23Nf5 GgHHA.3388@TK2M SFTNGP02.phx.gb l...
>>>>I don't think there are any system counters exposed by the JET provider
>>>>or by OLE DB to monitor the CP.
>>>If you exhaust the pool you should get a timeout exception.
>>>However, (and Ginny please correct me here), if you're working with JET
>>>and an ASP application, you've got your wires crossed. JET is not
>>>designed to provide data for more than one user. Sure, you can share a
>>>JET .MDB database over a LAN, but each user gets its own JET engine to
>>>access the file. Using it in a web application that requires one JET
>>>engine to access the data is problematic at best. I suggest using a
>>>DBMS designed for the web--SQL Express.
>>>>
>>>hth
>>>>
>>>--
>>>William (Bill) Vaughn
>>>Author, Mentor, Consultant
>>>Microsoft MVP
>>>INETA Speaker
>>>www.betav.com/blog/billva
>>>www.betav.com
>>>Please reply only to the newsgroup so that others can benefit.
>>>This posting is provided "AS IS" with no warranties, and confers no
>>>rights.
>>>____________ _______________ _______
>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>books:
>>>Hitchhiker 's Guide to Visual Studio and SQL Server (7th Edition) and
>>>Hitchhiker 's Guide to SQL Server 2005 Compact Edition
>>>>
>>>-----------------------------------------------------------------------------------------------------------------------
>>>"fniles" <fniles@pfmail. comwrote in message
>>>news:ejDHZzG gHHA.4368@TK2MS FTNGP03.phx.gbl ...
>>>>>I am using VS2003 and connecting to MS Access database.
>>>>When using a connection pooling (every time I open the OLEDBCONNECTION
>>>>I use the exact matching connection string),
>>>>1. how can I know how many connection has been used ?
>>>>2. If the maximum pool size has been reached, what happens when I call
>>>>the method Open to open the connection ? Will I get an error ? MSDN
>>>>says the request is queued, but will I get an error in the open method
>>>>?
>>>>>
>>>>ConnectionD emoOLE = New OleDb.OleDbConn ection
>>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>>";OLE DB
>>>>Services=-1"
>>>>With ConnectionDemoO LE
>>>> .ConnectionStri ng = sPath
>>>> .Open() --what happens here when the maximum pool size has been
>>>>reached ?
>>>>>
>>>>Thanks
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[fniles]

Re: Connection pooling question

Thank you.

>If the cause of your problem is the CP

What did you mean by CP ?

>JET is not designed for this kind of work.

So, if I use SQL Server (and assuming I use the code like below, except
using SqlConnection instead of OLEDbConnection ), most likely I will not have
the problem where the pool is filling like in Access ? Is the maximum pool
size in Access smaller than in SQL Server (where the default is 100) ?

I close the connection right after I fill the dataset like shown below. Can
I close the connection faster then the way I do it ?
This code is called everytime somebody login to the application.

Dim cmd As New OleDb.OleDbComm and
Dim da As OleDb.OleDbData Adapter
Dim ds As DataSet
Dim ConnectionDemoO LE As OleDb.OleDbConn ection
With cmd
bDBSuccess = OpenDBDemoOLE(C onnectionDemoOL E)
If bDBSuccess Then
.Connection = ConnectionDemoO LE
.CommandText = sql
Try
da = New OleDb.OleDbData Adapter
ds = New DataSet
da.SelectComman d = cmd
da.Fill(ds)
CloseConDemoOLE (ConnectionDemo OLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE (ByRef ConnectionDemoO LE As OleDb.OleDbConn ection)
If Not ConnectionDemoO LE Is Nothing Then
ConnectionDemoO LE.Close()
ConnectionDemoO LE = Nothing
End If
End Sub

Function OpenDBDemoOLE(B yRef ConnectionDemoO LE As OleDb.OleDbConn ection)
As Boolean
Try
ConnectionDemoO LE = New OleDb.OleDbConn ection
OpenDBDemoOLE = True
With ConnectionDemoO LE
.ConnectionStri ng = g_dbPathDemo
.Open()
If .State = ConnectionState .Closed Then
CloseConDemoOLE (ConnectionDemo OLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try

"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:Oroa83QgHH A.3460@TK2MSFTN GP04.phx.gbl...

Ah, I doubt if this will help. It assumes that the engine has enough idle
time to do it's work. The fundamental issue is clear. If the cause of your
problem is the CP and the pool is filling then something is overloading
the engine or your code is not releasing/closing connections in a timely
fashion. Again, JET is not designed for this kind of work. I think you're
beating a dead horse.
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:OUmc0jKgHH A.2640@TK2MSFTN GP06.phx.gbl...

>Thank you.
>I do use try/catch, but I get the "Unspecifie d error".
>>
>When the maximum pool size has been reached and I get an error, how can I
>loop and wait until a connection is available again ?
>>
> Try
> ConnectionOLE = New OleDb.OleDbConn ection
> OpenDBOLE = True
> With ConnectionOLE
> .ConnectionStri ng = g_dbPath
> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL GO
>to the Catch. How can I loop and wait to open the db until a connection
>is available again ?
> End With
> Catch ex As Exception
> Try
>>
>Thank you.
>>
>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>news:OlI3%236H gHHA.4804@TK2MS FTNGP02.phx.gbl ...

>>See >>>>>
>>>
>>--
>>William (Bill) Vaughn
>>Author, Mentor, Consultant
>>Microsoft MVP
>>INETA Speaker
>>www.betav.com/blog/billva
>>www.betav.com
>>Please reply only to the newsgroup so that others can benefit.
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>_____________ _______________ ______
>>Visit www.hitchhikerguides.net to get more information on my latest
>>books:
>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>
>>-----------------------------------------------------------------------------------------------------------------------
>>"fniles" <fniles@pfmail. comwrote in message
>>news:%23uACzG HgHHA.4260@TK2M SFTNGP03.phx.gb l...
>>>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>>>Access.
>>>In SQL Server, in the connection string you can set the max pool size,
>>>can you do that in MS Access ?
>>>
>>>>>>>Nope.
>>>
>>>>
>>>You mentioned that "If you exhaust the pool you should get a timeout
>>>exception. " So, you will get an error, right ?
>>>I mean if you put try-catch-end try, it will go to the Cath section,
>>>right ?
>>>
>>>>>>>Right.
>>>
>>>In my other posting I posted that sometimes I get "Unspecifie d error"
>>>on the Open method when opening up the OleDBConnection . This does not
>>>happen all the time, only sometimes, which makes me think that maybe
>>>the maximum pool size has been reached ? Is it possible that the
>>>"Unspecifi ed error" on the Open method caused by the maximum pool size
>>>has been reached ?
>>>
>>>>>>"Unspecif ied" errors generally means you don't have a Try/Catch
>>>>>>block to trap the specific exception. In Access/JET, yes it might
>>>>>>mean the pool is full or the database is corrupt, or almost anything
>>>>>>else.
>>>
>>>
>>>>
>>> Dim swError As StreamWriter
>>> Dim sSub As String
>>> Try
>>> sSub = "1"
>>> ConnectionOLE = New OleDb.OleDbConn ection
>>> OpenDBOLE = True
>>> With ConnectionOLE
>>> .ConnectionStri ng = g_dbPath
>>> sSub = "2"
>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>>GO TO THE Catch section below ?
>>>
>>>>>In theory... yes.
>>>
>>>
>>> sSub = "3"
>>> End With
>>> Catch ex As Exception
>>> swError = New StreamWriter(Ap plication.Start upPath &
>>>"\AQErrorLog " & Date.Now.ToStri ng("MMddyy") & ".txt", True)
>>> swError.Write(N ow & " OpenDBOLE - error = " & ex.Message
>>>& " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>>> swError.Close()
>>> swError = Nothing
>>> End Try
>>>>
>>>Thank you.
>>>>
>>>>
>>>>
>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>>>news:u2%23Nf 5GgHHA.3388@TK2 MSFTNGP02.phx.g bl...
>>>>>I don't think there are any system counters exposed by the JET provider
>>>>>or by OLE DB to monitor the CP.
>>>>If you exhaust the pool you should get a timeout exception.
>>>>However, (and Ginny please correct me here), if you're working with
>>>>JET and an ASP application, you've got your wires crossed. JET is not
>>>>designed to provide data for more than one user. Sure, you can share a
>>>>JET .MDB database over a LAN, but each user gets its own JET engine to
>>>>access the file. Using it in a web application that requires one JET
>>>>engine to access the data is problematic at best. I suggest using a
>>>>DBMS designed for the web--SQL Express.
>>>>>
>>>>hth
>>>>>
>>>>--
>>>>William (Bill) Vaughn
>>>>Author, Mentor, Consultant
>>>>Microsoft MVP
>>>>INETA Speaker
>>>>www.betav.com/blog/billva
>>>>www.betav.com
>>>>Please reply only to the newsgroup so that others can benefit.
>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>rights.
>>>>___________ _______________ ________
>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>books:
>>>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>>>
>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>"fniles" <fniles@pfmail. comwrote in message
>>>>news:ejDHZz GgHHA.4368@TK2M SFTNGP03.phx.gb l...
>>>>>>I am using VS2003 and connecting to MS Access database.
>>>>>When using a connection pooling (every time I open the
>>>>>OLEDBCONNE CTION I use the exact matching connection string),
>>>>>1. how can I know how many connection has been used ?
>>>>>2. If the maximum pool size has been reached, what happens when I
>>>>>call the method Open to open the connection ? Will I get an error ?
>>>>>MSDN says the request is queued, but will I get an error in the open
>>>>>method ?
>>>>>>
>>>>>Connection DemoOLE = New OleDb.OleDbConn ection
>>>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>>>";OLE DB
>>>>>Services =-1"
>>>>>With ConnectionDemoO LE
>>>>> .ConnectionStri ng = sPath
>>>>> .Open() --what happens here when the maximum pool size has been
>>>>>reached ?
>>>>>>
>>>>>Thanks
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[William \(Bill\) Vaughn]

Re: Connection pooling question

CP::Connection Pool
The differences between JET and SQL Server are ... well, dramatic. They are
designed very differently. JET is a throw-back to shared-file ISAM (dBASE)
database engines (circa 1970's) while SQL Server is a service-based engine
designed to handle many, many users and far more secure and scalable
database requirements. I characterize JET as a "home" database and I don't
recommend it for any (serious) business applications--despite the fact that
it's in very wide use all over the world in lots of businesses. It makes a
sad web DBMS engine. While it can work, you're likely to see more and more
serious (unsolvable) problems with JET when used incorrectly.

Will SQL Server tolerate code that does not properly close connections?
Nope, its connection pool will overflow if you don't write the application
correctly. Will it expose more counters and trace metrics to let you inspect
the CP status? Sure and then some. Is it designed for use in a web site?
Absolutely.

I looked at your code (again) and I see that you're manually opening the
connection. You don't have to. In your case I suggest that you don't. The
Fill method opens the connection (if it's not already open), runs the query,
populates the DataSet and closes the connection (if it was opened by Fill).

I still suspect you might be over-running the ability of JET to handle the
workload. Remember JET can't stop work on a query and service another
request. All requests are handled serially (unlike SQL Server).

I discuss all of this and more in my latest book.

--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition

-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ego1grRgHH A.3960@TK2MSFTN GP02.phx.gbl...

Thank you.

>>If the cause of your problem is the CP

What did you mean by CP ?
>

>>JET is not designed for this kind of work.

So, if I use SQL Server (and assuming I use the code like below, except
using SqlConnection instead of OLEDbConnection ), most likely I will not
have the problem where the pool is filling like in Access ? Is the maximum
pool size in Access smaller than in SQL Server (where the default is 100)
?
>
I close the connection right after I fill the dataset like shown below.
Can I close the connection faster then the way I do it ?
This code is called everytime somebody login to the application.
>
Dim cmd As New OleDb.OleDbComm and
Dim da As OleDb.OleDbData Adapter
Dim ds As DataSet
Dim ConnectionDemoO LE As OleDb.OleDbConn ection
With cmd
bDBSuccess = OpenDBDemoOLE(C onnectionDemoOL E)
If bDBSuccess Then
.Connection = ConnectionDemoO LE
.CommandText = sql
Try
da = New OleDb.OleDbData Adapter
ds = New DataSet
da.SelectComman d = cmd
da.Fill(ds)
CloseConDemoOLE (ConnectionDemo OLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE (ByRef ConnectionDemoO LE As OleDb.OleDbConn ection)
If Not ConnectionDemoO LE Is Nothing Then
ConnectionDemoO LE.Close()
ConnectionDemoO LE = Nothing
End If
End Sub
>
Function OpenDBDemoOLE(B yRef ConnectionDemoO LE As
OleDb.OleDbConn ection) As Boolean
Try
ConnectionDemoO LE = New OleDb.OleDbConn ection
OpenDBDemoOLE = True
With ConnectionDemoO LE
.ConnectionStri ng = g_dbPathDemo
.Open()
If .State = ConnectionState .Closed Then
CloseConDemoOLE (ConnectionDemo OLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
>
"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:Oroa83QgHH A.3460@TK2MSFTN GP04.phx.gbl...

>Ah, I doubt if this will help. It assumes that the engine has enough idle
>time to do it's work. The fundamental issue is clear. If the cause of
>your problem is the CP and the pool is filling then something is
>overloading the engine or your code is not releasing/closing connections
>in a timely fashion. Again, JET is not designed for this kind of work. I
>think you're beating a dead horse.
>>
>--
>William (Bill) Vaughn
>Author, Mentor, Consultant
>Microsoft MVP
>INETA Speaker
>www.betav.com/blog/billva
>www.betav.com
>Please reply only to the newsgroup so that others can benefit.
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>______________ _______________ _____
>Visit www.hitchhikerguides.net to get more information on my latest
>books:
>Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
>Hitchhiker's Guide to SQL Server 2005 Compact Edition
>>
>-----------------------------------------------------------------------------------------------------------------------
>"fniles" <fniles@pfmail. comwrote in message
>news:OUmc0jKgH HA.2640@TK2MSFT NGP06.phx.gbl.. .

>>Thank you.
>>I do use try/catch, but I get the "Unspecifie d error".
>>>
>>When the maximum pool size has been reached and I get an error, how can
>>I loop and wait until a connection is available again ?
>>>
>> Try
>> ConnectionOLE = New OleDb.OleDbConn ection
>> OpenDBOLE = True
>> With ConnectionOLE
>> .ConnectionStri ng = g_dbPath
>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>GO to the Catch. How can I loop and wait to open the db until a
>>connection is available again ?
>> End With
>> Catch ex As Exception
>> Try
>>>
>>Thank you.
>>>
>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>>news:OlI3%236 HgHHA.4804@TK2M SFTNGP02.phx.gb l...
>>>See >>>>>
>>>>
>>>--
>>>William (Bill) Vaughn
>>>Author, Mentor, Consultant
>>>Microsoft MVP
>>>INETA Speaker
>>>www.betav.com/blog/billva
>>>www.betav.com
>>>Please reply only to the newsgroup so that others can benefit.
>>>This posting is provided "AS IS" with no warranties, and confers no
>>>rights.
>>>____________ _______________ _______
>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>books:
>>>Hitchhiker 's Guide to Visual Studio and SQL Server (7th Edition) and
>>>Hitchhiker 's Guide to SQL Server 2005 Compact Edition
>>>>
>>>-----------------------------------------------------------------------------------------------------------------------
>>>"fniles" <fniles@pfmail. comwrote in message
>>>news:%23uACz GHgHHA.4260@TK2 MSFTNGP03.phx.g bl...
>>>>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>>>>Access.
>>>>In SQL Server, in the connection string you can set the max pool size,
>>>>can you do that in MS Access ?
>>>>
>>>>>>>>Nope.
>>>>
>>>>>
>>>>You mentioned that "If you exhaust the pool you should get a timeout
>>>>exception ." So, you will get an error, right ?
>>>>I mean if you put try-catch-end try, it will go to the Cath section,
>>>>right ?
>>>>
>>>>>>>>Right .
>>>>
>>>>In my other posting I posted that sometimes I get "Unspecifie d error"
>>>>on the Open method when opening up the OleDBConnection . This does not
>>>>happen all the time, only sometimes, which makes me think that maybe
>>>>the maximum pool size has been reached ? Is it possible that the
>>>>"Unspecifie d error" on the Open method caused by the maximum pool size
>>>>has been reached ?
>>>>
>>>>>>>"Unspeci fied" errors generally means you don't have a Try/Catch
>>>>>>>block to trap the specific exception. In Access/JET, yes it might
>>>>>>>mean the pool is full or the database is corrupt, or almost
>>>>>>>anythi ng else.
>>>>
>>>>
>>>>>
>>>> Dim swError As StreamWriter
>>>> Dim sSub As String
>>>> Try
>>>> sSub = "1"
>>>> ConnectionOLE = New OleDb.OleDbConn ection
>>>> OpenDBOLE = True
>>>> With ConnectionOLE
>>>> .ConnectionStri ng = g_dbPath
>>>> sSub = "2"
>>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>>>GO TO THE Catch section below ?
>>>>
>>>>>>In theory... yes.
>>>>
>>>>
>>>> sSub = "3"
>>>> End With
>>>> Catch ex As Exception
>>>> swError = New StreamWriter(Ap plication.Start upPath &
>>>>"\AQErrorLo g" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
>>>> swError.Write(N ow & " OpenDBOLE - error = " &
>>>>ex.Messag e & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>>>> swError.Close()
>>>> swError = Nothing
>>>> End Try
>>>>>
>>>>Thank you.
>>>>>
>>>>>
>>>>>
>>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in
>>>>message news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..
>>>>>>I don't think there are any system counters exposed by the JET
>>>>>>provide r or by OLE DB to monitor the CP.
>>>>>If you exhaust the pool you should get a timeout exception.
>>>>>However, (and Ginny please correct me here), if you're working with
>>>>>JET and an ASP application, you've got your wires crossed. JET is not
>>>>>designed to provide data for more than one user. Sure, you can share
>>>>>a JET .MDB database over a LAN, but each user gets its own JET engine
>>>>>to access the file. Using it in a web application that requires one
>>>>>JET engine to access the data is problematic at best. I suggest using
>>>>>a DBMS designed for the web--SQL Express.
>>>>>>
>>>>>hth
>>>>>>
>>>>>--
>>>>>William (Bill) Vaughn
>>>>>Author, Mentor, Consultant
>>>>>Microsof t MVP
>>>>>INETA Speaker
>>>>>www.betav.com/blog/billva
>>>>>www.betav.com
>>>>>Please reply only to the newsgroup so that others can benefit.
>>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>>rights.
>>>>>__________ _______________ _________
>>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>>books:
>>>>>Hitchhiker 's Guide to Visual Studio and SQL Server (7th Edition) and
>>>>>Hitchhiker 's Guide to SQL Server 2005 Compact Edition
>>>>>>
>>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>>"fniles" <fniles@pfmail. comwrote in message
>>>>>news:ejDHZ zGgHHA.4368@TK2 MSFTNGP03.phx.g bl...
>>>>>>>I am using VS2003 and connecting to MS Access database.
>>>>>>When using a connection pooling (every time I open the
>>>>>>OLEDBCONN ECTION I use the exact matching connection string),
>>>>>>1. how can I know how many connection has been used ?
>>>>>>2. If the maximum pool size has been reached, what happens when I
>>>>>>call the method Open to open the connection ? Will I get an error ?
>>>>>>MSDN says the request is queued, but will I get an error in the open
>>>>>>method ?
>>>>>>>
>>>>>>Connectio nDemoOLE = New OleDb.OleDbConn ection
>>>>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>>>>";OLE DB
>>>>>>Service s=-1"
>>>>>>With ConnectionDemoO LE
>>>>>> .ConnectionStri ng = sPath
>>>>>> .Open() --what happens here when the maximum pool size has
>>>>>>been reached ?
>>>>>>>
>>>>>>Thanks
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[Paul Clement]

Re: Connection pooling question

On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fniles@pfmail. comwrote:

¤ I am using VS2003 and connecting to MS Access database.
¤ When using a connection pooling (every time I open the OLEDBCONNECTION I use
¤ the exact matching connection string),
¤ 1. how can I know how many connection has been used ?
¤ 2. If the maximum pool size has been reached, what happens when I call the
¤ method Open to open the connection ? Will I get an error ? MSDN says the
¤ request is queued, but will I get an error in the open method ?
¤
¤ ConnectionDemoO LE = New OleDb.OleDbConn ection
¤ sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath & ";OLE DB
¤ Services=-1"
¤ With ConnectionDemoO LE
¤ .ConnectionStri ng = sPath
¤ .Open() --what happens here when the maximum pool size has been
¤ reached ?

While connection pooling is supported in Jet, it's really of little value and there isn't really any
point in attempting to monitor it. But to answer your prior question, it's enabled by default and
the connection pools are maintained on each client that opens the database because that is where the
database engine in running.

If instead you want to monitor the users in your database there are tools to do this:

How to determine who is logged on to a database by using Microsoft Jet UserRoster in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;EN-US;198755

Paul
~~~~
Microsoft MVP (Visual Basic)

[fniles]

Re: Connection pooling question

Thank you one more time. You are very helpful.
You suggested to not open the connection manually. How do you do that ?
Don't I need to assign a connection to the OleDbCommand ?
You mentioned the Fill method opens the connection, how does it know what
connection ?

Will my code than look something like below :

Dim cmd As New OleDb.OleDbComm and
Dim da As OleDb.OleDbData Adapter
Dim ds As DataSet
Dim ConnectionDemoO LE As OleDb.OleDbConn ection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(C onnectionDemoOL E) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoO LE '????
.CommandText = sql
Try
da = New OleDb.OleDbData Adapter
ds = New DataSet
da.SelectComman d = cmd
da.Fill(ds)
CloseConDemoOLE (ConnectionDemo OLE)
Catch ex As Exception
end try

Thank you.

"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:ulZ4z6UgHH A.1388@TK2MSFTN GP05.phx.gbl...

CP::Connection Pool
The differences between JET and SQL Server are ... well, dramatic. They
are designed very differently. JET is a throw-back to shared-file ISAM
(dBASE) database engines (circa 1970's) while SQL Server is a
service-based engine designed to handle many, many users and far more
secure and scalable database requirements. I characterize JET as a "home"
database and I don't recommend it for any (serious) business
applications--despite the fact that it's in very wide use all over the
world in lots of businesses. It makes a sad web DBMS engine. While it can
work, you're likely to see more and more serious (unsolvable) problems
with JET when used incorrectly.
>
Will SQL Server tolerate code that does not properly close connections?
Nope, its connection pool will overflow if you don't write the application
correctly. Will it expose more counters and trace metrics to let you
inspect the CP status? Sure and then some. Is it designed for use in a web
site? Absolutely.
>
I looked at your code (again) and I see that you're manually opening the
connection. You don't have to. In your case I suggest that you don't. The
Fill method opens the connection (if it's not already open), runs the
query, populates the DataSet and closes the connection (if it was opened
by Fill).
>
I still suspect you might be over-running the ability of JET to handle the
workload. Remember JET can't stop work on a query and service another
request. All requests are handled serially (unlike SQL Server).
>
I discuss all of this and more in my latest book.
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva

www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ego1grRgHH A.3960@TK2MSFTN GP02.phx.gbl...

>Thank you.

>>>If the cause of your problem is the CP

>What did you mean by CP ?
>>

>>>JET is not designed for this kind of work.

>So, if I use SQL Server (and assuming I use the code like below, except
>using SqlConnection instead of OLEDbConnection ), most likely I will not
>have the problem where the pool is filling like in Access ? Is the
>maximum pool size in Access smaller than in SQL Server (where the default
>is 100) ?
>>
>I close the connection right after I fill the dataset like shown below.
>Can I close the connection faster then the way I do it ?
>This code is called everytime somebody login to the application.
>>
>Dim cmd As New OleDb.OleDbComm and
>Dim da As OleDb.OleDbData Adapter
>Dim ds As DataSet
>Dim ConnectionDemoO LE As OleDb.OleDbConn ection
>With cmd
> bDBSuccess = OpenDBDemoOLE(C onnectionDemoOL E)
> If bDBSuccess Then
> .Connection = ConnectionDemoO LE
> .CommandText = sql
> Try
> da = New OleDb.OleDbData Adapter
> ds = New DataSet
> da.SelectComman d = cmd
> da.Fill(ds)
> CloseConDemoOLE (ConnectionDemo OLE)
> Catch ex As Exception
> end try
> Sub CloseConDemoOLE (ByRef ConnectionDemoO LE As OleDb.OleDbConn ection)
> If Not ConnectionDemoO LE Is Nothing Then
> ConnectionDemoO LE.Close()
> ConnectionDemoO LE = Nothing
> End If
> End Sub
>>
> Function OpenDBDemoOLE(B yRef ConnectionDemoO LE As
>OleDb.OleDbCon nection) As Boolean
> Try
> ConnectionDemoO LE = New OleDb.OleDbConn ection
> OpenDBDemoOLE = True
> With ConnectionDemoO LE
> .ConnectionStri ng = g_dbPathDemo
> .Open()
> If .State = ConnectionState .Closed Then
> CloseConDemoOLE (ConnectionDemo OLE)
> OpenDBDemoOLE = False
> End If
> End With
> Catch ex As Exception
>end try
>>
>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>news:Oroa83QgH HA.3460@TK2MSFT NGP04.phx.gbl.. .

>>Ah, I doubt if this will help. It assumes that the engine has enough
>>idle time to do it's work. The fundamental issue is clear. If the cause
>>of your problem is the CP and the pool is filling then something is
>>overloading the engine or your code is not releasing/closing connections
>>in a timely fashion. Again, JET is not designed for this kind of work. I
>>think you're beating a dead horse.
>>>
>>--
>>William (Bill) Vaughn
>>Author, Mentor, Consultant
>>Microsoft MVP
>>INETA Speaker
>>www.betav.com/blog/billva
>>www.betav.com
>>Please reply only to the newsgroup so that others can benefit.
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>_____________ _______________ ______
>>Visit www.hitchhikerguides.net to get more information on my latest
>>books:
>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>
>>-----------------------------------------------------------------------------------------------------------------------
>>"fniles" <fniles@pfmail. comwrote in message
>>news:OUmc0jKg HHA.2640@TK2MSF TNGP06.phx.gbl. ..
>>>Thank you.
>>>I do use try/catch, but I get the "Unspecifie d error".
>>>>
>>>When the maximum pool size has been reached and I get an error, how can
>>>I loop and wait until a connection is available again ?
>>>>
>>> Try
>>> ConnectionOLE = New OleDb.OleDbConn ection
>>> OpenDBOLE = True
>>> With ConnectionOLE
>>> .ConnectionStri ng = g_dbPath
>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>>GO to the Catch. How can I loop and wait to open the db until a
>>>connection is available again ?
>>> End With
>>> Catch ex As Exception
>>> Try
>>>>
>>>Thank you.
>>>>
>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>>>news:OlI3%23 6HgHHA.4804@TK2 MSFTNGP02.phx.g bl...
>>>>See >>>>>
>>>>>
>>>>--
>>>>William (Bill) Vaughn
>>>>Author, Mentor, Consultant
>>>>Microsoft MVP
>>>>INETA Speaker
>>>>www.betav.com/blog/billva
>>>>www.betav.com
>>>>Please reply only to the newsgroup so that others can benefit.
>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>rights.
>>>>___________ _______________ ________
>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>books:
>>>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>>>
>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>"fniles" <fniles@pfmail. comwrote in message
>>>>news:%23uAC zGHgHHA.4260@TK 2MSFTNGP03.phx. gbl...
>>>>>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>>>>>Access.
>>>>>In SQL Server, in the connection string you can set the max pool
>>>>>size, can you do that in MS Access ?
>>>>>
>>>>>>>>>Nope .
>>>>>
>>>>>>
>>>>>You mentioned that "If you exhaust the pool you should get a timeout
>>>>>exception. " So, you will get an error, right ?
>>>>>I mean if you put try-catch-end try, it will go to the Cath section,
>>>>>right ?
>>>>>
>>>>>>>>>Righ t.
>>>>>
>>>>>In my other posting I posted that sometimes I get "Unspecifie d error"
>>>>>on the Open method when opening up the OleDBConnection . This does not
>>>>>happen all the time, only sometimes, which makes me think that maybe
>>>>>the maximum pool size has been reached ? Is it possible that the
>>>>>"Unspecifi ed error" on the Open method caused by the maximum pool
>>>>>size has been reached ?
>>>>>
>>>>>>>>"Unspec ified" errors generally means you don't have a Try/Catch
>>>>>>>>block to trap the specific exception. In Access/JET, yes it might
>>>>>>>>mean the pool is full or the database is corrupt, or almost
>>>>>>>>anythin g else.
>>>>>
>>>>>
>>>>>>
>>>>> Dim swError As StreamWriter
>>>>> Dim sSub As String
>>>>> Try
>>>>> sSub = "1"
>>>>> ConnectionOLE = New OleDb.OleDbConn ection
>>>>> OpenDBOLE = True
>>>>> With ConnectionOLE
>>>>> .ConnectionStri ng = g_dbPath
>>>>> sSub = "2"
>>>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT
>>>>>WILL GO TO THE Catch section below ?
>>>>>
>>>>>>>In theory... yes.
>>>>>
>>>>>
>>>>> sSub = "3"
>>>>> End With
>>>>> Catch ex As Exception
>>>>> swError = New StreamWriter(Ap plication.Start upPath &
>>>>>"\AQErrorL og" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
>>>>> swError.Write(N ow & " OpenDBOLE - error = " &
>>>>>ex.Messa ge & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>>>>> swError.Close()
>>>>> swError = Nothing
>>>>> End Try
>>>>>>
>>>>>Thank you.
>>>>>>
>>>>>>
>>>>>>
>>>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in
>>>>>message news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..
>>>>>>>I don't think there are any system counters exposed by the JET
>>>>>>>provid er or by OLE DB to monitor the CP.
>>>>>>If you exhaust the pool you should get a timeout exception.
>>>>>>However , (and Ginny please correct me here), if you're working with
>>>>>>JET and an ASP application, you've got your wires crossed. JET is
>>>>>>not designed to provide data for more than one user. Sure, you can
>>>>>>share a JET .MDB database over a LAN, but each user gets its own JET
>>>>>>engine to access the file. Using it in a web application that
>>>>>>require s one JET engine to access the data is problematic at best. I
>>>>>>suggest using a DBMS designed for the web--SQL Express.
>>>>>>>
>>>>>>hth
>>>>>>>
>>>>>>--
>>>>>>William (Bill) Vaughn
>>>>>>Author, Mentor, Consultant
>>>>>>Microso ft MVP
>>>>>>INETA Speaker
>>>>>>www.betav.com/blog/billva
>>>>>>www.betav.com
>>>>>>Please reply only to the newsgroup so that others can benefit.
>>>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>>>rights.
>>>>>>_________ _______________ __________
>>>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>>>books:
>>>>>>Hitchhike r's Guide to Visual Studio and SQL Server (7th Edition) and
>>>>>>Hitchhike r's Guide to SQL Server 2005 Compact Edition
>>>>>>>
>>>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>>>"fniles " <fniles@pfmail. comwrote in message
>>>>>>news:ejDH ZzGgHHA.4368@TK 2MSFTNGP03.phx. gbl...
>>>>>>>>I am using VS2003 and connecting to MS Access database.
>>>>>>>When using a connection pooling (every time I open the
>>>>>>>OLEDBCON NECTION I use the exact matching connection string),
>>>>>>>1. how can I know how many connection has been used ?
>>>>>>>2. If the maximum pool size has been reached, what happens when I
>>>>>>>call the method Open to open the connection ? Will I get an error ?
>>>>>>>MSDN says the request is queued, but will I get an error in the
>>>>>>>open method ?
>>>>>>>>
>>>>>>>Connecti onDemoOLE = New OleDb.OleDbConn ection
>>>>>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>>>>>";OLE DB
>>>>>>>Services =-1"
>>>>>>>With ConnectionDemoO LE
>>>>>>> .ConnectionStri ng = sPath
>>>>>>> .Open() --what happens here when the maximum pool size has
>>>>>>>been reached ?
>>>>>>>>
>>>>>>>Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>

[fniles]

Re: Connection pooling question

I have a question.
If for example application A and B uses the same database (either Access or
SQL Server).
Will the maximum connection pooling apply per application ? In other words,
if maximum connection pooling is 100, application A uses 10 connetions, will
application B has 90 or 100 connections left ?

Thank you.

"Paul Clement" <UseAdddressAtE ndofMessage@sws pectrum.comwrot e in message
news:gk4c23hbbp s8kdcbv6dha9t8q eoapa3d2j@4ax.c om...

On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fniles@pfmail. comwrote:
>
¤ I am using VS2003 and connecting to MS Access database.
¤ When using a connection pooling (every time I open the OLEDBCONNECTION I
use
¤ the exact matching connection string),
¤ 1. how can I know how many connection has been used ?
¤ 2. If the maximum pool size has been reached, what happens when I call
the
¤ method Open to open the connection ? Will I get an error ? MSDN says the
¤ request is queued, but will I get an error in the open method ?
¤
¤ ConnectionDemoO LE = New OleDb.OleDbConn ection
¤ sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
";OLE DB
¤ Services=-1"
¤ With ConnectionDemoO LE
¤ .ConnectionStri ng = sPath
¤ .Open() --what happens here when the maximum pool size has been
¤ reached ?
>
While connection pooling is supported in Jet, it's really of little value
and there isn't really any
point in attempting to monitor it. But to answer your prior question, it's
enabled by default and
the connection pools are maintained on each client that opens the database
because that is where the
database engine in running.
>
If instead you want to monitor the users in your database there are tools
to do this:
>
How to determine who is logged on to a database by using Microsoft Jet
UserRoster in Access 2000

http://support.microsoft.com/default.aspx?scid=kb;EN-US;198755

>
>
Paul
~~~~
Microsoft MVP (Visual Basic)

[Paul Clement]

Re: Connection pooling question

On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fniles@pfmail. comwrote:

¤ I have a question.
¤ If for example application A and B uses the same database (either Access or
¤ SQL Server).
¤ Will the maximum connection pooling apply per application ? In other words,
¤ if maximum connection pooling is 100, application A uses 10 connetions, will
¤ application B has 90 or 100 connections left ?

Connection pools are create per process (or app pool) and per unique connection string. So unless
application A and application B are in the same app pool (such as a web appl) they will each have
their own connection pool.


Paul
~~~~
Microsoft MVP (Visual Basic)

[fniles]

Re: Connection pooling question

I notice when the application uses an Access db on my machine (where no
other application access that database) my application does not reach the
maximum pool connection as fast as when I use an Access db on our server
(where many other applications access that database).
Is this because even though my application say has maximum of 100
connections, because other applications access the same database, there may
not be connection available ?

Thanks.

"Paul Clement" <UseAdddressAtE ndofMessage@sws pectrum.comwrot e in message
news:798c231c4t e0auv2to19u1rjo pgeo1f4jf@4ax.c om...

On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fniles@pfmail. comwrote:
>
¤ I have a question.
¤ If for example application A and B uses the same database (either Access
or
¤ SQL Server).
¤ Will the maximum connection pooling apply per application ? In other
words,
¤ if maximum connection pooling is 100, application A uses 10 connetions,
will
¤ application B has 90 or 100 connections left ?
>
Connection pools are create per process (or app pool) and per unique
connection string. So unless
application A and application B are in the same app pool (such as a web
appl) they will each have
their own connection pool.
>
>
Paul
~~~~
Microsoft MVP (Visual Basic)

[fniles]

Re: Connection pooling question

Also, 1 more question.
The way I do connection pooling is the following:
In the main form load I open a connection using a connection string that I
stored in a global variable g_sConnectionSt ring and leave this connection
open and not close it until it exits the application.
Then on each thread I create a local OleDBConnection variable, open the
connection using the exact same connection string as the main form (stored
in global variable g_sConnectionSt ring), and close it after populating a
DataSet.
Is this correct ?

Thank you.

"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
news:ulZ4z6UgHH A.1388@TK2MSFTN GP05.phx.gbl...

CP::Connection Pool
The differences between JET and SQL Server are ... well, dramatic. They
are designed very differently. JET is a throw-back to shared-file ISAM
(dBASE) database engines (circa 1970's) while SQL Server is a
service-based engine designed to handle many, many users and far more
secure and scalable database requirements. I characterize JET as a "home"
database and I don't recommend it for any (serious) business
applications--despite the fact that it's in very wide use all over the
world in lots of businesses. It makes a sad web DBMS engine. While it can
work, you're likely to see more and more serious (unsolvable) problems
with JET when used incorrectly.
>
Will SQL Server tolerate code that does not properly close connections?
Nope, its connection pool will overflow if you don't write the application
correctly. Will it expose more counters and trace metrics to let you
inspect the CP status? Sure and then some. Is it designed for use in a web
site? Absolutely.
>
I looked at your code (again) and I see that you're manually opening the
connection. You don't have to. In your case I suggest that you don't. The
Fill method opens the connection (if it's not already open), runs the
query, populates the DataSet and closes the connection (if it was opened
by Fill).
>
I still suspect you might be over-running the ability of JET to handle the
workload. Remember JET can't stop work on a query and service another
request. All requests are handled serially (unlike SQL Server).
>
I discuss all of this and more in my latest book.
>
--
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker

https://www.betav.com/blog/billva

https://www.betav.com

Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
_______________ _______________ ____
Visit www.hitchhikerguides.net to get more information on my latest books:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and
Hitchhiker's Guide to SQL Server 2005 Compact Edition
>
-----------------------------------------------------------------------------------------------------------------------
"fniles" <fniles@pfmail. comwrote in message
news:ego1grRgHH A.3960@TK2MSFTN GP02.phx.gbl...

>Thank you.

>>>If the cause of your problem is the CP

>What did you mean by CP ?
>>

>>>JET is not designed for this kind of work.

>So, if I use SQL Server (and assuming I use the code like below, except
>using SqlConnection instead of OLEDbConnection ), most likely I will not
>have the problem where the pool is filling like in Access ? Is the
>maximum pool size in Access smaller than in SQL Server (where the default
>is 100) ?
>>
>I close the connection right after I fill the dataset like shown below.
>Can I close the connection faster then the way I do it ?
>This code is called everytime somebody login to the application.
>>
>Dim cmd As New OleDb.OleDbComm and
>Dim da As OleDb.OleDbData Adapter
>Dim ds As DataSet
>Dim ConnectionDemoO LE As OleDb.OleDbConn ection
>With cmd
> bDBSuccess = OpenDBDemoOLE(C onnectionDemoOL E)
> If bDBSuccess Then
> .Connection = ConnectionDemoO LE
> .CommandText = sql
> Try
> da = New OleDb.OleDbData Adapter
> ds = New DataSet
> da.SelectComman d = cmd
> da.Fill(ds)
> CloseConDemoOLE (ConnectionDemo OLE)
> Catch ex As Exception
> end try
> Sub CloseConDemoOLE (ByRef ConnectionDemoO LE As OleDb.OleDbConn ection)
> If Not ConnectionDemoO LE Is Nothing Then
> ConnectionDemoO LE.Close()
> ConnectionDemoO LE = Nothing
> End If
> End Sub
>>
> Function OpenDBDemoOLE(B yRef ConnectionDemoO LE As
>OleDb.OleDbCon nection) As Boolean
> Try
> ConnectionDemoO LE = New OleDb.OleDbConn ection
> OpenDBDemoOLE = True
> With ConnectionDemoO LE
> .ConnectionStri ng = g_dbPathDemo
> .Open()
> If .State = ConnectionState .Closed Then
> CloseConDemoOLE (ConnectionDemo OLE)
> OpenDBDemoOLE = False
> End If
> End With
> Catch ex As Exception
>end try
>>
>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>news:Oroa83QgH HA.3460@TK2MSFT NGP04.phx.gbl.. .

>>Ah, I doubt if this will help. It assumes that the engine has enough
>>idle time to do it's work. The fundamental issue is clear. If the cause
>>of your problem is the CP and the pool is filling then something is
>>overloading the engine or your code is not releasing/closing connections
>>in a timely fashion. Again, JET is not designed for this kind of work. I
>>think you're beating a dead horse.
>>>
>>--
>>William (Bill) Vaughn
>>Author, Mentor, Consultant
>>Microsoft MVP
>>INETA Speaker
>>www.betav.com/blog/billva
>>www.betav.com
>>Please reply only to the newsgroup so that others can benefit.
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>_____________ _______________ ______
>>Visit www.hitchhikerguides.net to get more information on my latest
>>books:
>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>
>>-----------------------------------------------------------------------------------------------------------------------
>>"fniles" <fniles@pfmail. comwrote in message
>>news:OUmc0jKg HHA.2640@TK2MSF TNGP06.phx.gbl. ..
>>>Thank you.
>>>I do use try/catch, but I get the "Unspecifie d error".
>>>>
>>>When the maximum pool size has been reached and I get an error, how can
>>>I loop and wait until a connection is available again ?
>>>>
>>> Try
>>> ConnectionOLE = New OleDb.OleDbConn ection
>>> OpenDBOLE = True
>>> With ConnectionOLE
>>> .ConnectionStri ng = g_dbPath
>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT WILL
>>>GO to the Catch. How can I loop and wait to open the db until a
>>>connection is available again ?
>>> End With
>>> Catch ex As Exception
>>> Try
>>>>
>>>Thank you.
>>>>
>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in message
>>>news:OlI3%23 6HgHHA.4804@TK2 MSFTNGP02.phx.g bl...
>>>>See >>>>>
>>>>>
>>>>--
>>>>William (Bill) Vaughn
>>>>Author, Mentor, Consultant
>>>>Microsoft MVP
>>>>INETA Speaker
>>>>www.betav.com/blog/billva
>>>>www.betav.com
>>>>Please reply only to the newsgroup so that others can benefit.
>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>rights.
>>>>___________ _______________ ________
>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>books:
>>>>Hitchhiker' s Guide to Visual Studio and SQL Server (7th Edition) and
>>>>Hitchhiker' s Guide to SQL Server 2005 Compact Edition
>>>>>
>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>"fniles" <fniles@pfmail. comwrote in message
>>>>news:%23uAC zGHgHHA.4260@TK 2MSFTNGP03.phx. gbl...
>>>>>>I am using VB.Net 2003 over a LAN, and I have no choice but to use MS
>>>>>>Access.
>>>>>In SQL Server, in the connection string you can set the max pool
>>>>>size, can you do that in MS Access ?
>>>>>
>>>>>>>>>Nope .
>>>>>
>>>>>>
>>>>>You mentioned that "If you exhaust the pool you should get a timeout
>>>>>exception. " So, you will get an error, right ?
>>>>>I mean if you put try-catch-end try, it will go to the Cath section,
>>>>>right ?
>>>>>
>>>>>>>>>Righ t.
>>>>>
>>>>>In my other posting I posted that sometimes I get "Unspecifie d error"
>>>>>on the Open method when opening up the OleDBConnection . This does not
>>>>>happen all the time, only sometimes, which makes me think that maybe
>>>>>the maximum pool size has been reached ? Is it possible that the
>>>>>"Unspecifi ed error" on the Open method caused by the maximum pool
>>>>>size has been reached ?
>>>>>
>>>>>>>>"Unspec ified" errors generally means you don't have a Try/Catch
>>>>>>>>block to trap the specific exception. In Access/JET, yes it might
>>>>>>>>mean the pool is full or the database is corrupt, or almost
>>>>>>>>anythin g else.
>>>>>
>>>>>
>>>>>>
>>>>> Dim swError As StreamWriter
>>>>> Dim sSub As String
>>>>> Try
>>>>> sSub = "1"
>>>>> ConnectionOLE = New OleDb.OleDbConn ection
>>>>> OpenDBOLE = True
>>>>> With ConnectionOLE
>>>>> .ConnectionStri ng = g_dbPath
>>>>> sSub = "2"
>>>>> .Open() --IF MAX POOL SIZE HAS BEEN REACHED, IT
>>>>>WILL GO TO THE Catch section below ?
>>>>>
>>>>>>>In theory... yes.
>>>>>
>>>>>
>>>>> sSub = "3"
>>>>> End With
>>>>> Catch ex As Exception
>>>>> swError = New StreamWriter(Ap plication.Start upPath &
>>>>>"\AQErrorL og" & Date.Now.ToStri ng("MMddyy") & ".txt", True)
>>>>> swError.Write(N ow & " OpenDBOLE - error = " &
>>>>>ex.Messa ge & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>>>>> swError.Close()
>>>>> swError = Nothing
>>>>> End Try
>>>>>>
>>>>>Thank you.
>>>>>>
>>>>>>
>>>>>>
>>>>>"William (Bill) Vaughn" <billva@NoSpamA tAll_betav.comw rote in
>>>>>message news:u2%23Nf5Gg HHA.3388@TK2MSF TNGP02.phx.gbl. ..
>>>>>>>I don't think there are any system counters exposed by the JET
>>>>>>>provid er or by OLE DB to monitor the CP.
>>>>>>If you exhaust the pool you should get a timeout exception.
>>>>>>However , (and Ginny please correct me here), if you're working with
>>>>>>JET and an ASP application, you've got your wires crossed. JET is
>>>>>>not designed to provide data for more than one user. Sure, you can
>>>>>>share a JET .MDB database over a LAN, but each user gets its own JET
>>>>>>engine to access the file. Using it in a web application that
>>>>>>require s one JET engine to access the data is problematic at best. I
>>>>>>suggest using a DBMS designed for the web--SQL Express.
>>>>>>>
>>>>>>hth
>>>>>>>
>>>>>>--
>>>>>>William (Bill) Vaughn
>>>>>>Author, Mentor, Consultant
>>>>>>Microso ft MVP
>>>>>>INETA Speaker
>>>>>>www.betav.com/blog/billva
>>>>>>www.betav.com
>>>>>>Please reply only to the newsgroup so that others can benefit.
>>>>>>This posting is provided "AS IS" with no warranties, and confers no
>>>>>>rights.
>>>>>>_________ _______________ __________
>>>>>>Visit www.hitchhikerguides.net to get more information on my latest
>>>>>>books:
>>>>>>Hitchhike r's Guide to Visual Studio and SQL Server (7th Edition) and
>>>>>>Hitchhike r's Guide to SQL Server 2005 Compact Edition
>>>>>>>
>>>>>>-----------------------------------------------------------------------------------------------------------------------
>>>>>>"fniles " <fniles@pfmail. comwrote in message
>>>>>>news:ejDH ZzGgHHA.4368@TK 2MSFTNGP03.phx. gbl...
>>>>>>>>I am using VS2003 and connecting to MS Access database.
>>>>>>>When using a connection pooling (every time I open the
>>>>>>>OLEDBCON NECTION I use the exact matching connection string),
>>>>>>>1. how can I know how many connection has been used ?
>>>>>>>2. If the maximum pool size has been reached, what happens when I
>>>>>>>call the method Open to open the connection ? Will I get an error ?
>>>>>>>MSDN says the request is queued, but will I get an error in the
>>>>>>>open method ?
>>>>>>>>
>>>>>>>Connecti onDemoOLE = New OleDb.OleDbConn ection
>>>>>>>sPath = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & sDBPath &
>>>>>>>";OLE DB
>>>>>>>Services =-1"
>>>>>>>With ConnectionDemoO LE
>>>>>>> .ConnectionStri ng = sPath
>>>>>>> .Open() --what happens here when the maximum pool size has
>>>>>>>been reached ?
>>>>>>>>
>>>>>>>Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>

>>
>>

>
>