DATA Step, Macro, Functions and more

Deleting records from Access DB

Reply
Respected Advisor
Posts: 4,646

Deleting records from Access DB

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
Contributor kbk
Contributor
Posts: 29

Re: Deleting records from Access DB

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

Ask a Question
Discussion stats
  • 1 reply
  • 422 views
  • 0 likes
  • 2 in conversation