BookmarkSubscribeRSS Feed
louisehadden
Quartz | Level 8

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!

louisehadden_0-1641944242381.png

Thanks in advance!

4 REPLIES 4
SASKiwi
PROC Star

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

ChrisHemedinger
Community Manager

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.

 

ChrisHemedinger_0-1641990238779.png

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.

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!
louisehadden
Quartz | Level 8

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
Quartz | Level 8

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 670 views
  • 0 likes
  • 3 in conversation