SAGE - SQL - ACCESS LINK (1 Viewer)

[FJM-P]

I am currently linking several SAGE tables into Access and have done this without a problem for a number of years.
SAGE have just done a complete system upgrade and 1 of the linked tables has developed a problem.
The SAGE table is dbo.StockItem
In my Access database when I use the standard method of New Data Source and choose either link through ODBC or SQL server, it allows me to create the link but some of the fields are not shown. They are not hidden.
When I look at the SAGE table using a SQL server program, I can see the table and ALL fields are visible
When I look at the linked table in access, some of the fields are not there.
I need 1 particular field from the table
CODE
When I look at the SQL table and look at the properties, there is nothing in the Datatype. It is blank.
There are several other fields where the Datatype is blank and none of these appear in the Access linked table.
I think this should actually read "varchar" and specify a string length because there are several other fields which are similar and show this datatype in their properties.
Has anyone ever seen anything like this and does anyone have a solution

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[theDBguy]

Hi. Welcome to AWF!

Which version of the ODBC driver are you using? Just curious...

FYI. I moved your thread out of the Introduction Forum. You can still start a thread there to introduce yourself. Cheers!

 

[Minty]

What version of SQL server is it running on - as @theDBguy suggested it could be the driver is no longer talking the "correct turkey" to understand a newer data type they might be using.

 

[FJM-P]

Hi. Welcome to AWF!

Which version of the ODBC driver are you using? Just curious...

FYI. I moved your thread out of the Introduction Forum. You can still start a thread there to introduce yourself. Cheers!

Thanks for moving the thread....appreciated.
Not sure about the ODBC driver....how do I find out the version ?
Also, any idea why some of the fields in the table are OK but there are 14 which are not ?

 

[theDBguy]

Thanks for moving the thread....appreciated.
Not sure about the ODBC driver....how do I find out the version ?
Also, any idea why some of the fields in the table are OK but there are 14 which are not ?

Hi. Unfortunately, I have no experience with using SAGE, so we'll have to wait for someone else to chime on that.

When you create the linked table, you should have been asked/prompted to select an ODBC driver. Do you remember that step? You can post a screenshot, if you need help determining your version.

 

[Pat Hartman]

I have experience with other financial systems and they are famous for their unnormalized schemas. Go to the back end database and determine how many columns the table in question has. If it is more than 254, that is your problem. You can solve it by creating a view and having the view select only the columns you need. If you actually need all of them, you will need two views and you can't join them because a query is a "table" and also cannot contain more than 254 columns.

 

[Galaxiom]

You probably just need to get the Sage ODBC driver for the upgraded version.

 

[WayneRyan]

Also not a sage user.
The blank data type probably indicates a calculated column.
In design view of table you'll see no data type and a function reference. The users may have select privilege on the table but can't execute the function.

Hth,
Wayne

 

[gemma-the-husky]

I suppose the Sage ODBC driver may not expose every column.
If so, you may need the services of a Sage Developer - ie Sage provide additional facilities for registered sage developers, but it's not inexpensive to be a sage developer.

 

[Cotswold]

Hi FJM-P, I wrote and sold hundreds of programs to work with SAGE in the past but I would only run Imports and Exports.
This meant that all transactions were validated by SAGE, so if any problems arose in an Import, they could only be SAGE, or
user related. I wasn't inclined to work directly on SAGE Tables. This was because I knew SAGE would always initially deny
responsibility and leave me to prove them wrong. If an issue arose in accounts, any solution would need to be fixed now!

It was exactly this type of situation that I didn't want to find myself in. If you are writing directly into SAGE you are a braver
man than me. Presume you're not a fee paying SAGE Partner or Developer? If not I'd consider it, or find one who may be
able to help. You'd then get full details and specifications on their tables. Once you have them they are unlikely to change
in years.

It is always a problem when this type of company make changes. They often do not find it important to keep users updated.
I often had problems sending imports to CAD packages for steel fabrication. They'd change the import file layout, sometimes
a couple of times a year, leaving me to play catch-up. One day fine, an update is applied and the next morning it is falling
over with everyone in an animated state of excitement. I was not involved in creating the problem, but as my software was
falling over, apparently it's my fault! It's all good fun!

 

[Pat Hartman]

@Cotswold, good practice. That's what I did also. The ODBC was used for reporting for the most part.

I developed a drawing log for a steel fabrication company and had to work with KSS files They still love it 10 years out. Next month I'm converting the BE to SQL Server for them.