| Groups | Blog | Home |
vb.net data : Connection pooling question
I don't think there are any system counters exposed by the JET provider or
by OLE DB to monitor the CP. If you exhaust the pool you should get a timeout exception. However, (and Ginny please correct me here), if you're working with JET and an ASP application, you've got your wires crossed. JET is not designed to provide data for more than one user. Sure, you can share a JET .MDB database over a LAN, but each user gets its own JET engine to access the file. Using it in a web application that requires one JET engine to access the data is problematic at best. I suggest using a DBMS designed for the web--SQL Express. hth -- William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker 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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message news:ejDHZzGgHHA.4368@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 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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message news:%23uACzGHgHHA.4260@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. [quoted text, click to view] > > 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. [quoted text, click to view] > 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.com> wrote in message > news:u2%23Nf5GgHHA.3388@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" <fniles@pfmail.com> wrote in message >> news:ejDHZzGgHHA.4368@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. [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message news:u2%23Nf5GgHHA.3388@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" <fniles@pfmail.com> wrote in message > news:ejDHZzGgHHA.4368@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. [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message news:OlI3%236HgHHA.4804@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" <fniles@pfmail.com> wrote in message > news:%23uACzGHgHHA.4260@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.com> wrote in message >> news:u2%23Nf5GgHHA.3388@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" <fniles@pfmail.com> wrote in message >>> news:ejDHZzGgHHA.4368@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.com> schreef in bericht news:u2%23Nf5GgHHA.3388@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] >I don't think there are any system counters exposed by the JET provider or >by OLE DB to monitor the CP. > If you exhaust the pool you should get a timeout exception. > However, (and Ginny please correct me here), if you're working with JET > and an ASP application, you've got your wires crossed. JET is not designed > to provide data for more than one user. Sure, you can share a JET .MDB > database over a LAN, but each user gets its own JET engine to access the > file. Using it in a web application that requires one JET engine to access > the data is problematic at best. I suggest using a DBMS designed for the > web--SQL Express. > > hth > > -- > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest books: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and > Hitchhiker's Guide to SQL Server 2005 Compact Edition > > ----------------------------------------------------------------------------------------------------------------------- > "fniles" <fniles@pfmail.com> wrote in message > news:ejDHZzGgHHA.4368@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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message
news:OUmc0jKgHHA.2640@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.com> wrote in message > news:OlI3%236HgHHA.4804@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" <fniles@pfmail.com> wrote in message >> news:%23uACzGHgHHA.4260@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.com> wrote in message >>> news:u2%23Nf5GgHHA.3388@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" <fniles@pfmail.com> wrote in message >>>> news:ejDHZzGgHHA.4368@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 >>>>> ? >>>>>
Thank you.
[quoted text, click to view] >If the cause of your problem is the CP What did you mean by CP ?[quoted text, click to view] >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 [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message
news:Oroa83QgHHA.3460@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" <fniles@pfmail.com> wrote in message > news:OUmc0jKgHHA.2640@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.com> wrote in message >> news:OlI3%236HgHHA.4804@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" <fniles@pfmail.com> wrote in message >>> news:%23uACzGHgHHA.4260@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
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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message
news:ego1grRgHHA.3960@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.com> wrote in message > news:Oroa83QgHHA.3460@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" <fniles@pfmail.com> wrote in message >> news:OUmc0jKgHHA.2640@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.com> wrote in message >>> news:OlI3%236HgHHA.4804@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:
[quoted text, click to view]
On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fniles@pfmail.com> wrote: ¤ 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.aspx?scid=kb;EN-US;198755 Paul ~~~~
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. [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message
news:ulZ4z6UgHHA.1388@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" <fniles@pfmail.com> wrote in message > news:ego1grRgHHA.3960@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.com> wrote in message >> news:Oroa83QgHHA.3460@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" <fniles@pfmail.com> wrote in message >>> news:OUmc0jKgHHA.2640@TK2MSFTNGP06.phx.gbl... >>>> Thank you. >>>> I do use try/catch, but I get the "Unspecified error". >>>>
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. [quoted text, click to view] "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message news:gk4c23hbbps8kdcbv6dha9t8qeoapa3d2j@4ax.com... > On Mon, 16 Apr 2007 16:18:56 -0500, "fniles" <fniles@pfmail.com> wrote: > > ¤ 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.aspx?scid=kb;EN-US;198755 > > > Paul > ~~~~ > Microsoft MVP (Visual Basic)
[quoted text, click to view]
On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fniles@pfmail.com> wrote: ¤ 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 ~~~~
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. [quoted text, click to view] "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message news:798c231c4te0auv2to19u1rjopgeo1f4jf@4ax.com... > On Wed, 18 Apr 2007 08:37:33 -0500, "fniles" <fniles@pfmail.com> wrote: > > ¤ 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. [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message
news:ulZ4z6UgHHA.1388@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" <fniles@pfmail.com> wrote in message > news:ego1grRgHHA.3960@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.com> wrote in message >> news:Oroa83QgHHA.3460@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" <fniles@pfmail.com> wrote in message >>> news:OUmc0jKgHHA.2640@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
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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message
news:%23zXJj4bgHHA.2400@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.com> wrote in message > news:ulZ4z6UgHHA.1388@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" <fniles@pfmail.com> wrote in message >> news:ego1grRgHHA.3960@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
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 ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "fniles" <fniles@pfmail.com> wrote in message news:ODZf9tegHHA.4188@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. [quoted text, click to view] > > 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). [quoted text, click to view] >
> "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message > news:%23B715IegHHA.1220@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" <fniles@pfmail.com> wrote in message >> news:%23zXJj4bgHHA.2400@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.com> wrote in message >>> news:ulZ4z6UgHHA.1388@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?
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 ? [quoted text, click to view] > Each application gets its own pool. Two applications running on the same If for example application A and B uses the same database (either Access or> machine in different process spaces get their own pool--even if the > ConnectionString is the same. 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 ? [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message
news:%23B715IegHHA.1220@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" <fniles@pfmail.com> wrote in message > news:%23zXJj4bgHHA.2400@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.com> wrote in message >> news:ulZ4z6UgHHA.1388@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" <fniles@pfmail.com> wrote in message >>> news:ego1grRgHHA.3960@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
[quoted text, click to view] > 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) : [quoted text, click to view] "William (Bill) Vaughn" <billva@NoSpamAtAll_betav.com> wrote in message news:uH81BlfgHHA.2396@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" <fniles@pfmail.com> wrote in message > news:ODZf9tegHHA.4188@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.com> wrote in message >> news:%23B715IegHHA.1220@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" <fniles@pfmail.com> wrote in message >>> news:%23zXJj4bgHHA.2400@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 some |