Linked Tables issue

[ClimoC]

Hi
Anyone else ever had this?

Get an accdb file (any kind). Import it to an Excel Workbook as a Linked Workbook (Data > 'Get External Data - From Access)

Data goes in. Lovely.

Go to your VBE Immediate window. Query an activecell.value (or any cell value)

Now hide the column / set the column width of that cell to 0

Now in the Immediate Window, query the value again

Zuh?!?! The rest of the table is fine, no links are broken, but the workbook suddenly can't get the range data.

This is a bit of a problem for one of my Linked-Data workbooks - it's supposed to fire SQL commands triggered by the Change event - but can't obtain information it needs if the column has been hidden / width set to zero

Ideas? Solutions?

Cross-posted here in Excel forum: http://www.mrexcel.com/forum/excel-...ccess-tables-vs-object-model.html#post3299904

 

[SydneyGeek]

Not sure but I think it's a 'feature' of data tables. I have seen similar behaviour before.
Why do you want to hide columns?

Denis

 

[ClimoC]

Morning Sydney

The reason being, is that rather than the crud that comes with Shared Workbooks, I set up an accdb linked table, then execute SQL commands to change values in the accdb based on the values found during the worksheet_change event

That part is fine - but the other major side of this is the Synchronisation part. CSV outputs are synchronised with the Worksheet.

I can't remember why my recordset isn't build straight from the accdb with an SQL query - but I had reasons (though maybe I should revisit them). I think it was a speed issue and I didn't want/need to slurp the entire table into a recordset to compare and sync. I only needed about 6 columns (there are bespoke conditions, creating collections, which are compared against incoming data to determine whether to import, update, or delete)

The problem, was that when users tried to hide columns they didn't want/need to see, it ruins everything. References to '.Cells(r, col)' were mute as the actual column was done like a count (hide col 'C', and suddenly .Cells(1,3) refers to Range("D1")), offset suffers the same pain, and explicit 'Range("C" & row)' returns null!

My only workaround so far, was to loop a tiny little loop for Columns 2 to 26, checking the column width, and never allowing the user to fully ​hide the columns - but rather set the width to '1'

 

[SydneyGeek]

I agree about the shared workbooks. I refuse to use them.

As long as you have a unique ID on the worksheet you can change a single record with ADO. I prefer in that case to update all the fields except the ID, using the ID as a filter.
New records can be created in a batch, then synced once the user is ready to refresh.

Also, unless you have thousands of records, pulling them down to the worksheet is quick. I've seen up to 10K records download in about a second.

Here's a link to a tutorial that takes you through many of the techniques.
Not sure about the hidden columns. How many fields do you have in the query?

Denis

 

[ClimoC]

I agree about the shared workbooks. I refuse to use them.

As long as you have a unique ID on the worksheet you can change a single record with ADO. I prefer in that case to update all the fields except the ID, using the ID as a filter.
New records can be created in a batch, then synced once the user is ready to refresh.

Also, unless you have thousands of records, pulling them down to the worksheet is quick. I've seen up to 10K records download in about a second.

Here's a link to a tutorial that takes you through many of the techniques.
Not sure about the hidden columns. How many fields do you have in the query?

Denis

Thanks Denis...

What you've described is exactly what I'm doing.

The problem is that the sync, as it is currently designed, relies on checking worksheet values - Two recordsets are created (Import CSV records, and existing DB records) and compared against each other.

If a column is hidden/width set to 0, the sync will fall over (and not just a case of error handling - the values simply cannot be obtained - so cannot be compared, cannot be updated etc)

Just wondering if anyone else knew about this. The aficionados will say I should deal directly with the source DB with SQL (Jet/OLE to the file, set as Recordset, commence) - and they're right - I just can't remember right now why I couldn't work it that way. It might have had something to do with not knowing how to declare the header names as field names (off the top of my head, setting a Recordset from a Connection winds up with a Recordset that has nameless column headers, and the first row of the RS is the col headers)

I will revisit methinks

 

[SydneyGeek]

Thanks Denis...

What you've described is exactly what I'm doing.

The problem is that the sync, as it is currently designed, relies on checking worksheet values - Two recordsets are created (Import CSV records, and existing DB records) and compared against each other.

If a column is hidden/width set to 0, the sync will fall over (and not just a case of error handling - the values simply cannot be obtained - so cannot be compared, cannot be updated etc)

Just wondering if anyone else knew about this. The aficionados will say I should deal directly with the source DB with SQL (Jet/OLE to the file, set as Recordset, commence) - and they're right - I just can't remember right now why I couldn't work it that way. It might have had something to do with not knowing how to declare the header names as field names (off the top of my head, setting a Recordset from a Connection winds up with a Recordset that has nameless column headers, and the first row of the RS is the col headers)

I will revisit methinks

You can definitely harvest the headers. Something like

For i = LBound(rst.Fields) to UBound(rst.Fields)
  Range("A1").Offset(0,i).Value = rst.Fields(i).Name
Next i
Range("A2").CopyFromRecordset rst

Alternatively, manually create the headers and then just clear the data before reloading:

Range("A1").CurrentRegion.Offset(1,0).ClearContents
Range("A2").CopyFromRecordset rst

Denis