SOLVED
Home

Linking a File Table from Microsoft SQL Server to a table within Microsoft Access 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-740197%22%20slang%3D%22en-US%22%3ELinking%20a%20File%20Table%20from%20Microsoft%20SQL%20Server%20to%20a%20table%20within%20Microsoft%20Access%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740197%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20used%20the%20standard%20method%20of%20linking%20tables%20from%20a%20Microsoft%20SQL%202012%20Database%20into%20an%20Access%202016%20Database.%20Every%20table%20linked%20correctly%20except%20for%20a%20File%20Table.%20Within%20Access%2C%20the%20linked%20table%20displays%20the%20File%20Table's%20fields%20but%20not%20the%20records.%20How%20can%20I%20create%20a%20linked%20table%20within%20Access%20that%20correctly%20displays%20all%20the%20contents%20of%20the%20File%20Table%20that%20are%20within%20the%20SQL%20database%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20using%20updated%20drivers%20to%20create%20the%20DSN%20to%20allow%20for%20connection%20to%20the%20SQL%20server.%20There%20is%20an%20integrated%20test%20within%20the%20DSN%20creation%20wizard%20that%20displays%20the%20connection%20is%20successful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20expected%20the%20linked%20table%20within%20Access%20to%20display%20the%20contents%20of%20the%20File%20Table%20within%20the%20SQL%20database%2C%20but%20it%20displays%20the%20following%20images%20and%20an%20ODBC--call%20failed%20error%20message%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-740197%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFile%20Tables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773104%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20a%20File%20Table%20from%20Microsoft%20SQL%20Server%20to%20a%20table%20within%20Microsoft%20Access%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371745%22%20target%3D%22_blank%22%3E%40iaskey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20can%20not%20link%20a%20FileTable%20as%20usual.%20FileTables%20extend%20the%20capabilities%20of%20the%20FILESTREAM%20feature%20of%20SQL%20Server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20you%20can%20do%20%22something%22%20with%20the%20FileTable%20with%20a%20%22passtrough%20query%22.%26nbsp%3B%20I%20think%20the%20solution%20would%20be%20solving%20it%20with%20VBA.%20Here%20is%20a%20link%20to%20an%20article%20about%20connecting%20to%20the%20FileStream%20table.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fblob%2Fcreate-client-applications-for-filestream-data%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fblob%2Fcreate-client-applications-for-filestream-data%3Fview%3Dsql-server-2017%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774117%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20a%20File%20Table%20from%20Microsoft%20SQL%20Server%20to%20a%20table%20within%20Microsoft%20Access%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381533%22%20target%3D%22_blank%22%3E%40testmuts%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response!%20The%20issue%20was%20a%20path_locator%20field%20in%20the%20FileTable.%20For%20some%20reason%2C%20there%20are%20problems%20with%20creating%20a%20linked%20table%20with%20the%20datatype%20of%20that%20field%20in%20Access.%20I%20was%20able%20to%20avoid%20creating%20a%20linked%20table%20with%20this%20field%20by%20creating%20a%20view%20in%20SQL%20Server%20that%20did%20not%20contain%20that%20field.%20I%20then%20did%20a%20dsnLess%20connection%20using%20VBA%20to%20that%20view%20in%20SQL%20Server.%20It%20worked%20well%20after%20that!%3C%2FP%3E%3C%2FLINGO-BODY%3E
iaskey
New Contributor

I've used the standard method of linking tables from a Microsoft SQL 2012 Database into an Access 2016 Database. Every table linked correctly except for a File Table. Within Access, the linked table displays the File Table's fields but not the records. How can I create a linked table within Access that correctly displays all the contents of the File Table that are within the SQL database?

 

I've tried using updated drivers to create the DSN to allow for connection to the SQL server. There is an integrated test within the DSN creation wizard that displays the connection is successful.

 

I expected the linked table within Access to display the contents of the File Table within the SQL database, but it displays the following images and an ODBC--call failed error message:

 

 

2 Replies

@iaskey 

 

I think you can not link a FileTable as usual. FileTables extend the capabilities of the FILESTREAM feature of SQL Server.

 

Maybe you can do "something" with the FileTable with a "passtrough query".  I think the solution would be solving it with VBA. Here is a link to an article about connecting to the FileStream table.

https://docs.microsoft.com/en-us/sql/relational-databases/blob/create-client-applications-for-filest...

Solution

@testmuts Thank you for your response! The issue was a path_locator field in the FileTable. For some reason, there are problems with creating a linked table with the datatype of that field in Access. I was able to avoid creating a linked table with this field by creating a view in SQL Server that did not contain that field. I then did a dsnLess connection using VBA to that view in SQL Server. It worked well after that!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies