Linked (Excel) Table - multiple users at a time unable to query

[Minty]

Have a look at this code - it takes a Excel Filename, I have fixed the sheet to sheet(1) but you could change it back to sSheet to take the worksheet name.

It formats the contents as a table. It uses late binding throughout.
Hopefully, this will get you where you need to be.

Public Sub XLFormatTable(sFile As String, sSheet As String, Optional bOpen As Boolean = True)

    '  On Error GoTo XLFormatTable_Error
    ' Late binding to avoid reference:
    Dim xlApp            As Object        'Excel.Application
    Dim xlWB             As Object        'Workbook
    Dim xlWS             As Object        'Worksheet
    Dim tbl              As Object
    Dim rng              As Object
    Dim iSheet           As Integer
    Debug.Print sFile, sSheet
    iSheet = 1
    ' Create the instance of Excel that we will use to open the temp book
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = bOpen
    Set xlWB = xlApp.Workbooks.Open(sFile)
    'Debug.Print xlWB.Name
    Set xlWS = xlWB.worksheets(iSheet)

    ' Format our temp sheet
    ' ************************************************** *************************

    xlApp.range("A1").Select

    With xlWS
        '        With .UsedRange
        '            .borders.LineStyle = xlContinuous
        '            .borders.ColorIndex = 0
        '            .borders.TintAndShade = 0
        '            .borders.Weight = xlThin
        '        End With
        '
        '        'format header 90 degree
        '        With .Range("i1:y1")
        '            .HorizontalAlignment = xlCenter
        '            .VerticalAlignment = xlBottom
        '            .WrapText = False
        '            .Orientation = 90
        '            .AddIndent = False
        '            .IndentLevel = 0
        '            .ShrinkToFit = False
        '            .ReadingOrder = xlContext
        '            .MergeCells = False
        '        End With
        '        .UsedRange.Rows.RowHeight = 15
        '        .UsedRange.Columns.AutoFit

        With xlWB.Sheets(iSheet)
            Set rng = .Cells(1, 1).CurrentRegion
        End With
        Set tbl = xlWS.ListObjects.Add(xlSrcRange, rng, , xlYes)
        tbl.TableStyle = "TableStyleMedium2"
        tbl.ShowTotals = False
        
        xlWS.Cells.EntireColumn.AutoFit
      
    End With
        
    xlWB.Save
    
    If Not bOpen Then
        xlApp.Workbooks.Close
        Set xlApp = Nothing
    Else
        xlApp.ActiveWindow.WindowState = xlMaximized
    End If
    
    
    On Error GoTo 0
    Exit Sub

XLFormatTable_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure XLFormatTable, line " & Erl & "."

End Sub

 

[NauticalGent]

Looks promising and I will have a look as soon as I get finished with the latest "big thing". Really appreciate your time...

 

[Minty]

Looks promising and I will have a look as soon as I get finished with the latest "big thing". Really appreciate your time...

You are more than welcome - I may have forgotten to include some Enumerations that I have set up globally - compiling it will soon tell you.

 

[CJ_London]

just curious - why not try the query? Only takes a minute

 

[Minty]

@CJ_London I think @NauticalGent really secretly loves Excel and wants to improve his skilzz.

Honest

 

[NauticalGent]

just curious - why not try the query? Only takes a minute

At first it was because I wanted to make a global solution that allows the users to open up the forms whose recordsource is based on the Linked Excel files. There are about 10 of them that get updated (exported from the Program of Record, SAP I believe) weekly. At no time is the data changed and since linked Excel files are read-only, it cut out a lot of the existing Empty/Delete table, Import Table process. But if some one has a form open that uses the file - either directly or via a query - everyone has to wait until the user closes the form. What kind of STUPID is THAT?!?! This should be a setting that can be toggled off and on easily. When I came across the MaintainConnection Property and that bit of code, I thought I had the fix.

When Minty chimed in, I was hoping he would figure out what I was missing but I am thinking that bit of code has to been run within Excel.

So that I am out of ideas, I will take the time to try your suggestion. My laziness wanted to avoid having to write 10 or so new queries and then change the applicable forms.

I will let you know how it turns out.

 

[Pat Hartman]

I use a template database to handle this type of downloaded data. You might need to create a batch file to do this so that it only happens once and everyone links to the template. Build a database that has the tables defined and indexed correctly and has the spreadsheets linked. Compact and save. Then if you build a special load database that you run nightly, that program opens and copies a new, empty copy of the template to the shared folder. They it runs the append queries to append the new data.

This avoids the sharing issue with Excel and it avoids the bloat issue with Access since you are rebuilding the template db every day. It only takes a couple lines of code and a batch file.

If you can't run the batch file on the server because Access isn't installed, you can try doing this from the first db that opens. It gets more complicated though. You have to delete the existing temp db so anyone who opens after you but before you're done gets an error. Then you copy the template as a different name, do the process, and rename the template to the common name. Then everyone else can open fine. The app that is running and did the rebuild might need to refresh the linked tables to see them. I've never done it this way so I don't know if the links break or if Access doesn't care as long as you don't reference the linked tables until the rebuild is complete.

 

[NauticalGent]

CJ, I made the Query as suggested and as pleased that it ran so quickly - unfortunately, the same issue persists. If I have the query open and someone else tries to open the same qurery on thier FE, they get the SoL message.

 

[The_Doc_Man]

See my discussion near the bottom of this thread's post #10. I believe the problem with Excel is that its file locks cannot be granular because its internal structure is self-limiting. Doesn't matter who or how you open it. Locked is locked. One of the "make a copy" solutions might help.

 

[CJ_London]

Something to try, not sure if it will work

Having created your query in sql, go to the query gui and change the recordset type from dynaset to snapshot. then save the query.

Or perhaps do this in your form instead - that might even work for a linked table

Note that IMEX has nothing to do with file locking, only which datatype a column of data will be interpreted as based on the first 8 rows. My point was that with IMEX=0, you can edit the excel file which is something you can't do with an excel linked table.

Another thought is to use ADO to create a disconnected recordset and set the form recordset to that.

 

[Pat Hartman]

If you have access to SQL Server, loading new versions of downloaded data can more easily be automated because SQL Server can run scheduled tasks and you don't have to worry about Access having to be available on a server so you can run a scheduled task.

 

[NauticalGent]

Just to wrap up this bit regarding the "MaintainConnection = False" property, I came back to this and ran this sub in Excel.

It ran with no issues (once a established the connection!) but still would not allow two users to open the linked file.

I and cutting bait and moving on. We have put in a request for an instance on a SQL server and will probably use SharePoint in the meantime. They told us it can take up to 3 months for the request to process.

At least I'm getting paid...

 

[Gasman]

I am probably shooting myself in the foot here but.....
I shared a workbook, and linked it to two different DBs, opened both and was able to open both linked tables.?
I was also able to open a query on both.?

All read only of course?

Wasn't the initial post about read only?

 

[NauticalGent]

Yeah, no idea how you pulled that off. If more than one user tries to open a form that uses a query based on the linked Spreadsheet, the second user gets the error...

 

[Gasman]

I'll try from another computer with a excel file on my NAS from two computers, when I get back from taking this passenger.

 

[Pat Hartman]

The problem is with Access, not Excel. Access does not play well with others.

In the mean time, you can import everything into Access. The only difficulty with that is what I mentioned and that is how to schedule the task. If you have a computer that is on 24x7, you can set up a batch job on it to recreate the temp file in the wee hours with the download drops. I have several clients that do it. The problem they occassionally run into is that the power goes off and so the desktop doesn't come back on or someone uses the desktop and leaves it in a state that keeps the batch file from running. But this is probably easier to organize for a temporary solution than SharePoint.

One thing you can do is to have the import send you an email that it successfully completed. I would send the emails to several people just to make sure that someone notices if they don't get the expected email.

 

[Gasman]

Yeah, no idea how you pulled that off. If more than one user tries to open a form that uses a query based on the linked Spreadsheet, the second user gets the error...

Ok, managed to get to two computers.
I selected an Excel file and Shared it.
I then created a new DB on each computer.

I linked the Excel file to each DB. I noticed that no other program other than Excel can be accessing the file when doing this.
I then created a query from the linked table on both computers.
I then created a form based on the above query on both computers.
I then opened form on each computer, without any issues.?

Bear in mind, all read only. Excel file is on a NAS box.

 

[CJ_London]

I think the issue is the users window ID, @Gasman, when you logged into each computer, did you log in as the same user? or different users?

 

[Gasman]

No,
I am Paul on one and SRVPH on the other. All my computers have access to the NAS boxes in the house.
When I tried on my personal laptop, although I tried two different DBs, I was obviously the same person.
That is why I waited, until I had access to two computers, one a laptop and the other a desktop.
The desktop was my old works server, so I left the username as it was.

 

[Isaac]

After pouring over the vast amount of (very good and educational) advice on this topic, combined with experience to-date, I kind of decided linked excel tables was a no-go, which aligns with what I had decided years ago, but revisited when creating this thread.
Now, I do have one db where I found it useful to 'copy' an excel file to the user's local and link from there, they know the data is not 100% up to date.
If I really found myself stuck with a situation where users were constantly updating an excel file with data that needed to go into access, I would probably choose a method where I did some programming in the excel file itself to export its data somewhere (flat file) and then suck it up into access as needed. This is where experience in both excel and access vba are priceless, sometimes you end up in an environment where you have to deal with both, like it or not. Excel has multiple Events which would all be possibilities to "re-export" the latest data. ThisWorkbook.AfterSave would probably be my top choice.