Desktop productivity for business analysts and programmers

Import Error when Importing from Access

Reply
New Contributor
Posts: 4

Import Error when Importing from Access

Hello,

I am running SAS Enterprise Guide version: 5.100.0.13219 (32-bit)

Running in WOW64 (32-bit subsystem): Yes

Running as a 64 bit process: No

and Access 2010 version 14.0.6023.1000 (32-bit)

I am attempting to import a table (1.1 million rows, 35 columns) from a database in Access to SAS EG. Every time I use File > Import Data and select the file I get an error that "SAS has encountered a problem and needs to close." Below is the error. More details can be provided if needed. Needless to say, the import fails. What is causing this, and how can I fix it/is there an alternative method? Currently we have been forced to export the table from Access to Excel in two pieces, then import it to SAS and stitch it back together. It's inefficient and time-consuming, so any help would be greatly appreciated! Thank you.

-------------------------- Technical Information Follows --------------------------

Exception Details:

----------------------------------------

Exception type:  System.IO.IOException

Message:         No more data is available.

Source:          mscorlib

Target Site:     Win32Error

Super User
Posts: 3,233

Re: Import Error when Importing from Access

Do you have the SAS product SAS/ACCESS to PC Files? In EG in Server List, check in properties for the SAS products installed. If you do then you can run a SAS program/EG process to try importing the data.

New Contributor
Posts: 4

Re: Import Error when Importing from Access

Hi SASKiwi,

It appears the server does have ACCESS to PC Files installed, but when I try Proc Import that fails as well. Here is I have tried to run:

PROC IMPORT OUT=fakeserver.cxb_iha_ops_2014_08_07

            DATATABLE='Member Level Data'

            DBMS=ACCESS REPLACE;

   DATABASE="&Q:\fakepath\Member Level Detail1.accdb";

   USEDATE=YES;

   SCANTIME=NO;

   DBSASLABEL=NONE;

RUN;

What's the problem with my code? I'm still getting familiar with SAS, so I welcome corrections/alternatives. Thank you!

Super User
Posts: 7,431

Re: Import Error when Importing from Access

To diagnose your code, you absolutely need to post the log that contains the error message(s). Since I'm closing shop for the weekend, it may be some time before I can help you further.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Import Error when Importing from Access

Apologies, Kurt. I meant to post the log and forgot.

1                                                          The SAS System                               08:26 Friday, August 8, 2014

1          rsubmit task1;

NOTE: Remote submit to TASK1 commencing.

1475  %put NOTE: Using grid node &SYSHOSTNAME to run task.;

NOTE: Using grid node isbsas09 to run task.

1476  %let _EGRC=0;

1477  ;*';*";*/;quit;run;

1478  OPTIONS PAGENO=MIN;

1479  %put NOTE: The current task will be submitted to the grid due to the server configuration policy.;

NOTE: The current task will be submitted to the grid due to the server configuration policy.

1480  %LET _CLIENTTASKLABEL='Program1';

1481  %LET _CLIENTPROJECTPATH='/sascode/myuserID/IHAWeeklyUpdate.egp';

1482  %LET _CLIENTPROJECTNAME='IHAWeeklyUpdate.egp';

1483  %LET _SASPROGRAMFILE=;

1484

1485  ODS _ALL_ CLOSE;

1486  OPTIONS DEV=ACTIVEX;

2                                                          The SAS System                               08:26 Friday, August 8, 2014

1487  %let sasworklocation="&_eg_gridworkpath/";

1488  GOPTIONS XPIXELS=0 YPIXELS=0;

1489  FILENAME EGSR "&_eg_gridworkpath/%sysfunc(uuidgen(0))_eggen";

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

1490  ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

1490! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

1490! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

1491

1492  GOPTIONS ACCESSIBLE;

1493  PROC IMPORT OUT=servername.cxb_iha_ops_2014_08_07

1494              DATATABLE='Member Level Data'

1495              DBMS=ACCESS REPLACE;

ERROR: DBMS type ACCESS not valid for import.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

      real time           0.01 seconds

      user cpu time       0.01 seconds

      system cpu time     0.00 seconds

      memory              44.06k

      OS Memory           14372.00k

      Timestamp           08/08/2014 08:39:18 AM

      Page Faults                       0

      Page Reclaims                     25

      Page Swaps                        0

      Voluntary Context Switches        6

      Involuntary Context Switches      5

      Block Input Operations            0

      Block Output Operations           0

    

WARNING: Apparent symbolic reference Q not resolved.

1496     DATABASE="&Q:\fakepath\Member Level Detail\Member Level Detail1.accdb"

1496! ;

1497     USEDATE=YES;

1498     SCANTIME=NO;

1499     DBSASLABEL=NONE;

1500  RUN;

1501

1502  GOPTIONS NOACCESSIBLE;

1503  %LET _CLIENTTASKLABEL=;

1504  %LET _CLIENTPROJECTPATH=;

1505  %LET _CLIENTPROJECTNAME=;

1506  %LET _SASPROGRAMFILE=;

1507

1508  ;*';*";*/;quit;run;

1509  ODS _ALL_ CLOSE;

1510

1511

1512  QUIT; RUN;

1513  %sysrput _EGRCGRID=&_EGRC;

NOTE: Remote submit to TASK1 complete.

2          %let _EGRC=&_EGRCGRID;

3        

Hope that helps. The items in strikethrough (user ID, server name, filepath), have been altered for confidentiality.

Community Manager
Posts: 2,889

Re: Import Error when Importing from Access

The PROC IMPORT step is running in your SAS Grid environment on a UNIX/Linux platform.  This session probably does not have access to the path where your MDB sits, and even if it did the SAS session would not be able to process this import of a Windows database directly.  Instead, it would require a PC Files Server specification and use of the DBMS=ACCESSCS option to trigger it.  I'm guessing that you don't have all of that set up, but if you do a SAS admin would need to tell you exactly what the PC File Server host and port specification should be.

Another possibility to get the data into the session via EG -- you could try File->Open->From ODBC.  Then you could use the Microsoft Access ODBC driver to open the database file.  However, for this large table it might be slow -- it's not usually the method we recommend.

Chris

Super User
Posts: 7,431

Re: Import Error when Importing from Access

Do you get the error immediately or does it take some time?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Import Error when Importing from Access

Hi Kurt,

I receive the error immediately upon selecting the file to be imported. Proc Import also fails, though it does not cause a fatal error.

Super User
Posts: 7,431

Re: Import Error when Importing from Access

Could it be that you are running out of space for the work library? It may be that EG tries to send a piece of data (converted into a SAS-readable format by MS DAO) to the workspace server so that SAS can take a look at it.

Another possible failure point is of course the MS DAO, which may not be happy with the data it reads from Access.

Consider a call to TS if nobody here has another idea.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Community Manager
Posts: 2,889

Re: Import Error when Importing from Access

From the error and from looking at other tracks, I think that this is failing when EG is trying to decide whether there is a Microsoft data access engine (ACE) driver installed.

It's looking for the ACE footprint in the Windows registry.  For this 32-bit process on your 64-bit machine, this would be in:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall

Specifically, it's looking to see if you have MS Access:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-0015-0409-0000-0000000FF1CE}

Or at least the ACE driver:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-00D1-0409-0000-0000000FF1CE}

But this step seems to be failing while simply getting the list, implying that you might not even have the rights to query this.  Perhaps there is a security package that is getting in the way?  Can you view these values in RegEdit?

Chris

Ask a Question
Discussion stats
  • 9 replies
  • 995 views
  • 0 likes
  • 4 in conversation