topic Using SAS ODBC driver to link and view SAS datasets from Microsoft Access in Microsoft Integration with SAS

Using SAS ODBC driver to link and view SAS datasets from Microsoft Access

JamesPower4 — Wed, 19 Feb 2020 20:46:04 GMT

I have a large number of SAS datasets that I would like to view (and possibly edit) using Microsoft Access. I can easily see/import Access tables into SAS, but I want to go the "other" way - view the SAS dataset contents while in Access. Screen shots of my SAS ODBC configuration are below. Within Access I select

External data -> New data source -> From other sources -> ODBC database

Using the wizard I select that I want to link to a SAS dataset that has an index defined. I select the SAS ODBC name (BSR) from the list of machine data sources.

At that point Access invariably crashes.

I have previously been able to see the list of datasets in the SAS BSR folder/library, and select one that has an index. I can't seem to reproduce that, but nonetheless get I always got an error when I try to open it in Access.

Has anyone had success with this, or can identify what i might be doing wrong?

Using 64 bit Windows 10, 64 bit SAS, and 64 bit Access.

Thanks in advance.

Jim Power

Re: Using SAS ODBC driver to link and view SAS datasets from Microsoft Access

SASKiwi — Wed, 19 Feb 2020 22:36:02 GMT

Have you chosen the 64-bit SAS ODBC driver? What happens if you select a non-indexed SAS dataset? Try starting SAS manually from a command line with your Local Options as a test.

Re: Using SAS ODBC driver to link and view SAS datasets from Microsoft Access

JamesPower4 — Wed, 19 Feb 2020 23:09:19 GMT

Hi SASKiwi:

Thanks for the quick response. I believe I'm using the correct (64 bit) driver. I opened it using the 64 bit ODBC Administrator, although I think the supplied driver supports both 32 and 64 bit. It lists the platform as 32/64 bit.

I did try to connect with a non-indexed SAS dataset, and Access crashed. I also tried starting the server from the command line, and again Access crashed. Those were both great suggestions, and I appreciate your help

Thanks again

Jim Power

Re: Using SAS ODBC driver to link and view SAS datasets from Microsoft Access

SASKiwi — Sun, 23 Feb 2020 02:51:37 GMT

@JamesPower4 - Appreciate your kind words. I suggest SAS Tech Support is your best option. The problem is there have been a lot of different versions of Access over the years and the same with SAS so figuring out what works with what is a bit of a minefield.