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    
 
   
isladogs
post Sep 1 2019, 11:42 AM
Post#21


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


Bear in mind that I haven't read the entire thread in detail.
As the linked files are 'live', in theory it could do whatever you want.
However updating after any Excel file is modified is likely to be a performance hog causing the Access app to slow to a crawl.
Depending on the frequency these files change you might do better to link the Excel files to an intermediary Access app as I do.
Then run that app at specified intervals during the day to update the tables in the backend of the main app.
Doing that should minimise any slowdowns caused by processing changes from Excel

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Paul Ked
post Sep 1 2019, 12:13 PM
Post#22



Posts: 13
Joined: 30-August 19



Again, thanks, but that is a million miles away from my original post.
Go to the top of the page
 
isladogs
post Sep 1 2019, 02:19 PM
Post#23


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


LOL...
I agree with you but it seemed a logical continuation from your previous post.

However going back to the original question, as the links already exist using the approach I'm suggesting, I would expect the procedure to be faster as there is no need for the CPU to make a new connection each time.


--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
Paul Ked
post Sep 1 2019, 02:51 PM
Post#24



Posts: 13
Joined: 30-August 19



Thank you, I'll look into it thumbup.gif
Go to the top of the page
 
Paul Ked
post Sep 1 2019, 08:05 PM
Post#25



Posts: 13
Joined: 30-August 19



Conclusion

I trailed the system last night and got some results...

I used the rs.Open method on one tablet and cn.Execute on the other. The average login times (the time it took to connect to the db, delete, insert and read a table and update the data within the spreadsheet) were 1.49secs for the cn.Execute and 1.33secs for rs.Open. Operators of the system were enthusiastic, but we'll see what it is like on a busy Friday!

Many thanks uarulez2.gif to all of you for your input, but especially to DB Guy for the DROP and then SELECT * INTO suggestion.

As far as SQL Server goes, I couldn't get it to SELECT * INTO (as my post 03:48 AM) so I did tried it this way:

CODE
Sub ReadWrite(Clrk1 As String, Clrk2 As String)
    Dim cn As Object, rs As ADODB.Recordset, rw As Long, tm As Long
    frmWork.Label2 = "Reading/Writing DataBase..."
    DoEvents
    Set cn = CreateObject("ADODB.Connection")
    Set rs = New ADODB.Recordset
    tm = Timer
    cn.Open "Driver={SQL Server};Server=ACERLAPTOP;Database=ePoSDB;"
    Debug.Print Timer - tm
    If Clrk1 <> 0 Then
        With Sheets("DB" & Clrk1)
            cn.Execute "DELETE FROM dbo.Clerk" & Clrk1
            rw = 2
            Do Until .Cells(rw, 1) = ""
                cn.Execute "insert into dbo.CLERK" & Clrk1 & " (id, Item, Price, Dept) values ('" _
                    & rw - 1 & "', '" & .Cells(rw, 1) & "', '" & .Cells(rw, 2) & "', '" & .Cells(rw, 3) & "')"
                rw = rw + 1
            Loop
        End With
    End If
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM dbo.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 from above, I put a timer to check the connection time to the db as it was taking a long time, here are the times taken:

6.168945313
6.079101563
6.428955078
6.918945313

So I rebooted and ran nothing but excel and got:

4.488037109
3.729003906
3.612060547
3.75390625
4.148925781
5.740966797
4.940917969
4.718017578
4.157958984
4.023925781

I searched the web, but couldn't find anything on connection times so I have put SQL Server in the bottom drawer for now. I may have a play with it when I have a bit of spare time on my hands reading.gif

Again, thanks all

Best regards

Paul Ked
Go to the top of the page
 
theDBguy
post Sep 1 2019, 09:27 PM
Post#26


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


Hi Paul. Thanks for the update. Continued success 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
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    16th November 2019 - 07:47 PM