BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Paul_NYS
Obsidian | Level 7

Hi

In the Enterprise Guide GUI area, I have exported a data set in various formats (i.e. txt, csv, xlsx.) and have tried to import the same file into an Access 2010 database. When I try to import the file, I am getting this error: "Cannot open database ". It may not be a database that your application recognizes, or the file may be corrupt."

Has anyone gotten this before?

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Paul: You very likely have exceeded the MS Access limitations.  It can only handle 32768 records and must be under 2gb.  The specs can be found at: http://office.microsoft.com/en-ca/access-help/access-2010-specifications-HA010341462.aspx

View solution in original post

11 REPLIES 11
Reeza
Super User

Why not export directly to an access DB?

Anyways, can you open the data in a text editor or Excel?

Can you import other files into Access that work and not just this one?

If so it's an Access issue, not a SAS issues?

Paul_NYS
Obsidian | Level 7

I have been able to import other SAS files from this SAS program into Access, so it is not Access.

With this particular file, I tried to eliminate columns to see if it was due to a data issue, but I got the same error regardless of which columns were in the file.

I was not aware you could export directly into a DB. I only see file export options? Actually, I don't know if I have the SAS DB software to be able to do this actually.

Paul

Reeza
Super User

Typically if you can export into an excel file you can into Access.

Can you connect to your db?

libname mydb ACCESS 'path to your accessdb.mdbx' here

Access may be accesscs or something else for 2010, not sure.

ChrisHemedinger
Community Manager

You can use SAS Enterprise Guide to export to a new MDB directly.  Use the Export menu above the data grid viewer to begin, and then select Microsoft Access (mdb) as a target file.  For that, you do not need SAS/ACCESS to PC Files.

If you want to export your table to an existing database, or a Microsoft Access 2007/2010 database, then you will need to use PROC EXPORT (DBMS=access or, for 64-bit SAS, DBMS=accesscs).  You can also use a LIBNAME statement with PCFILES or even ODBC (if you have a DSN defined for your Access database).

As to why one of the other "raw formats" (txt, csv, XLSX) didn't work as a "transport" between EG and your Microsoft Access database, I can't say.  But if you can skip that step in between, that's probably better.

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Paul_NYS
Obsidian | Level 7

Hi

I am trying to export to a MS Access 2010 DB using the below statement and am getting an error for "ERROR: FILE= or TABLE= is required and must be specified.". However, I do have a file name indicated. I was just going to create a new table within this DB for the data set, do I need to do something different?

Paul

proc export data=SASd.court_events

dbms=access file="F:\MetricFiles\2013_Changes\metrics.accdb";

run;

acfarrer
Quartz | Level 8

Coincidentally, I am trying to do something very similar but from a Unix workspace server. I was able to follow Chris' advice and Export a dataset from Unix to mdb. However, although I could select more than one dataset, only the first was accepted by the wizard. When I try to export the remaining datasets, there is no option to add them to the existing mdb.

Not surprisingly, running the proc export below from a Unix session does not work:

proc export data = homedata.groupmemgroups_info dbms=accesscs file = '\\todnfs02.tsa.bmo.com\userdata2$\afarrer\home\My SAS Files\UserAdmin\GROUPMEM_INFO.mdb' ;

gives 'ERROR: Server Name is invalid or missing.' since Unix does nor recognize the Windows location.

I am considering Exporting each dataset to a separate XLS which I then import using Access but I will lose the indexes that I was hoping to keep.

Paul_NYS
Obsidian | Level 7

I was able to somewhat successfully run the export using the below Proc, however, about half way through the file, SAS stopped and printed the below error in the log file. I tried to load the second half of the file into a different table in the same DB starting with the records it stopped on and am getting an error for "Error attempting to CREATE a DBMS table. ERROR: Execute: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt..".

When I look at the specific record that the export originally stopped on, it doesn't appear to be any different than others. The records up to the stopping point appear to have loaded successfully. The record set being exported is quite large, 7.3 GB. Has anyone run into this?

Paul

PROC EXPORT DATA=SASd.court_events

            OUTTABLE="CourtEvents"

            DBMS=ACCESS REPLACE;

     DATABASE="F:\access.accdb";

RUN;

15         PROC EXPORT DATA=SASd.court_events

16                     OUTTABLE="CourtEvents"

17                     DBMS=ACCESS REPLACE;

18              DATABASE="F:\access.accdb";

19         RUN;

1 The SAS System                                                      08:55 Monday, December 24, 2012

ERROR: Execute: Unable to insert row

WARNING: File deletion failed for _IMEX_.CourtEvents.DATA.

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           1:01:39.14

      cpu time            1:01:26.30

     

ERROR: Export unsuccessful.  See SAS Log for details.

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

20        

21        

22         GOPTIONS NOACCESSIBLE;

23         %LET _CLIENTTASKLABEL=;

24         %LET _CLIENTPROJECTPATH=;

25         %LET _CLIENTPROJECTNAME=;

26         %LET _SASPROGRAMFILE=;

27        

28         ;*';*";*/;quit;run;

29         ODS _ALL_ CLOSE;

30        

31        

32         QUIT; RUN;

33        

art297
Opal | Level 21

Paul: You very likely have exceeded the MS Access limitations.  It can only handle 32768 records and must be under 2gb.  The specs can be found at: http://office.microsoft.com/en-ca/access-help/access-2010-specifications-HA010341462.aspx

Paul_NYS
Obsidian | Level 7

Hi Art

That is exactly the stopping point and the issue. I guess I assumed Access was more capable than that. Thanks for the link also. The link gave me an idea though, which is to link tables in multiple Access DBs. I only have Access for the time being so linking may have to do.

Paul

Chang
Quartz | Level 8

hi,

In SAS base, I am able to export up to 2938410 obs* 37 columns to a single Access table. This took 8 minute real time.

art297
Opal | Level 21

Chang,

I was incorrect on the 32,767 records limitation, but the 2gb limitation is definitely there.  Yes, there is no defined limit to the number of records.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 11 replies
  • 11356 views
  • 7 likes
  • 6 in conversation