BookmarkSubscribeRSS Feed
PGStats
Opal | Level 21

Hello, can anybody provide insights into the mystery of Access record deletion from SAS, i.e. explain the following log. There are many records with myID=1 in table myTable of Access database myDB:

13   %let path=Consultations\SAS Community\Datasets;

14

15   libname myLib Access "&path.\myDB.accdb";

NOTE: Libref MYLIB was successfully assigned as follows:

      Engine:        ACCESS

      Physical Name: Consultations\SAS Community\Datasets\myDB.accdb

16

17   /* This doesn't work (documented Microsoft ACE 'feature'). Only two records get

17 !  deleted. */

18   proc sql;

19   delete from myLib.myTable

20   where myID=1;

NOTE: 2 rows were deleted from MYLIB.myTable.

21   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.21 seconds

      cpu time            0.03 seconds

22

23   /* This doesn't work either */

24   proc sql;

25   connect using myLib as myConn;

ERROR: You need to specify a data source or prompt for it.

26   execute (delete from myTable where myID=1) by myConn;

ERROR: The MYCONN engine cannot be found.

ERROR: A Connection to the myConn DBMS is not currently supported, or is not

       installed at your site.

27   disconnect from myConn;

ERROR: Connection to the myConn DBMS does not exist.

28   quit;

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

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

29

30   /* This works, however */

31   proc sql;

32   select path into :myPath trimmed

33   from dictionary.libnames

34   where libname="MYLIB";

35   connect to Access as myConn (path="&myPath.");

36   execute (delete from myTable where myID=1) by myConn;

37   disconnect from myConn;

38   quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.19 seconds

      cpu time            0.03 seconds

39

40   libname myLib clear;

NOTE: Libref MYLIB has been deassigned.

PG

PG
1 REPLY 1
kbk
Fluorite | Level 6 kbk
Fluorite | Level 6

Bump. PGStats, did you ever get anywhere with this? Thanks for including a workaround.

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!

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
  • 1 reply
  • 1738 views
  • 0 likes
  • 2 in conversation