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

[Gasman]

On computer now.

Well the bank had to upgrade JUST the Excel license to get the extra rows in the newer version, as they exceeded the 65K rows quite quickly. the rest of our Office suite was was of an earlier version.?

What used to make me laugh was that they would copy the data to a new workbook and give it a different name that we then had to use until the next corruption, rather than rename the corrupted workbook and give the new workbook the same name.

Negated the fact that I would pin the filename in Excel and use that each day ?

 

[The_Doc_Man]

If I may say it, a bank that would hold that much data in an Excel worksheet would probably have issues with requirements compliance. I can only imagine that your 65K rows are personal-account related data, which means data subject to fiduciary regulation. The security level of an Excel sheet ain't very high.

 

[Gasman]

If I may say it, a bank that would hold that much data in an Excel worksheet would probably have issues with requirements compliance. I can only imagine that your 65K rows are personal-account related data, which means data subject to fiduciary regulation. The security level of an Excel sheet ain't very high.

Doc, it was all for personal PPI requirements? As for the rules?, well I was just a pleb in admin

 

[Isaac]

If I may say it, a bank that would hold that much data in an Excel worksheet would probably have issues with requirements compliance. I can only imagine that your 65K rows are personal-account related data, which means data subject to fiduciary regulation. The security level of an Excel sheet ain't very high.

I've worked for banks in one capacity or another for many years, and discovered that the vast vast majority of their data has nothing to do with accounts, balances etc. It's just all the rest of the crap produced by various processes. Couple years ago I worked for a department in Wells Fargo that handled all the call center data. Not even just call center, but rows and rows of data to represent every single thing that happened to every call that came into the bank anywhere on any of their numbers, and to represent every leg of that call journey throughout the IVR. Billions upon billions upon billions of records just to represent things like calls, call recordings, and even data that came out of systems as obscure as a Google hardware product that they used to power their internal policy searches.

But also, we generally considered the security of our Network shares to be the only thing that was required, which was IT's responsibility to maintain and audit that annually. No matter how confidential the data was, the only people who had access to certain folders were people with a need to know about all content contained therein. Within those drives we could store just about anything, although I was given some stricter requirements when it came to things like debit card numbers, but that was many years ago and it was rare that I came across any of that type of data.

So, while I often hear people talk about the security of Excel or access relative to the sensitivity level of the data, in my own experience that is usually not the comparison that management makes (at least in a large corporate setting). They usually manage things by Network folder permissions, and once they have a folder that they believe has the perfect permissions on it, you could have the data in a text file and they really wouldn't care.

Of course, with every passing year the regulatory environment becomes stricter and probably doubles the bureaucracy required to manage all this..

 

[Pat Hartman]

I'm surprised that by changing a setting, Access can update a linked Excel table. In the early days of Access, linked Excel was always updateable but some years ago MS lost a lawsuit and rather than license the technology, they blocked Access from being able to update linked spreadsheets. Has the restraining order been overturned?

Anyway, this is a lot of trouble to go through when you should be able to link to the SharePoint list directly. The data in question seems to be a multi-value field and I'm pretty sure there is Access SQL that will return the string you want. I don't know it because I don't use multi-value fields but someone should be able to post the SQL you need.

 

[Isaac]

Anyway, this is a lot of trouble to go through when you should be able to link to the SharePoint list directly. The data in question seems to be a multi-value field and I'm pretty sure there is Access SQL that will return the string you want

I agree, my problem is that here when we connect Access to Sharepoint, ALL operations of any kind are painfully, ridiculously, slow. So I have to experiment a bit with different approaches....but yeah, I agree, this all seems kind of silly - and is. Some day, I have faith, that our department will be given access to a SQL Server. Until then, it's challenging.

 

[Pat Hartman]

If the Excel link to SharePoint is live and you are not having any problem with speed, I'm surprised that the direct link is such a problem. How many rows in the list with the multi-value field? Is that the same number of rows as are in the spreadsheet?

When Access performs a heterogeneous join (tables from different source databases), it brings down ALL rows from the non-ACE/Jet tables and performs the join locally.

 

[Isaac]

If the Excel link to SharePoint is live and you are not having any problem with speed, I'm surprised that the direct link is such a problem. How many rows in the list with the multi-value field? Is that the same number of rows as are in the spreadsheet?

When Access performs a heterogeneous join (tables from different source databases), it brings down ALL rows from the non-ACE/Jet tables and performs the join locally.

Scenario 1: Every time I need to look something up from the sharepoint list, or have a query or recordsource open 'continually', involve the actual Linked Table Object which is the actual Sharepoint list

Scenario 2: Link to the Excel table for ALL of those lookups, and only refresh the data occasionally (by Automation to refresh the data connection in the linked workbook), then continue querying the linked Excel table.

Scenario 2 is 100 times faster. There are only a few hundred records, but in some cases 7-9 People fields, which is pushing the limit of effectively being able to ODBC link to it (as MS states at 10 people fields, the whole linking becomes untenable).

 

[CJ_London]

I'm surprised that by changing a setting, Access can update a linked Excel table. In the early days of Access, linked Excel was always updateable but some years ago MS lost a lawsuit and rather than license the technology, they blocked Access from being able to update linked spreadsheets. Has the restraining order been overturned?

No. But we are not talking about linked spreadsheets, we are talking about a query. Mute difference I agree, but you cannot specify IMEX using transferspreadsheet and with the query you still do not have full editing functionality to delete rows/columns, tho' you can add rows and change content as values, but not cells containing a formula (which only applies to those cells, not the whole column)

Benefits are limited but it has it's uses. I'm currently doing some what-if's on a number of spreadsheets for a client where I need to change some excel based values, easier to do it through the query and just have the one app open than to copy/paste or import the data, then have to export it all again.

 

[NauticalGent]

I came across this issue yesterday and naturally looked here for an answer. I haven't tried the Query method that CJ mentioned yet, but I DID go into the connection settings via the Linked Table Manager and changed the connection string to IMEX=0 and refreshed the link - no joy.

I found this bit of code on StackOverflow:

Dim i As Integer
Dim awc As WorkbookConnection
Dim c As OLEDBConnection

For i = 0 to ActiveWorkbook.Connections.Count
    Set awc = ActiveWorkbook.Connections.Item(i)
    Set c = awc.OLEDBConnection
    c.EnableRefresh = True
    c.BackgroundQuery = False
    c.Reconnect
    c.Refresh
    c.MaintainConnection = False
Next i

It wouldn't compile at first because I didn't have the MS Excel 16.0 Object Library referenced - but once I did, it tapped out on the highlighed line: "91 - Object variable or With Block variable not set"

I clearly need to declare ActiveWorkbook as something but I am unsure of what to do...any ideas?

 

[The_Doc_Man]

First, as a matter of habit (and of documentation), I always qualify objects with their app type.

I might have tried this:

Dim AWC as Excel.Workbook.Connection

Then I would instantiate them in the code, more or less like you did. Note that with code tags your highlights are not preserved so... which line tapped out?

 

[NauticalGent]

Morning Doc, it was this one: For i = 0 to ActiveWorkbook.Connections.Count

 

[The_Doc_Man]

I might instantiate another variable with

Dim AW As Excel.Workbook

and then in a separate line outside the loop,

Set AW = excelobject.ActiveWorkbook

Then use AW in the loop header. For that matter, since this ISN'T in the Excel forum, have you even instantiated an Excel app object in a context where you can see one? Because Excel isn't instantiated in the code you showed us.

 

[NauticalGent]

Set AW = excelobject.ActiveWorkbook wont compile: Variable not defined.

I am REALLY feeling like the slow kid in the back of the class

 

[Minty]

You need at least two objects, and all need creating and or setting

xlApp - The EXCEL application object
xlWkb - The Workbook Object

So to set it up would be

Dim xlApp as Object
Dim xlWkb as Object

Set xlApp = CreateObject("Excel.Application")
Set xlWkb = XlApp.Activeworkbook

This uses late binding so you don't need a specific reference set.

Move forwards a bit - you'll hear the teacher better

 

[Isaac]

I came across this issue yesterday and naturally looked here for an answer. I haven't tried the Query method that CJ mentioned yet, but I DID go into the connection settings via the Linked Table Manager and changed the connection string to IMEX=0 and refreshed the link - no joy.

I found this bit of code on StackOverflow:

Dim i As Integer
Dim awc As WorkbookConnection
Dim c As OLEDBConnection

For i = 0 to ActiveWorkbook.Connections.Count
    Set awc = ActiveWorkbook.Connections.Item(i)
    Set c = awc.OLEDBConnection
    c.EnableRefresh = True
    c.BackgroundQuery = False
    c.Reconnect
    c.Refresh
    c.MaintainConnection = False
Next i

It wouldn't compile at first because I didn't have the MS Excel 16.0 Object Library referenced - but once I did, it tapped out on the highlighed line: "91 - Object variable or With Block variable not set"

I clearly need to declare ActiveWorkbook as something but I am unsure of what to do...any ideas?

what is your goal here? just refresh all workbook connections?

 

[NauticalGent]

Thanks Minty, that got me over that particular hurdle. The end-state is to get the c.MaintainConnection = False to work. According to the S/O thread it is that property that is causing my issue.

Here is my new code:

Dim i As Integer
Dim xlApp as Object
Dim xlWkb as Object
Dim awc As WorkbookConnection
Dim c As OLEDBConnection

Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.ActiveWorkbook

'For i = 0 to ActiveWorkbook.Connections.Count '(this was original)
For i = 0 to xlApp.Connections.Count  'New Code
    Set awc = ActiveWorkbook.Connections.Item(i)
    Set c = awc.OLEDBConnection
    c.EnableRefresh = True
    c.BackgroundQuery = False
    c.Reconnect
    c.Refresh
    c.MaintainConnection = False
Next i

The new Error is 438-Object doesnt support this property or method, on the same line: For i = 0 to xlApp.Connections.Count

Move forwards a bit - you'll hear the teacher better

The would only fix the proximity part of the problem. The other bit I am resigned to I'm afraid...

 

[NauticalGent]

what is your goal here? just refresh all workbook connections?

Morning Isaac, see my reply to Minty!

 

[Minty]

You're getting closer

For i = 0 to xlWkb.Connections.Count 'New Code

 

[NauticalGent]

Appreciate your persistence and patience...

Made the changes and same line taps out, this time with 91-Object variable ow With block variable not set

Looking at this code after taking a lunch break, I don't see how it would work as written. At no time am I telling it to loop through the TableDefs collection nor am I giving it a name to the linked file.

For the record, the name of the linked file is WIP_DLA.xlsx, so shouldnt I be setting the xlWkb variable to the name of the file?

Did I mention I HATE working with Excel?