Can you keep a connection to a DB open?

[paulked]

Hi all

I'm trying to speed up an application that reads and writes to an Access DB. The app is used by 4 tablets writing to the DB on a server via WiFi, so it is not the fastest of beasts to work with.

Would it be possible to keep the connection open all the time to speed things up, or is there another way?

The 4 routines for comms are:

Option Explicit


Sub Trans2DB(Clrk As String)
    Dim cn As ADODB.Connection, dbPath As String, dbWb As String, scn As String, dsh As String, dsql As String, ssql As String
    Set cn = CreateObject("ADODB.Connection")
    dbPath = Application.ActiveWorkbook.Path & "\Epos1.accdb"
    dbWb = Application.ActiveWorkbook.FullName
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    dsh = "[DB" & Clrk & "$]"
    cn.Open scn
    dsql = "DELETE FROM Clerk" & Clrk
    cn.Execute dsql
    ssql = "INSERT INTO Clerk" & Clrk & " (fdItem, fdPrice, fdDept, fdSpare) "
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    cn.Execute ssql
    cn.Close
    Set cn = Nothing
End Sub

Sub ReadFromDB(Clrk As String)
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, sQRY As String, dbPath As String
    dbPath = Application.ActiveWorkbook.Path & "\Epos1.accdb"
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    sQRY = "SELECT * FROM Clerk" & Clrk
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cn, adOpenStatic, adLockReadOnly
    Application.ScreenUpdating = False
    With Sheet19
        .Cells.Clear
        .Range("A1").CopyFromRecordset rs
    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub


Sub Trans2Ledger()
    Dim cn As ADODB.Connection, dbPath As String, dbWb As String, scn As String, dsh As String, dsql As String, ssql As String
    Set cn = CreateObject("ADODB.Connection")
    dbPath = "Y:\Epos1.accdb"
    dbWb = Application.ActiveWorkbook.FullName
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    dsh = "[Ledger$]"
    cn.Open scn
    ssql = "INSERT INTO Ledger (fdClerk, fdProduct, fdPrice, fdDept, fdSpare) "
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    cn.Execute ssql
    cn.Close
    Set cn = Nothing
End Sub


Sub Trans2Tills()
    Dim cn As ADODB.Connection, dbPath As String, dbWb As String, scn As String, dsh As String, dsql As String, ssql As String
    Set cn = CreateObject("ADODB.Connection")
    dbPath = Application.ActiveWorkbook.Path & "\Epos1.accdb"
    dbWb = Application.ActiveWorkbook.FullName
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    dsh = "[Till1$]"
    cn.Open scn
    dsql = "DELETE FROM Till1Reg"
    cn.Execute dsql
    ssql = "INSERT INTO Till1Reg (fdReg) "
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
    cn.Execute ssql
    cn.Close
    Set cn = Nothing
End Sub

Many thanks and best regards

Paul Ked

[Bob Phillips]

I always open the connection when the app is started, and close it on shutdown. Connecting takes time so it makes sense. You would need to check that the connection is still open, a simple read would do it, and restart if it has dropped out.

[paulked]

Many thanks for that, I'm on the case!



Paul Ked

[snb]

Why not ?

Sub ReadFromDB(Clrk As String)
  Sheet19.Cells.Clear

  With createobject("ADODB.Recordset") 
    .Open "SELECT * FROM Clerk" & Clrk, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ActiveWorkbook.Path & "\Epos1.accdb;" 
    Sheet19.Range("A1").CopyFromRecordset .datasource 
  End With 
End Sub

[paulked]

I like it

I was having tremendous trouble getting XL and Acc to communicate (the right syntax and provider combination). So when it actually did transfer data I just copied it and ran away!

Thanks, I'm streamlining everything later today.



Paul Ked

[paulked]

Hi Guys

Many thanks for your input. I've got rid of all the mess

Option Explicit


Sub Trans2DB(Clrk As String)
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\Epos1.accdb"
        .Execute "DELETE FROM Clerk" & Clrk
        .Execute "INSERT INTO Clerk" & Clrk & " (fdItem, fdPrice, fdDept, fdSpare) " _
                 & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Y:\EPoSTab1.xlsm].[DB" & Clrk & "$]"
    End With
End Sub


Sub ReadFromDB(Clrk As String)
    Sheet19.Cells.Clear
    With CreateObject("ADODB.Recordset")
        .Open "SELECT * FROM Clerk" & Clrk, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\Epos1.accdb;"
        Sheet19.Range("A1").CopyFromRecordset .DataSource
    End With
End Sub


Sub Trans2Ledger()
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\Epos1.accdb"
        .Execute "INSERT INTO Ledger (fdClerk, fdProduct, fdPrice, fdDept, fdSpare) " _
                 & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Y:\EPoSTab1.xlsm].[Ledger$]"
    End With
End Sub


Sub Trans2Tills()
    With CreateObject("ADODB.Connection")
        .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\Epos1.accdb"
        .Execute "DELETE FROM Till1Reg"
        .Execute "INSERT INTO Till1Reg (fdReg) SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=Y:\EPoSTab1.xlsm].[Till1$]"
    End With
End Sub

and it's running like an express train rather than a steam engine!

Thanks again



Paul Ked