Deleting records from Access DB

PGStats — Thu, 09 Apr 2015 20:35:45 GMT

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;

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

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

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

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

40 libname myLib clear;

NOTE: Libref MYLIB has been deassigned.

Re: Deleting records from Access DB

kbk — Tue, 05 Apr 2016 17:23:04 GMT

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

topic Deleting records from Access DB in SAS Programming

Deleting records from Access DB

Re: Deleting records from Access DB