PDA

View Full Version : [SOLVED] Can you keep a connection to a DB open?



paulked
11-30-2017, 04:26 PM
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
11-30-2017, 06:59 PM
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
11-30-2017, 07:03 PM
Many thanks for that, I'm on the case!

:beerchug:

Paul Ked

snb
12-01-2017, 01:10 AM
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
12-01-2017, 02:54 AM
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.

:beerchug:

Paul Ked

paulked
12-01-2017, 06:25 AM
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

:grouphug:

Paul Ked