SageForum for UK versions of Sage 50- Help and support.

Hi,
Trying to find the table/field through ODBC that shows when a stock item is marked as inactive.

I assumed it would be in STOCK (just as the IGNORE_STK_LEVEL_FLAG is when stock level control is turned off which has 0 or 1 to designate)

But I can't find it in STOCK or any other likely tables.

Thanks!

The field is called STOCK.INACTIVE_FLAG

Thanks for fast reply but I've scoured the STOCK table via ODBC so many times now (via table linked into an Access dbase)
There's no field of that name or similar in what i'm seeing.

I've got INTRASTAT_COMM_CODE, INTRASTAT_IMPORT_DUTY_CODE, IGNORE_STK_LVL_FLAG
But not INACTIVE_FLAG

I'm using Sage50 program version v26.3.245.0 and ODBC 64bit v26 driver.
I'm starting to wonder if this field isn't exposed, or there's a bug, in the ODBC driver.

Thanks though!

I do not use the 64bit drivers as moist of my work is with MS Office and only the 32 bit version works properly with Sage 50.

I have checked the v26 32 bit driver and the field is present.


This could be a bug in the 64bit driver.

If you are using Access, why not install the 32bit version of office which is properly compatible with all the sage integration, you can then use the 32 bit drivers.

Thanks for the suggestion.
Just tried the 32bit odbc driver (and i'm running access for off365 32bit version) but no different i'm afraid.

Strangely other fields showing in your screen grab are not present in my STOCK fields exposed via either 32 or 64bit odbc (eg I can't see DISC_E_LEVEL10_RATE, PURCHASE_NOMINAL_CODE, RECORD_CREATE_DATE)

We might have to give up on this one!
Thanks anyway.

If you are doing this from Access I assume you have created a DSN to access the data.

I wonder if the DSN is pointing at the correct data set.

Add a product and see if it appears

Can you attach the DSN?

There is no difference in the fields that are exposed between the 32 bit and 64 bit ODBC drivers as far as I am aware, and the problem cannot be data related.

I believe that the problem you're hitting is that Access limits the number of fields in a linked table to 255 but the STOCK table in Sage has significantly more fields than this - I've not counted them but it's well over 300. What is happening is that Access is only linking the first 255 fields and this is all you see, even though there are more. I am not sure how Access decides on the order but I'd guess it is something to do with the order they are declared in Sage.

To my knowledge the 255 limit is hard coded in Access and applies to all data sources, not just Sage. So unfortunately you can't get all the fields, and because you can't control the order of the fields you could not do something like linking the table multiple times and expose different fields in each.

I suspect the reason why Bruce sees them is that he is using Excel - that's an educated guess as the screenshot certainly looks like the query wizard that Excel uses - and this does not impose a limit on retrieving more than 255 field names (although I believe it may still limit to 255 fields in a query).

So your choices are pretty limited I'm afraid. Best bet would be to query the ODBC directly rather than going via Access.

Hope that helps.
SG

Hi Someguy,

Very interesting. I've played around a bit and it seems that the MS Query interface does indeed have a 255 field limit. However the ODBC driver does not. What I've done is use MS Query to set things up with a single field query like SELECT STOCK_CODE from STOCK and then edit the SQL in the connection in Excel to read SELECT * from STOCK. Then I get all 365 columns brought back into Excel.
Regards,

Ian

Someguy has a given good insight into this, indeed I was using Excel.

If I look at where in the list of fields that field is it is towards the end so is consistent with his view.

Having a quick google, it confirms there is a 255 field limit.

https://support.microsoft.com/en-us/off ... c1025bb47c

I dumped the table and there are 367 fields in stock.

As a work around, you could do an external query using something like the outwit ODBC program to get just the field you want in a CSV file, or you could look at using one of the odbc to odbc tools an see if there is one that can only present a subset of fields. e.g. https://www.easysoft.com/blog/access-column-limit.html