PROC EXPORT and Office 365: Access DB creation failing after migration to Office 365

[louisehadden]

Hi, all. We have been running a (somewhat antiquated) program to create an ACCESS db with multiple tables uneventfully for many years. Our company migrated our servers to Office 365 instead of standalone copies of Access, Word, etc. recently and the program no longer works. We get a connection error. We've determined that it is a connection error. Does anyone have any handy tips on (1) setting up an OBDC connection for SAS->MS Access 365 and (2) any tweaks required to the PROC EXPORT code? Any other suggestions? This file is generated every month and we definitely don't have time for tweaking for this month's client delivery - we know we need to modernize in the near future but need a quick fix for now. Any suggestions on how to fill in the blanks programmatically (i.e. syntax for the OBDC connection required) welcome!

Thanks in advance!

[SASKiwi]

It would help if you posted the SAS log for your existing code so we have a clearer idea of what is happening.

[ChrisHemedinger]

Hi Louise,

Is the Access DB on the local machine or accessible network drive/path? Or is it legitimately "in the cloud" now that you're on Microsoft 365?

Assuming SAS for Windows 64-bit, see if you can test this ODBC connection using the ODBC Data Sources app that's built into Windows.

If that works, then the same settings should work in SAS. You can even define a System DSN to use as shorthand in your SAS code for the next time you run this.

[louisehadden]

Thanks, Chris, I will check out the ODBC connections. We have a set up with Windows x64 Amazon servers (so data in the cloud), and previously had non-cloud 64 bit Microsoft products in place. We just need to figure out how to make the connections work in a monthly process where the output file name will differ each month. You've given me some good clues toward making this process "push button" again.

[louisehadden]

Interestingly, after trying the access export with SAS Studio, EG, Display Manager and batch SAS, getting different error messages with all 4 systems, the problem turned out to be an overlong path name, and had nothing to do with PROC EXPORT and Office 365 (except that perhaps SAS or MS Excel suddenly got picky about the length after many years of running the same program with the same path name, or that maybe our servers naming conventions changed slightly - I'm looking into that). I will put together a package for Tech Support because it really was interesting that the error messages in the logs did not ever indicate what the problem really was! A fun little debugging project for a cold winter's day.

[Chidgene]

One thing you could try is checking the SAS documentation or forums for any updates or patches that might address these issues. Sometimes, these software updates can be a game-changer and fix those pesky bugs that pop up unexpectedly.

Another approach could be to double-check your settings and permissions within Office 365. Sometimes, it's the little things like permissions or security settings that can trip things up, so it's worth a look.