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

UtterAccess VIP
Posts: 2,241
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

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
post Sep 1 2019, 02:19 PM

UtterAccess VIP
Posts: 2,241
Joined: 4-June 18
From: Somerset, UK

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

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

Posts: 13
Joined: 30-August 19


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:

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..."
    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
        End With
    End If
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM dbo.Clerk" & Clrk2, cn, adOpenForwardOnly, adLockReadOnly
    With Sheet19
        .Range("A1").CopyFromRecordset rs
    End With
    Set rs = Nothing
    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:


So I rebooted and ran nothing but excel and got:


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
post Sep 1 2019, 09:27 PM

UA Moderator
Posts: 77,718
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    2nd April 2020 - 07:37 AM