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 ?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
20  3084 
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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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 ?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE
DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
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 "Unspecified 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 "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message & "
sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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 ?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE
DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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 "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message &
" sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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 ?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
";OLE DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
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@NoSpamAtAll_betav.comschreef in bericht
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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 ?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE
DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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 "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message &
" sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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
?
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
";OLE DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
Thanks
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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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
"Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message
& " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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
?
>
ConnectionDemoOLE = New OleDb.OleDbConnection
sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
";OLE DB
Services=-1"
With ConnectionDemoOLE
.ConnectionString = sPath
.Open() --what happens here when the maximum pool size has been
reached ?
>
Thanks
>
>
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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
>Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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
"Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " & ex.Message
& " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:ej**************@TK2MSFTNGP03.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 ?
>>
>ConnectionDemoOLE = New OleDb.OleDbConnection
>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
>";OLE DB
>Services=-1"
>With ConnectionDemoOLE
> .ConnectionString = sPath
> .Open() --what happens here when the maximum pool size has been
>reached ?
>>
>Thanks
>>
>>
>
>
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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
>>Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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
"Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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(Application.StartupPath &
"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
swError.Write(Now & " OpenDBOLE - error = " &
ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
swError.Close()
swError = Nothing
End Try
>
Thank you.
>
>
>
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>news:ej**************@TK2MSFTNGP03.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 ?
>>>
>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
>>";OLE DB
>>Services=-1"
>>With ConnectionDemoOLE
>> .ConnectionString = sPath
>> .Open() --what happens here when the maximum pool size has
>>been reached ?
>>>
>>Thanks
>>>
>>>
>>
>>
>
>
On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fn****@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 ?
¤
¤ ConnectionDemoOLE = New OleDb.OleDbConnection
¤ sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";OLE DB
¤ Services=-1"
¤ With ConnectionDemoOLE
¤ .ConnectionString = 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...b;EN-US;198755
Paul
~~~~
Microsoft MVP (Visual Basic)
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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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
>"Unspecified 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.OleDbConnection
> OpenDBOLE = True
> With ConnectionOLE
> .ConnectionString = 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(Application.StartupPath &
>"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
> swError.Write(Now & " OpenDBOLE - error = " &
>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
> swError.Close()
> swError = Nothing
> End Try
>>
>Thank you.
>>
>>
>>
>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>news:ej**************@TK2MSFTNGP03.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 ?
>>>>
>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
>>>";OLE DB
>>>Services=-1"
>>>With ConnectionDemoOLE
>>> .ConnectionString = sPath
>>> .Open() --what happens here when the maximum pool size has
>>>been reached ?
>>>>
>>>Thanks
>>>>
>>>>
>>>
>>>
>>
>>
>
>
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" <Us***********************@swspectrum.comwrote in message
news:gk********************************@4ax.com...
On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fn****@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 ?
¤
¤ ConnectionDemoOLE = New OleDb.OleDbConnection
¤ sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
";OLE DB
¤ Services=-1"
¤ With ConnectionDemoOLE
¤ .ConnectionString = 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...b;EN-US;198755
Paul
~~~~
Microsoft MVP (Visual Basic)
On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fn****@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)
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" <Us***********************@swspectrum.comwrote in message
news:79********************************@4ax.com...
On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fn****@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)
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_sConnectionString 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_sConnectionString), and close it after populating a
DataSet.
Is this correct ?
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in message
news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP03.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 "Unspecified 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
>"Unspecified 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.OleDbConnection
> OpenDBOLE = True
> With ConnectionOLE
> .ConnectionString = 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(Application.StartupPath &
>"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
> swError.Write(Now & " OpenDBOLE - error = " &
>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
> swError.Close()
> swError = Nothing
> End Try
>>
>Thank you.
>>
>>
>>
>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>news:ej**************@TK2MSFTNGP03.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 ?
>>>>
>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath &
>>>";OLE DB
>>>Services=-1"
>>>With ConnectionDemoOLE
>>> .ConnectionString = sPath
>>> .Open() --what happens here when the maximum pool size has
>>>been reached ?
>>>>
>>>Thanks
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Fniles,
Why not buy the book from Bill, it does not help you much in a high
theoretical question without answer.
However for those practical questions that you have now it is very good.
(I had it on my desk Bill, however my collegues took it away)
Cor
"fniles" <fn****@pfmail.comschreef in bericht
news:e$*************@TK2MSFTNGP05.phx.gbl...
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_sConnectionString 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_sConnectionString), and close it after populating a
DataSet.
Is this correct ?
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in
message news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>news:%2****************@TK2MSFTNGP03.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 "Unspecified
>>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 "Unspecified 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.OleDbConnection
>> OpenDBOLE = True
>> With ConnectionOLE
>> .ConnectionString = 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(Application.StartupPath &
>>"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
>> swError.Write(Now & " OpenDBOLE - error = " &
>>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>> swError.Close()
>> swError = Nothing
>> End Try
>>>
>>Thank you.
>>>
>>>
>>>
>>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>news:ej**************@TK2MSFTNGP03.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 ?
>>>>>
>>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath
>>>>& ";OLE DB
>>>>Services=-1"
>>>>With ConnectionDemoOLE
>>>> .ConnectionString = sPath
>>>> .Open() --what happens here when the maximum pool size has
>>>>been reached ?
>>>>>
>>>>Thanks
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Instantiating a Connection object and opening the connection to the data
source are two different steps.
You can create a global Connection object that's visible to the entire
application. In some situations this makes sense--especially in Windows
Forms applications.
Opening the connection changes the State, links the application to the data
source (like opening a file), and permits the application to send queries to
the data source (like a DBMS engine).
Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
--
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl...
Thank you.
I do use try/catch, but I get the "Unspecified 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.OleDbConnection
OpenDBOLE = True
With ConnectionOLE
.ConnectionString = 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@NoSpamAtAll_betav.comwrote in
message news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>news:%2****************@TK2MSFTNGP03.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 "Unspecified
>>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 "Unspecified 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.OleDbConnection
>> OpenDBOLE = True
>> With ConnectionOLE
>> .ConnectionString = 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(Application.StartupPath &
>>"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
>> swError.Write(Now & " OpenDBOLE - error = " &
>>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath & vbCrLf)
>> swError.Close()
>> swError = Nothing
>> End Try
>>>
>>Thank you.
>>>
>>>
>>>
>>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>news:ej**************@TK2MSFTNGP03.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 ?
>>>>>
>>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath
>>>>& ";OLE DB
>>>>Services=-1"
>>>>With ConnectionDemoOLE
>>>> .ConnectionString = sPath
>>>> .Open() --what happens here when the maximum pool size has
>>>>been reached ?
>>>>>
>>>>Thanks
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
Thank you.
I am sorry I am still not clear on how to call the Fill method without
opening the connection manually before hand.
Do you have to open the connection sometime before hand ?
Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
If for example application A and B uses the same database (either Access or
SQL Server). If maximum connection pooling is 100, each application will get
100 maximum connection pooling, is this correct ?
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 ?
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
Instantiating a Connection object and opening the connection to the data
source are two different steps.
You can create a global Connection object that's visible to the entire
application. In some situations this makes sense--especially in Windows
Forms applications.
Opening the connection changes the State, links the application to the
data source (like opening a file), and permits the application to send
queries to the data source (like a DBMS engine).
Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
--
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
news:OU**************@TK2MSFTNGP06.phx.gbl.. .
>Thank you.
>I do use try/catch, but I get the "Unspecified 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.OleDbConnection
> OpenDBOLE = True
> With ConnectionOLE
> .ConnectionString = 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@NoSpamAtAll_betav.comwrote in
>message news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>news:%2****************@TK2MSFTNGP03.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 "Unspecified
>>>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 "Unspecified 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.OleDbConnection
>>> OpenDBOLE = True
>>> With ConnectionOLE
>>> .ConnectionString = 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(Application.StartupPath &
>>>"\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
>>> swError.Write(Now & " OpenDBOLE - error = " &
>>>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath &
>>>vbCrLf)
>>> swError.Close()
>>> swError = Nothing
>>> End Try
>>>>
>>>Thank you.
>>>>
>>>>
>>>>
>>>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>>>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>>news:ej**************@TK2MSFTNGP03.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 ?
>>>>>>
>>>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath
>>>>>& ";OLE DB
>>>>>Services=-1"
>>>>>With ConnectionDemoOLE
>>>>> .ConnectionString = sPath
>>>>> .Open() --what happens here when the maximum pool size has
>>>>>been reached ?
>>>>>>
>>>>>Thanks
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
No. Fill opens the connection automatically and closes it afterwards.
However, if for any reason the connection has already been opened, Fill does
not change the connection state.
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" <fn****@pfmail.comwrote in message
news:OD**************@TK2MSFTNGP02.phx.gbl...
Thank you.
I am sorry I am still not clear on how to call the Fill method without
opening the connection manually before hand.
Do you have to open the connection sometime before hand ?
>Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
If for example application A and B uses the same database (either Access
or
SQL Server). If maximum connection pooling is 100, each application will
get 100 maximum connection pooling, is this correct ?
Connection pools are maintained on a process/application basis. Pools are
not shared between processes. Again, this assumes that the JET OLE DB
provider implements pooling. While I suspect it does, the issue has never
come up because JET when used in a Windows Forms application does not need a
pool unless you have implemented the application incorrectly.
>
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 ?
In a JET architecture where Windows Forms applications are opening the
database, each client/user gets its own copy of JET to manage the shared
file. Each client has its own pool and are not sharing any other
applications' pool. Just because your application opens more than one
connection, it does not impact the other applications' pool. It DOES impact
the performance of the application as each JET engine must perform physical
IO over the LAN to share the database file. This means if your (or any)
application opens a lot of connections to the datatabase, this puts an
enormous load on the system and the ability to share the data.
Remember that JET was designed to support a few users with light load. It
breaks down quickly when stressed (as you have discovered).
Again, this is all covered in my earlier books. Considering that MS
encourages developers to get off of JET for more suitable DBMS engines, I
suggest you follow their suggestions (and mine).
>
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>Instantiating a Connection object and opening the connection to the data
source are two different steps.
You can create a global Connection object that's visible to the entire
application. In some situations this makes sense--especially in Windows
Forms applications.
Opening the connection changes the State, links the application to the
data source (like opening a file), and permits the application to send
queries to the data source (like a DBMS engine).
Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
--
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>>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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection)
If Not ConnectionDemoOLE Is Nothing Then
ConnectionDemoOLE.Close()
ConnectionDemoOLE = Nothing
End If
End Sub
>
Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
OleDb.OleDbConnection) As Boolean
Try
ConnectionDemoOLE = New OleDb.OleDbConnection
OpenDBDemoOLE = True
With ConnectionDemoOLE
.ConnectionString = g_dbPathDemo
.Open()
If .State = ConnectionState.Closed Then
CloseConDemoOLE(ConnectionDemoOLE)
OpenDBDemoOLE = False
End If
End With
Catch ex As Exception
end try
>
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
message news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
>news:OU**************@TK2MSFTNGP06.phx.gbl. ..
>>Thank you.
>>I do use try/catch, but I get the "Unspecified 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.OleDbConnection
>> OpenDBOLE = True
>> With ConnectionOLE
>> .ConnectionString = 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@NoSpamAtAll_betav.comwrote in
>>message news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>news:%2****************@TK2MSFTNGP03.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 "Unspecified
>>>>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 "Unspecified 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.OleDbConnection
>>>> OpenDBOLE = True
>>>> With ConnectionOLE
>>>> .ConnectionString = 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(Application.StartupPath
>>>>& "\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
>>>> swError.Write(Now & " OpenDBOLE - error = " &
>>>>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath &
>>>>vbCrLf)
>>>> swError.Close()
>>>> swError = Nothing
>>>> End Try
>>>>>
>>>>Thank you.
>>>>>
>>>>>
>>>>>
>>>>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>>>>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>>>news:ej**************@TK2MSFTNGP03.phx.gb l...
>>>>>>>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 ?
>>>>>>>
>>>>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>>>>>>sDBPath & ";OLE DB
>>>>>>Services=-1"
>>>>>>With ConnectionDemoOLE
>>>>>> .ConnectionString = sPath
>>>>>> .Open() --what happens here when the maximum pool size has
>>>>>>been reached ?
>>>>>>>
>>>>>>Thanks
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
No. Fill opens the connection automatically and closes it afterwards.
I am sorry, but how does Fill know which connection to use ?
Do you have a sample code on how to just do fill without assigning a
connection to the OLEDbCommand object ? Thanks so much for your help.
These are my original codes:
Dim cmd As New OleDb.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
If bDBSuccess Then
.Connection = ConnectionDemoOLE
.CommandText = sql
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
:
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:uH**************@TK2MSFTNGP04.phx.gbl...
No. Fill opens the connection automatically and closes it afterwards.
However, if for any reason the connection has already been opened, Fill
does not change the connection state.
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" <fn****@pfmail.comwrote in message
news:OD**************@TK2MSFTNGP02.phx.gbl...
>Thank you.
I am sorry I am still not clear on how to call the Fill method without
opening the connection manually before hand.
Do you have to open the connection sometime before hand ?
>>Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
If for example application A and B uses the same database (either Access
or
SQL Server). If maximum connection pooling is 100, each application will
get 100 maximum connection pooling, is this correct ?
Connection pools are maintained on a process/application basis. Pools are
not shared between processes. Again, this assumes that the JET OLE DB
provider implements pooling. While I suspect it does, the issue has never
come up because JET when used in a Windows Forms application does not need
a pool unless you have implemented the application incorrectly.
>>
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 ?
In a JET architecture where Windows Forms applications are opening the
database, each client/user gets its own copy of JET to manage the shared
file. Each client has its own pool and are not sharing any other
applications' pool. Just because your application opens more than one
connection, it does not impact the other applications' pool. It DOES
impact the performance of the application as each JET engine must perform
physical IO over the LAN to share the database file. This means if your
(or any) application opens a lot of connections to the datatabase, this
puts an enormous load on the system and the ability to share the data.
Remember that JET was designed to support a few users with light load. It
breaks down quickly when stressed (as you have discovered).
Again, this is all covered in my earlier books. Considering that MS
encourages developers to get off of JET for more suitable DBMS engines, I
suggest you follow their suggestions (and mine).
>>
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
>>Instantiating a Connection object and opening the connection to the data
source are two different steps.
You can create a global Connection object that's visible to the entire
application. In some situations this makes sense--especially in Windows
Forms applications.
Opening the connection changes the State, links the application to the
data source (like opening a file), and permits the application to send
queries to the data source (like a DBMS engine).
Each application gets its own pool. Two applications running on the same
machine in different process spaces get their own pool--even if the
ConnectionString is the same.
--
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" <fn****@pfmail.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl.. .
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.OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim ConnectionDemoOLE As OleDb.OleDbConnection
With cmd
'----DO NOT NEED THIS CODE ----- bDBSuccess =
OpenDBDemoOLE(ConnectionDemoOLE) ------'
'----If bDBSuccess Then
.Connection = ConnectionDemoOLE '????
.CommandText = sql
Try
da = New OleDb.OleDbDataAdapter
ds = New DataSet
da.SelectCommand = cmd
da.Fill(ds)
CloseConDemoOLE(ConnectionDemoOLE)
Catch ex As Exception
end try
Thank you.
"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in message
news:ul**************@TK2MSFTNGP05.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" <fn****@pfmail.comwrote in message
news:eg**************@TK2MSFTNGP02.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.OleDbCommand
>Dim da As OleDb.OleDbDataAdapter
>Dim ds As DataSet
>Dim ConnectionDemoOLE As OleDb.OleDbConnection
>With cmd
> bDBSuccess = OpenDBDemoOLE(ConnectionDemoOLE)
> If bDBSuccess Then
> .Connection = ConnectionDemoOLE
> .CommandText = sql
> Try
> da = New OleDb.OleDbDataAdapter
> ds = New DataSet
> da.SelectCommand = cmd
> da.Fill(ds)
> CloseConDemoOLE(ConnectionDemoOLE)
> Catch ex As Exception
> end try
> Sub CloseConDemoOLE(ByRef ConnectionDemoOLE As
>OleDb.OleDbConnection)
> If Not ConnectionDemoOLE Is Nothing Then
> ConnectionDemoOLE.Close()
> ConnectionDemoOLE = Nothing
> End If
> End Sub
>>
> Function OpenDBDemoOLE(ByRef ConnectionDemoOLE As
>OleDb.OleDbConnection) As Boolean
> Try
> ConnectionDemoOLE = New OleDb.OleDbConnection
> OpenDBDemoOLE = True
> With ConnectionDemoOLE
> .ConnectionString = g_dbPathDemo
> .Open()
> If .State = ConnectionState.Closed Then
> CloseConDemoOLE(ConnectionDemoOLE)
> OpenDBDemoOLE = False
> End If
> End With
> Catch ex As Exception
>end try
>>
>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>message news:Or**************@TK2MSFTNGP04.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" <fn****@pfmail.comwrote in message
>>news:OU**************@TK2MSFTNGP06.phx.gbl.. .
>>>Thank you.
>>>I do use try/catch, but I get the "Unspecified 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.OleDbConnection
>>> OpenDBOLE = True
>>> With ConnectionOLE
>>> .ConnectionString = 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@NoSpamAtAll_betav.comwrote in
>>>message news:Ol****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>>news:%2****************@TK2MSFTNGP03.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 "Unspecified
>>>>>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 "Unspecified 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.OleDbConnection
>>>>> OpenDBOLE = True
>>>>> With ConnectionOLE
>>>>> .ConnectionString = 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(Application.StartupPath
>>>>>& "\AQErrorLog" & Date.Now.ToString("MMddyy") & ".txt", True)
>>>>> swError.Write(Now & " OpenDBOLE - error = " &
>>>>>ex.Message & " sub = " & sSub & " g_dbPath = " & g_dbPath &
>>>>>vbCrLf)
>>>>> swError.Close()
>>>>> swError = Nothing
>>>>> End Try
>>>>>>
>>>>>Thank you.
>>>>>>
>>>>>>
>>>>>>
>>>>>"William (Bill) Vaughn" <billva@NoSpamAtAll_betav.comwrote in
>>>>>message news:u2****************@TK2MSFTNGP02.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" <fn****@pfmail.comwrote in message
>>>>>>news:ej**************@TK2MSFTNGP03.phx.g bl...
>>>>>>>>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 ?
>>>>>>>>
>>>>>>>ConnectionDemoOLE = New OleDb.OleDbConnection
>>>>>>>sPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
>>>>>>>sDBPath & ";OLE DB
>>>>>>>Services=-1"
>>>>>>>With ConnectionDemoOLE
>>>>>>> .ConnectionString = sPath
>>>>>>> .Open() --what happens here when the maximum pool size
>>>>>>>has been reached ?
>>>>>>>>
>>>>>>>Thanks
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
On Wed, 18 Apr 2007 09:21:38 -0500, "fniles" <fn****@pfmail.comwrote:
¤ 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 ?
I'm not sure how you're monitoring the application pool but there shouldn't be any difference. The
application pool for the app on your machine will not reflect usage from clients on other machines.
If you're monitoring the Jet Roster, then that's a bit different since it will reflect all the
connections to the database.
Paul
~~~~
Microsoft MVP (Visual Basic)
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
18 posts
views
Thread by Rob Nicholson |
last post: by
|
1 post
views
Thread by Lenny Shprekher |
last post: by
|
5 posts
views
Thread by Zlatko Matić |
last post: by
|
7 posts
views
Thread by Mrinal Kamboj |
last post: by
|
16 posts
views
Thread by crbd98 |
last post: by
|
3 posts
views
Thread by fniles |
last post: by
| | |
15 posts
views
Thread by Sylvie |
last post: by
|
6 posts
views
Thread by TheSteph |
last post: by
| | | | | | | | | | | | | | | | | | | |
