Compatibility with Amazon Redshift as external database

%3CLINGO-SUB%20id%3D%22lingo-sub-1342004%22%20slang%3D%22en-US%22%3ECompatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1342004%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20determine%20whether%20Access%202019%20is%20compatible%20with%20the%20current%20version%20of%20Amazon%20Redshift%20as%20an%20external%20data%20source%3F%26nbsp%3B%20Specifically%2C%20does%20the%20linked%20tables%20feature%20work%20with%20Redshift%20via%20ODBC%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPostgreSQL%20appears%20to%20work%20with%20Access%2C%20but%20not%20Redshift%2C%20although%20there%20are%20reports%20on%20the%20web%20of%20Redshift%20being%20used%20in%20this%20way.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20able%20to%20connect%20to%20Redshift%20in%20Access%20(via%20ODBC)%2C%20and%20we%20reach%20the%20point%20of%20seeing%20the%20list%20of%20tables%20in%20the%20database%20to%20select%20from.%20When%20we%20select%20any%20table%2C%20even%20a%20very%20simple%20table%20owned%20by%20the%20connecting%20user%20and%20containing%20an%20INTEGER%20primary%20key%20and%20other%20standard%20SQL%20data%20types%2C%20the%20following%20error%20appears%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReserved%20error%20(-7733)%3B%20there%20is%20no%20message%20for%20this%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1342004%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1343709%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1343709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639070%22%20target%3D%22_blank%22%3E%40jussi58%3C%2FA%3E%26nbsp%3BTake%20a%20look%20here%20%3A%3CA%20href%3D%22http%3A%2F%2Fwww.utteraccess.com%2Fforum%2Flofiversion%2Findex.php%2Ft2032855.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.utteraccess.com%2Fforum%2Flofiversion%2Findex.php%2Ft2032855.html%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1345338%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1345338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401564%22%20target%3D%22_blank%22%3E%40tsgiannis%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much%20for%20finding%20this.%26nbsp%3B%20It%20includes%20the%20suggestion%20of%20using%20passthrough%20as%20a%20workaround%2C%20which%20will%20not%20work%20in%20my%20case.%26nbsp%3B%20But%20it%20looks%20like%20we%20have%20the%20same%20problem%20now%20that%20was%20reported%20in%202015.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1346353%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1346353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639070%22%20target%3D%22_blank%22%3E%40jussi58%3C%2FA%3E%26nbsp%3BCan%20i%20ask%20why%20Pass%20through%20queries%20are%20not%20an%20option.%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1350466%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1350466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639070%22%20target%3D%22_blank%22%3E%40jussi58%3C%2FA%3E%26nbsp%3BHave%20explored%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.aws.amazon.com%2Fredshift%2Flatest%2Fmgmt%2Fconnecting-drop-issues.html%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAWS%20documentation%20and%20troubleshooting%20pages%3C%2FA%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1351836%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1351836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401564%22%20target%3D%22_blank%22%3E%40tsgiannis%3C%2FA%3EYes%2C%20this%20is%20for%20a%20data%20warehouse%20with%20more%20than%20100%20tables%2C%20and%20my%20(limited)%20understanding%20is%20that%20setting%20up%20passthrough%20queries%20would%20be%20laborious%20with%20that%20many%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1351842%22%20slang%3D%22en-US%22%3ERe%3A%20Compatibility%20with%20Amazon%20Redshift%20as%20external%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1351842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%20Yes%2C%20thanks.%26nbsp%3B%20We%20are%20able%20to%20connect%20to%20the%20database%20using%20other%20database%20clients%20running%20on%20the%20same%20machine%2C%20and%20as%20I%20mentioned%2C%20Access%20is%20able%20to%20connect%20and%20gets%20as%20far%20as%20retrieving%20the%20list%20of%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Is it possible to determine whether Access 2019 is compatible with the current version of Amazon Redshift as an external data source?  Specifically, does the linked tables feature work with Redshift via ODBC?

 

PostgreSQL appears to work with Access, but not Redshift, although there are reports on the web of Redshift being used in this way.

 

We are able to connect to Redshift in Access (via ODBC), and we reach the point of seeing the list of tables in the database to select from. When we select any table, even a very simple table owned by the connecting user and containing an INTEGER primary key and other standard SQL data types, the following error appears:

 

Reserved error (-7733); there is no message for this error.

 

Thank you for any assistance.

6 Replies
Highlighted

@tsgiannis Thank you very much for finding this.  It includes the suggestion of using passthrough as a workaround, which will not work in my case.  But it looks like we have the same problem now that was reported in 2015.

Highlighted

@jussi58 Can i ask why Pass through queries are not an option. ?

Highlighted
Highlighted

@tsgiannisYes, this is for a data warehouse with more than 100 tables, and my (limited) understanding is that setting up passthrough queries would be laborious with that many tables.

Highlighted

@George Hepworth  Yes, thanks.  We are able to connect to the database using other database clients running on the same machine, and as I mentioned, Access is able to connect and gets as far as retrieving the list of tables.