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
