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
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