LIBNAME error

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

LIBNAME error

Hi,

 

I am trying to export a SAS dataset to MS Access using the Libname statement. When I code this:

libname DatIn ACCESS PATH="C:\Temp\ExpAcc.accdb";

I get this error:

 

22   libname DatIn ACCESS PATH="C:\Temp\ExpAcc.accdb";

 

ERROR: Connect: Class not registered

 

ERROR: Error in the LIBNAME statement

 

According to page “The LIBNAME Statement for Access and Excel on Microsoft Windows” in the SAS/ACCESS help, this is one of two options for assigning a libref to an MS Access file.  The other option is

 

libname DatIn "C:\Temp\ExpAcc.accdb";

 

both of which produce the same error.
Thank you,
John

Accepted Solutions
Solution
‎07-07-2016 08:45 AM
Contributor
Posts: 20

Re: LIBNAME error

[ Edited ]

Hi Tom,

 

In my case, changing either installation is not an option. As it turns out, I should not have been attempting to use the LIBNAME statement at all.  The context is that I was trying to export a SAS dataset to MS Access, and I thought I could use the LIBNAME to do so, however, in my case, there is a good probability that the MS Access database I'm trying to create already exists, so the LIBNAME statement would not work. I needed to use PROC EXPORT so that I could specify the REPLACE option.

 

My solution is like this:

 

proc export

                data = SAS_DS

                dbms = accesscs

                outtable = EXPACCESS

                replace;

                database = "c:\dir\NewDB.accdb";

run;

 

For me, the key was the value of the DBMS= option.  When I read the SAS/ACCESS help page entitled

File Format-Specific Reference for the IMPORT and EXPORT Procedures, it said

 

"For an existing .mdb you can specify DBMS=ACCESSCS when using the client/server model and SAS identifies the version of Access for you."

 

I mistakenly interpreted this to imply that I could not use it with .accdb files, but you can, as explained in the SAS/ACCESS help page entitled "PROC EXPORT".

 

Thank you again for your help!

 

John

View solution in original post


All Replies
Super User
Super User
Posts: 6,343

Re: LIBNAME error

Your SAS installation and you Microsoft Office installation are using different number of bits (32 vs 64).  Check with your system admins to see if you can change one or the other.

 

Contributor
Posts: 20

Re: LIBNAME error

Thank you Tom!

Valued Guide
Posts: 505

Re: LIBNAME error



* Make a copy of the mdb just in case; * I prefer the older "full" version of access because access meta data is available; * you can always move the data to the newer version; x "copy d:\mdb\db.mdb d:\mdb\want.mdb"; * prep SAS dataset for R; data "d:/sd1/class.sas7bdat"; set sashelp.class; run;quit; %utl_submit_r32( library(RODBC); library(haven);
class<-read_sas('d:/sd1/class.sas7bdat'); myDB <- odbcConnectAccess(""d:/mdb/want.mdb"",uid=""admin"",pwd=""""); sqlSave(myDB,class,rownames=FALSE); ); libname mdb "d:\mdb\want.mdb"; proc contents data=mdb._all_; ;run;quit; libname mdb clear; he CONTENTS Procedure Directory ibref MDB ngine ACCESS hysical Name d:\mdb\want.mdb ser Admin DBMS Member Member Name Type Type class DATA TABLE Data Set Name MDB.class Observations . Member Type DATA Variables 5 Engine ACCESS Indexes 0 Created . Observation Length 0 Last Modified . Deleted Observations 0 Protection Compressed NO Data Set Type Sorted NO Label Data Representation Default Encoding Default Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 3 AGE Num 8 AGE 4 HEIGHT Num 8 HEIGHT 1 NAME Char 255 $255. $255. NAME 2 SEX Char 255 $255. $255. SEX 5 WEIGHT Num 8 WEIGHT
Contributor
Posts: 20

Re: LIBNAME error

Hi Roger,

 

Please see my reply to Tom. In my case, the MS Access database I'm trying to create probably already exists, so I would not be able to use the LIBNAME statement. Your solution looks interesting, but, in my case, if I wanted to connect to an existing MS Access database using the LIBNAME statement, I think an easier solution would be to use the SAS PC Files Server in the libname:

 

libname mylibref pcfiles path="C:\dir\MyAccessDB.accdb";

 

Thank you,

 

John

 

 

 

Solution
‎07-07-2016 08:45 AM
Contributor
Posts: 20

Re: LIBNAME error

[ Edited ]

Hi Tom,

 

In my case, changing either installation is not an option. As it turns out, I should not have been attempting to use the LIBNAME statement at all.  The context is that I was trying to export a SAS dataset to MS Access, and I thought I could use the LIBNAME to do so, however, in my case, there is a good probability that the MS Access database I'm trying to create already exists, so the LIBNAME statement would not work. I needed to use PROC EXPORT so that I could specify the REPLACE option.

 

My solution is like this:

 

proc export

                data = SAS_DS

                dbms = accesscs

                outtable = EXPACCESS

                replace;

                database = "c:\dir\NewDB.accdb";

run;

 

For me, the key was the value of the DBMS= option.  When I read the SAS/ACCESS help page entitled

File Format-Specific Reference for the IMPORT and EXPORT Procedures, it said

 

"For an existing .mdb you can specify DBMS=ACCESSCS when using the client/server model and SAS identifies the version of Access for you."

 

I mistakenly interpreted this to imply that I could not use it with .accdb files, but you can, as explained in the SAS/ACCESS help page entitled "PROC EXPORT".

 

Thank you again for your help!

 

John

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 466 views
  • 0 likes
  • 3 in conversation