ODBC Missing Fields

Question

I have an MS Access 2010 Front End to SQL Server 2012 Backend Database I use OLE DB to connect to that Database.  I've developed this on my PC with the backend to a local version of SQL Server that I have full sa Access to.  Everything works fine. However, in production the Front End must hook to our Production SQL Server Database which I do not have sa Access. The issue is when I reconnect the linked tables from the Production Database Several Fields in the views I use disappear. In fact the fields that vanish are the Primary and Foreign Keys I use to link the tables. To the best of my knowledge I have full access to that database in SQL Server just not SA access because there are other commercial databases in that server that I don't need Access to and that server is located at our Data Center.   If I go into SQL Server I can see those fields just fine. It's only when I connect using ODBC on the Production Server they're not there. Any idea on what I can do to fix this?

Answer 1

Did you originally create the database on your local sql, and then have it placed on the server?

You also don’t mention if you connecting via windows authentication, or are you using SQL logons?

If you using SQL studio (SSMS) to connect to the company’s instance of SQL server, MAKE sure you using the SAME logon you using when you link + connect with Access. (Ie: windows authenticated user, or SQL logons).

It is certainly possible that some permissions were assigned to given columns if a SELECT “GRANT” was used to give you rights to some of those tables (and they specified columns in place of just the table).

In other words is certainly possible to apply rights and permissions to individual columns, but it not going to occur by “accident” (hence the question as to where the original database came from – from your suggesting that seemed to be a local instance of SQL used during development).

So it certainly possible that if you connect using different logons, then tables “can” present different columns.

Another possible reason is you using a different SQL driver. (However this “usually” results in the data types of columns being shown wrong in Access, not that they are hidden).

If you use the windows “default” SQL driver, then any newer datetime2 columns will appear as stings and make a real mess of things. (So you want to choose the Native 11 or later driver, and link the tables using that to ensure support for the newer types of data columns in SQL server).

So during testing, make sure when you launch SSMS on your local machine to “connect” to the production SQL server, you use the SAME logon that you link tables with.

So from your local computer, launching SSMS with same logon you use to link your tables on that same machine, can you see those columns?

I should also point out that you can’t be using OLE DB to connect to sql server if you talking about linked tables in Access (the only exception to this would you be using ADP’s applications which are depreciated after Access 2010, and will not be supported).

So OLEdb is on its way out – not really supported by SQL Azure, and linked tables in Access never used OLEdb (except for the depreciated ADP or so called Access data projects).

You can still use OLEdb if you writing VBA code and creating ADO record sets, but for standard linked tables, you ARE using ODBC and not OLEdb.

You can certainly connect to the production server, and “check” of any column permissions with this command:

sp_column_privileges @table_name = 'dbo.Company'

Also, you likely want to ensure you using the same SQL drivers. The default “legacy” SQL driver is prefer, but if you using newer column types, then you want to use the SQL native 11 or later drivers – but these drivers are NOT installed by default and will have to be installed on each workstation.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Answer 2

The original DB is a commercial Database that is currently stored at our corporate Data Center.  That data is backed up Nightly to a Reporting Server and a copy was provided to us so we could generate reports without going across the network.  There was enough growth that it slowed down production due to network traffic when running reports.  Now there are plans in place to de-centralize our Database due to the growth and effect on Network traffic.  We've already moved the data into individual SQL Server Databases for each of the subsidiary companies but the Servers have yet to be moved. Once moved I will have SA Rights on the Server here but until then my Rights are limited by Corporate.  I do not have the ability to verify what rights I have and Corporate won't respond to email requests.  I just don't get why If I use SSMS and login to the Server I can see that the fields exist. If I connect to the Database through Access The fields do not exist or at least they're unavailable and I can't even see them. If it was random fields that would be one thing. But they're either the Primary Keys and Foreign Keys. Which makes running my reports impossible. I'm just trying to determine with certainty before I complain to my bosses if it's possible that the ODBC Driver for the connection could be at  fault?  (FYI:  I don't believe so because if I take the corporate backup and restore it to our local server I can see all fields through ODBC.

Answer 3

As I stated it is most certainly possible to assign permissions to columns. However as I ALSO stated that you can “test” this theory in about 5 seconds flat by ensuring that you try using SSMS on that SAME computer were you having trouble “seeing” columns from Access.

This is the critical part:

If in BOTH cases you are using the SAME user and logon to SQL server and SSMS still sees the columns, and Access does not, then you can conclude this is NOT due to permissions on the tables (and in our context permission on columns).

So you have to “play ball” here in terms of this trouble shooting process (in other words as part of the trouble shooting process YOU HAVE to engage people here and answer my questions).

So I need to you to “answer” my questions, else we can’t really start the process of solving why Access can’t see some columns.

So you need to confirm that on that same machine, when using SSMS and THE SAME LOGON/ID TO CONNECT to that SQL server, that you see the columns in SSMS, but not in Access?

If you using a different logon, or using remote desktop, or using SSMS on that actual SQL server with a different user, THEN THE ABOVE TEST IS NULL AND VOID AND OF NO USE FOR TROUBLESHOOTING.

If above test results in one showing columns, and the other does not show columns, then we can BY LOGIC conclude this issues is NOT due to permissions assigned to columns, but due to driver or some other issue.

>> I do not have the ability to verify what rights I have and Corporate won't respond to email requests

Actually, you can easy if Access OR SSMS is able to connect to that SQL server instance.

You just stated in your post you ARE using SSMS to verify that some column s are missing. So if you have use of SSMS, then you simply can connect and test/look at/enquire about permissions as you please.

So NOTHING should be stopping you from running ANY command that gives information about permissions. You ARE saying you have use of SSMS and you ARE saying you using SSMS to show that some columns exist, but you can’t see such columns form Access. So your WHOLE CASE has been made by using SSMS – and SMS is EXACTLY HOW you can test your permissions!

So given that you using SSMS to prove and show the above column missing issue, then you most certainly have the ability to check your permissions using SSMS. You don’t need to “ask” someone for information about permissions.

However, EVEN if you all of a sudden don’t have use of SSMS, you can STILL execute ANY T-SQL command RIGHT from Access that you would execute in SSMS anyway! (Read this again a few times – you have that great not follow instructions knack).

Simply create a pass-through query in Access. What you type in that query will be IDENTICAL as if you typed it in with SSMS in to a query window.

In fact, you can execute ANY command right from Access to check permissions if you don’t have use of SSMS. So EVEN if you do not have ssms on the offending machine, you can:

Create a pass-through query, and then:

Test raw 100% T-SQL server SQL from Access. That includes doing a simple select from table which would case and return 100% IDENTICAL RESULTS if done from SSMS. This would also thus verify if columns are missing.

So in the pass-through query, type in a select on a known table name like this:

select * from dbo.tblCompany

Run the above query (of course use a known table name I place of tblCompany)– does it return all columns? This test is great since it is NOT dependent on a linked table – it returns the columns “on the fly” and is NOT pre-determined during the table linking process.

The above will return all columns – do you see them? As I stated, creating a PT query and typing in the above will take far less time than even you writing up a response here.

You can type the above EITHER in Access, or EITHER in SSM - but from Access this MUST be a pass-though query.

You can also execute (from Access and the PT query) any command to show and display your permissions.

So you can type this command right inside of the Access query builder (assuming a PT query) (just use the same PT query you using for testing the above select command).

OR YOU can type this command in from SSMS. So you have TWO choices here to check if column permissions exist:

sp_column_privileges @table_name = 'dbo.Company'

So the above would show and display any permissions by column assigned to the above given table. (If the data returned is blank, then INDIVIDUAL column permissions have NOT been assigned.

>if it's possible that the ODBC Driver for the connection could be at fault? 

Yes, it could be. So are you using the legacy default install SQL server from Access, or are you using a newer “Native 11” driver? (and if you not sure, then simply re-link the tables and create a new connection – you HAVE to choose which driver during a table re-link process anyway.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 4

Hi Albert,

I am having the exact same problem, I am trying to link to a table in our ERP database but when I do it I am only getting 19 out of 176 fields, I am using a SQL account to login, if I do it from SSMS I can see all fields, if I do a PT query in Access I can get all the fields, but if I link the table I only get 19 (I did all this with the same SQL login in the same computer). Executing sp_column_privileges is not returning any records so I assume there are no individual column permissions assigned. I am using the SQL Server native client 11.0 driver on an .accdb in Access 2016 64Bit running on windows 7. The SQL server is 2014 (12.0.2000.8).

Any idea of what could be causing the issue?

Thanks!

JG

Answer 5

Hum, that certainly is perplexing!

Note that if you add new columns, then they don’t show up in access until you refresh or re-link.

Given that the pt query shows all the columns, I would delete the linked table, and then re-link.

However, I dare say what you are seeing is perplexing at this point in time.

Best guess + suggesting is to delete the link, and then re-link. If that don't work then shovels and digging deeper will be required!

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Answer 6

I just wanted to add to this string to point out that I too have linked to a SQL database via a DSN and the tables (as displayed with field lists) are  not showing all the fields. I have tried both MS query (from Excel) and MS Access. In both cases not all the fields are showing.

However, I find that when I drag and drop all the fields into the Query, within MS Query - Excel, and load the data into Excel and then edit the SQL by right clicking on the data -  Table,External Data Properities, Definition, Command text - and I change the SQL by removing the list of field names with an asterisk, then all the fields show up.

Equally if I use MS Access to write a query for all fields and the query SQL is Select * from db1 for instance it does not show all the fields but if I then change the same query to a Pass-Through Query and use the same DSN then all the fields show up.

It is annoying. I am still investigating.

Answer 7

And you not linking to a view? If a view was created, and THEN columns were added to the database, then the view on sql server will still not have those additional columns until you refresh the view.

You seem to suggest that the linked table in question ***is*** a linked table, and not a view. However, if this is a view you are linking to, then this would explain your issue.

If just a table, then I have to think some kind of permissions are involved. If you have linked tables, and the sql tables are modified (new columns), then Access will NOT see these new columns until you re-link the access table(s) in question.

Regards

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 8

I am linking to a View but this is a commercial Database and the views don't change until corporate gets an update from the commercial company.  

Answer 9

Well, if logging into the server database with sql manager, (same logon as used to link the Access tables), then there is no reason as to why Access would not see all the columns.

The only issue  seen that messes this up is if the view has more then 255 columns.

However, you noted and suggested that if you use a PT query, then you see all of the columns. I guess, I would check/ask if the view has more then 255 columns.

I don't think the PT query will work with more then 255 columns, but it might well return "more" columns.

As noted, assuming that you deleted, and re-linked that view from Access, I can't think of any other reason other then permissions on the columns (but as you noted, you are testing with SQL tools and using the SAME logon). At this point, I am somewhat guessing, but more then 255 columns likely the issue.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 10

I considered that so I counted the columns there are 213 So that isn't it.  Besides the column that is missing is the Primary Key Column

Answer 11

No surprise that the column does NOT show as a PK (views do not have a defined PK). However the column not showing up is a different matter.

Assuming in both cases you using the SAME logon (from access, and from SSMS), then the only idea left is that the column in question has some illegal characters.

When you use a PT query, this “illegal” checking of column name does not occur and it gets through. However, when you link, it is verified.

Any strange characters in that PK column? Even if the PK is a GUID, it still should link as a string data type column in access.

You certainly could replace the linked view with a PT query that goes

Select * from theViewName

While the above likely works, you going to take a big performance hit, since the PT query will not filter correctly (say used as a form/report in access with a “where” clause).

So a PT query likely can get you around this issue. Keep in mind that the PT query is always read only. And filtering against that PT query will of course not work well at all.

Different sql logons can be assigned different default schema’s, and that again would be an issue, but as you stated, you using the same SQL logon in both cases (you are sure of this, right?).

I wonder if some hidden character, or some non supported character is in that column name?

The fact of that column being a PK should not matter, and as noted, Access will not see any of the columns as PK in a view unless you tell it to do so.

Hopefully someone else can offer a solution to this mystery, but given the above suggestions and process of elimination, then this issue is beyond my knowledge here.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 12

I am going to add one more comment here:

In the past 15 years I seen this question come up?

In EVERY case the information given to the problem was incorrect.

In one case, they had scripted out the database to a different test server.

In another case, they were launching SSMS on the server, and using the admin account to logon on.

In another case, they were using a view on view (but then BOTH SSMS and access should have produced the same result – but again a different test database was being used). The user came back and said the problem was that they had added columns to the main table, but had not refreshed the view (but that should not matter – since both SSMS and access would have shown the missing columns).

In EVERY case some “Mr. Obvious” step was missing, and in NEAR every case the logon and process they were using to launch SSMS was using a different database, or a different logon.

If you are launching SSMS from the same workstation as access, then both SSMS and Access should show the same columns for that linked view – but this assumes you using the SAME logon from SSMS and from Access.

And in most cases, the linked table was not being deleted, and re-created during this testing process each time.

Since SSMS, and the PT query shows the columns, then deleting the linked table, and re-creating it from the Access UI should also then show the columns. (but again, same logon being used here).

So, you are 100% sure that you have SSMS and Access on the same machine, and that you using the SAME logon and connection for both Access and SSMS?

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 13

100% yes.  

Answer 14

Thank you kindly for following up. I do appreciate this.

I wish I could offer more.

I don’t doubt what you are seeing but I can’t come up with any reason for this issue.

I suppose you could try the native 11, or 13 odbc drivers, and see if that helps.

The newer drivers are preferred, but then you have to install the ODBC driver on each work station as a result.

So you could try a re-link using a different set of ODBC drivers (but this is a long shot on my part).

I am “really” curious as what is going on here!

I don’t like being stumped so bad. I worked with sql server + access for 15+ years now. I can’t reproduce what you are seeing.

If you script out the table, and the view to another test database, and link – does access show the columns?

If the above ALSO hides the columns, then you can post the create table script, and the view without having to share data.

This would only help if such a test on your part results in those columns missing again.

I am leaning towards characters in the column name that access don't like - but this idea of mine is also a long shot.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada