Database Connectivity Using Singleton Pattern

[ChloeRadshaw]

My application does a lot of database work to and from the database.

In Java I would create a database pool and have a thread kick off in the background which would close database connections after ten minutes or so.

At the moment I am having to open and close lots of database connections.

Is there any way to create a database connection and then force the connection to be closed after a set period of time?

I could then have one connection open to the database and one shared object and the connection could be opened if it had timed out.


Ideas??????

[Bob Phillips]

Do it the same way. Create a global variable for the connection, when you want to do a query, check if the connection is nothing or not, if so open the connection. Have an ontime macro to close the connection, resetting the macro every time you query (so that it gets 10 minutes of inactivity).

[ChloeRadshaw]

Do it the same way. Create a global variable for the connection, when you want to do a query, check if the connection is nothing or not, if so open the connection. Have an ontime macro to close the connection, resetting the macro every time you query (so that it gets 10 minutes of inactivity).

Good idea - Thank you

[ChloeRadshaw]

Presumably I cannot set a macro ti kick off every ten minutes..

I need to do now + ten minutes every time the macro is called

[Bob Phillips]

Yes you can, you use Ontime


Application.Ontime Now + TimeSerial(0, 10, 0), "myProc"

[Bob Phillips]

But as I said, you should also reset that Ontime call every time you query, so as to get 10 minutes BETWEEN db activity, not just shutting the connection down every 10 minutes.

[ChloeRadshaw]

But as I said, you should also reset that Ontime call every time you query, so as to get 10 minutes BETWEEN db activity, not just shutting the connection down every 10 minutes.

Thanks - However I do not understand how to do this.

If I have already registered one procedure to run:

Application.OnTime(TimeValue(getNextDatabaseConnectionCloseSweep()), "timerCloseConnection") and I want to reset this so ten minutes from nowthe function would be called how would this be done??

Would this not force the function to be called at the original time AND in ten minutes time.

[mdmackillop]

Just working on some code with 3 calls to a database so I've pinched your idea.


Option Explicit
Private Sub Workbook_Open()
OpenConnection
End Sub

Option Explicit
Public oConn As Object
Public CloseTime As Date

Sub OpenConnection()
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\Database\ValData2000.mdb"
CloseTime = Now + TimeValue("00:10:00")
Application.OnTime CloseTime, "CloseConnection"
End Sub

Sub CloseConnection()
oConn.Close
Set oConn = Nothing
'Debug code
[b35] = "Closed at " & Now
End Sub

Sub ExtendTime()
If oConn Is Nothing Then
OpenConnection
Else
'Remove previous close time
Application.OnTime EarliestTime:=CloseTime, _
Procedure:="CloseConnection", Schedule:=False
'Add new close time
CloseTime = Now + TimeValue("00:10:00")
Application.OnTime CloseTime, "CloseConnection"
End If
End Sub

Sub AddData()
Dim sSQL As String
Dim Sql As String
Dim Chk As Long
ExtendTime
Sql = [M2] & [M3] & [M4] & [M5] & [M6] & [M9] & [M11] & [M12] & [M13] & [M14] & [M15] & [M17]
sSQL = "UPDATE ValPropDet SET " & Sql & _
" WHERE (((ValPropDet.Record_Number)=" & [B27] & "));"
oConn.Execute sSQL
End Sub