Relinking to SQL Server View produces error (1 Viewer)

djoosten

New member
Local time
Yesterday, 20:31
Joined
Aug 30, 2011
Messages
7
I have a procedure that runs to update the links to SQL Server tables and views that uses the RefreshLink method in Access VBA. I am now getting an error (Error: 3001 Invalid Argument) when some of the views in SQL Server attempt to relink. I tried to use the 'linked Table manager' to relink it manually but still get the error. I also tried to remove the view and relink it using External Data | ODBC Database. It still produces the error. This view has linked properly for over 10 years and it still works correctly in my test environment with Access 2010 and SQL Server 2008. The customer has recently installed Access 2016 and has SQL Server 2008R2. There are several views used in the database but only a couple of them fail. I verified that they are identical on SQL Server to my test environment so I can't figure out why they will not link. I can run a 'pass-through' query using the views in question and it will return results but it will no longer relink. Does anyone know if something has changed in Access 2016 that would cause this issue? I can't find anything online that indicates a change.
 

Minty

AWF VIP
Local time
Today, 02:31
Joined
Jul 26, 2013
Messages
10,368
I wonder if the SQL ODBC driver has been updated and is causing problems?
Might be worth checking the version being used.
 

djoosten

New member
Local time
Yesterday, 20:31
Joined
Aug 30, 2011
Messages
7
Thanks, Mindy. I checked the ODBC Driver version and both locations are using the default v6.01.7601.17514 SQL Driver. I also installed the latest Microsoft driver v13 on the client PC and it gave the save error. I also installed Access 2016 in my test environment and it worked fine in testing as well. I also verified that the views in both locations on SQL Server are the same. Our test database is SQL Server 2008 the client is using SQL Server 2008R2.
 

djoosten

New member
Local time
Yesterday, 20:31
Joined
Aug 30, 2011
Messages
7
Sorry, I noticed that I misspelled your name. Thanks, Minty.
 

Minty

AWF VIP
Local time
Today, 02:31
Joined
Jul 26, 2013
Messages
10,368
No problem - been called a lot worse...
Have the permissions been set correctly on the view within SQL?
As I'm down as an Admin on our SQL server, views / tables etc are always available to me - in production if I forget to grant select privileges on a view it bombs out.
 

djoosten

New member
Local time
Yesterday, 20:31
Joined
Aug 30, 2011
Messages
7
I believe it has the correct permissions because I can access the data via a pass-through query but I will double check. We normally give 'owner' permissions to our standard login credentials. These same login credentials are used for the pass-through as are used in the DSN.
 

djoosten

New member
Local time
Yesterday, 20:31
Joined
Aug 30, 2011
Messages
7
As a follow-up to this issue...I was able to fix this problem by manually forcing the Office Updates to download and run. Even though their IT said that they run all updates automatically, I searched for the latest updates for Office 2016 and ran them. After the updates, all views linked normally.
 

Minty

AWF VIP
Local time
Today, 02:31
Joined
Jul 26, 2013
Messages
10,368
Glad you got it working - updates (or the lack of them) have caused various issues for us over the years.
 

Users who are viewing this thread

Top Bottom