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

[JamesPower4]

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

[SASKiwi]

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.

[JamesPower4]

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

[SASKiwi]

@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.