UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Reading/writing To Access From Excel Vba, Access 2016    
 
   
Paul Ked
post Aug 30 2019, 09:27 AM
Post#1



Posts: 13
Joined: 30-August 19



Hi there!

Please excuse me if I have posted in the wrong place, but it is my first post! I'm very happy to be advised on your 'local' rules.

I am writing an app in excel for an epos system using access as a 'holder' for temporary data and to store all the transactions as a journal.

I have been experimenting with different methods of reading/writing to the access tables over a network but would love some advice as my methods seem a bit 'clunky' and are inconsistent (sometimes takes under a second, other times 12 seconds!)

The following two methods seem to be the most successful:

Method 1 (averages 1.59secs):

CODE
Sub ReadWrite(Clrk1 As String, Clrk2 As String)
    Dim cn As Object, rs As ADODB.Recordset
    frmWork.Label2 = "Reading/Writing DataBase..."
    DoEvents
    Set cn = CreateObject("ADODB.Connection")
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Sheet3.Cells(2, 8)
    If Clrk1 <> 0 Then
        cn.Execute "DELETE FROM Clerk" & Clrk1
        cn.Execute "INSERT INTO Clerk" & Clrk1 & " (fdItem, fdPrice, fdDept, fdSpare) SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
            & Sheet3.Cells(3, 8) & "]." & "[DB" & Clrk1 & "$]"
    End If
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM Clerk" & Clrk2, cn, adOpenForwardOnly, adLockReadOnly
    With Sheet19
        .Cells.Clear
        .Range("A1").CopyFromRecordset rs
    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub


Method 2 (averages 1.32secs):

CODE
Sub ReadWrite1(Clrk1 As String, Clrk2 As String)
    Dim cn As Object, rs As ADODB.Recordset
    frmWork.Label2 = "Reading/Writing DataBase..."
    DoEvents
    Set cn = CreateObject("ADODB.Connection")
    Set rs = New ADODB.Recordset
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Sheet3.Cells(2, 8)
    rs.CursorLocation = adUseClient
    If Clrk1 <> 0 Then
        rs.Open "DELETE FROM Clerk" & Clrk1, cn, adOpenForwardOnly, adLockReadOnly
        rs.Open "INSERT INTO Clerk" & Clrk1 & " (fdItem, fdPrice, fdDept, fdSpare) SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
            & Sheet3.Cells(3, 8) & "]." & "[DB" & Clrk1 & "$]", cn, adOpenForwardOnly, adLockReadOnly
    End If
    rs.Open "SELECT * FROM Clerk" & Clrk2, cn, adOpenForwardOnly, adLockReadOnly
    With Sheet19
        .Cells.Clear
        .Range("A1").CopyFromRecordset rs
    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub


As can be seen, the only difference is the cn.Execute v rs.Open.

Is there a faster/better way to approach this? Especially with the DELETE FROM and INSERT INTO, is there an OVERWRITE?

Any help would be much appreciated

Thanks in advance

Paul Ked
Go to the top of the page
 
theDBguy
post Aug 30 2019, 09:39 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul. Welcome to UA! welcome2UA.gif

Another thing you could try just to see if it'll be any better is to delete the table in Access first and then use a single Make-Table query to create the table again and insert the records at the same time. I can't promise it will run faster but would be interesting to find out.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 09:48 AM
Post#3



Posts: 13
Joined: 30-August 19



Thank you!

Interesting, I'll give it a go
Go to the top of the page
 
GroverParkGeorge
post Aug 30 2019, 09:54 AM
Post#4


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


"...are inconsistent (sometimes takes under a second, other times 12 seconds!)"

I'm not sure that is relevant to the actual method(s) you illustrate. Why? Because other factors impact performance across a network. Things like how much network traffic happens to occur at the same time.E.g. many other users downloading or uploading files at the same time. Things like other processes on your computer potentially consuming resources. E.g. Outlook reaching out to update your email, or something like an AV running a scan, or perhaps a backup process kicking off. Networks do tend to fluctuate for a lot of reasons, in other words, some obvious, some less so. If you can run repeated tests and get cumulative results showing one method CONSISTENTLY outperforms other methods after many trials, then you might be able to pick a preferred option.

You note that one of your methods seems to average about a quarter of a second faster than the other. If you didn't have a timer on it, would you even be able to detect that difference? Will you ever have enough records to manage so that the difference does even become noticeable?

I'd try theDBGuy's suggestion as well.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 10:25 AM
Post#5



Posts: 13
Joined: 30-August 19



Hi there.

I tried:
CODE
Sub ReadWrite3()
    Dim cn As Object, rs As ADODB.Recordset, tm As Long
    tm = Timer
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Q:\Epos2.accdb"
    cn.Execute "DROP TABLE Clerk1"
    cn.Execute "CREATE TABLE Clerk1 ([Item] text(20) WITH Compression, " & _
        "[Price] currency, " & _
        "[Dept] text(20) WITH Compression, " & _
        "[Spare] text(20) WITH Compression)"
    cn.Execute "INSERT INTO Clerk1 (fdItem, fdPrice, fdDept, fdSpare) SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" _
        & Sheet3.Cells(3, 8) & "].[DB1$]"
    cn.Close
    Set cn = Nothing
    Debug.Print Timer - tm
End Sub


but it averaged 0.36 secs slower, which is encouraging as I couldn't work out how to create the table with data (still had to use the INSERT INTO)

Thanks
Go to the top of the page
 
theDBguy
post Aug 30 2019, 10:29 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul. How about this one?
CODE
Sub ReadWrite4()
    Dim cn As Object, rs As ADODB.Recordset, tm As Long
    tm = Timer
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Q:\Epos2.accdb"
    cn.Execute "DROP TABLE Clerk1"
    cn.Execute "SELECT * INTO Clerk1 FROM [Excel 8.0;HDR=YES;DATABASE=" _
        & Sheet3.Cells(3, 8) & "].[DB1$]"
    cn.Close
    Set cn = Nothing
    Debug.Print Timer - tm
End Sub
(untested)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 11:10 AM
Post#7



Posts: 13
Joined: 30-August 19



notworthy.gif

That cuts around .3s off the original, which is huge when people are waiting!

Many thanks
Go to the top of the page
 
theDBguy
post Aug 30 2019, 11:19 AM
Post#8


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul. Glad we could assist. Please keep us posted if you find a better way. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 12:10 PM
Post#9



Posts: 13
Joined: 30-August 19



Thanks DB Guy.

GP George.

I appreciate what you are saying but there are things I hadn't pointed out. The network is a dedicated WiFi network just for the ePoS, with no internet connection, so the traffic is minimal. It is, however, susceptible to interference, if I could hard-wire the Ethernet it would be quicker... but that's difficult with a tablet on the move. Even on the master, where the db is on the same machine, it can be very slow opening the connection to the db, which is why I asked if there was a difference between the cn. and rs. methods. The tablets and pc used are dedicated to the ePoS too, I've ripped out all Windows 'Stuff and Nonsense' that isn't needed and optimised them for Excel & Access, it is, after all, a 'system' for one job.

When an operator is using the system, this procedure and others that look up the database can be called many times during a transaction. Even 5 x 0.25 is a very long time when you are working in a busy bar, and users get frustrated by trying to key in items before the code has stopped running. I have put in measures to stop this from being a problem, but I'd sooner speed things up rather than cause frustration.

I think we all, as developers (including the big guys!), get lazier as memory and processing power gets better and faster, but that shouldn't stop us trying to be as energy and time efficient as possible... 'think green' should be everyone's priority!

Thanks to all for help cheers.gif I'll try to reciprocate soon, if i can shrug.gif
Go to the top of the page
 
GroverParkGeorge
post Aug 30 2019, 12:21 PM
Post#10


UA Admin
Posts: 36,033
Joined: 20-June 02
From: Newcastle, WA


Thanks for the background. That explains it.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 12:26 PM
Post#11



Posts: 13
Joined: 30-August 19



hat_tip.gif
Go to the top of the page
 
theDBguy
post Aug 30 2019, 12:26 PM
Post#12


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


Hi Paul. Since you said this is for a WiFi system, is there any chance you could use SQL Server Express instead of Access? Access wasn't designed to work well with LAN or WiFi networks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Paul Ked
post Aug 30 2019, 01:15 PM
Post#13



Posts: 13
Joined: 30-August 19



That is something I hadn't thought about. I am new to using databases (and SQL) and appreciate any help, so thanks and I'll look into it over the weekend, it's time for a guiness.gif now, have a good weekend and I'll report my findings. Cheers
Go to the top of the page
 
theDBguy
post Aug 30 2019, 01:20 PM
Post#14


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


Happy Holiday!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Aug 30 2019, 04:11 PM
Post#15



Posts: 6,150
Joined: 11-November 10
From: SoCal, USA


if this were two Access dbs, rather than 1 Excel and 1 Access, probably the first advice would be to open a persistent connection to the receiving db, from the sending db - rather than opening/closing a connection repeatedly. i don't know if that can be done from Excel, or with your setup, or if it would help speedwise...just a thought...

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Paul Ked
post Aug 31 2019, 09:48 PM
Post#16



Posts: 13
Joined: 30-August 19



Tina t.

Thanks for the input, but when an excel macro has finished running the connection goes out of scope, so it wouldn't be recognised the next time round. There may be a way round this but I don't think it would be very efficient, especially with multiple connections open from different users.

DBGuy

I've installed (and almost got my head around the basics!) SQL Server Express, it seems to be a very powerful tool! However, I'm struggling to get the correct syntax to write the sheet data to the db.

CODE
        cn.Execute "SELECT * INTO Clerk" & Clrk1 & " FROM [Excel 8.0;HDR=YES;DATABASE=" _
            & Sheet3.Cells(3, 8) & ".DB" & Clrk1 & "$]"


which gives the error Invalid object name 'Excel 8.0;HDR=YES;DATABASE=Q:\ePoSSlave.xlsm.DB2$'

I've searched the net but can't find an example, any ideas?

Best regards

Paul Ked
Go to the top of the page
 
isladogs
post Sep 1 2019, 12:27 AM
Post#17


UtterAccess VIP
Posts: 1,795
Joined: 4-June 18
From: Somerset, UK


Maybe I'm missing something here.
Why not make a permanent link to the Excel file and run an append statement/query in Access...or if you must ...a make table equivalent.
If you do that no ADODB connection is required

Agree about the significant risks using Access with a WiFi network. Using a hardwired LAN is fine. I think the DB Guy meant doesn't work well with a WAN.
If it must be WiFi then SQL Server is a much safer option.

EDIT
The best reference for connection strings is https://www.connectionstrings.com/

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Paul Ked
post Sep 1 2019, 07:14 AM
Post#18



Posts: 13
Joined: 30-August 19



Can you make a permanent link to multiple Excel files from Access?

The basic logic of one of the operations is:

User logs on to a pc/tablet
Check if they are logged on anywhere else
if so, get their current transactions and log them out of that pc/tablet
save their transactions to the pc/tablet they are now logged on to.

Simple, but it needs to be fast!

There are many other operations between excel and access like product/price, operators, journals etc.
Go to the top of the page
 
isladogs
post Sep 1 2019, 08:18 AM
Post#19


UtterAccess VIP
Posts: 1,795
Joined: 4-June 18
From: Somerset, UK


Yes you can have multiple Excel files linked to Access and treated like any linked table. Similarly you can have as many linked text/CSV files as you want.
For example one of my schools apps contains links to 30 external CSV files which are remotely updated each night using code from the Access app itself.
The data in each is then processed and used to update tables in another Access app.
All of this (and much more) is done automatically using a Windows Scheduled task with no user intervention required.

No ADODB connection strings are required.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Paul Ked
post Sep 1 2019, 10:56 AM
Post#20



Posts: 13
Joined: 30-August 19



Ok, thanks, I understand that. But would it work For my application:

So, as my front end on the remote devices is Excel, wouldn’t I still have to look at the database for transactions not yet completed? Or could the dB update every instance of excel immediately after any user hits a button on their tablet?
This post has been edited by Paul Ked: Sep 1 2019, 11:13 AM
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 03:16 AM