vierhout@unity.ncsu.edu (Crystal Vierhout) wrote in message news:<001501c11ab8$d84aa600$a5250198@SASSERVER>... > Hello, > > Is there a way to update a mcaccess table from within SAS? We would > like multiple users in the database at one time. Is it possible to hold > the row and update in place? > > > Crystal Vierhout > Dairy Records Management Systems > 919-661-3139 > > --

Crystal

SAS reads/inserts rows/deletes etc. quite well via ODBC & proc sql. I've not tried to update existing rows though, but it should be possible.

You have to watch the dates/times as access only has datetime fields. The documentation in the online help is not as good as it could be.

Main problem when inserting from SAS is that you'd think pass-thru would work (or at least I did), but access doesn't see the sas data file that you're running the updates from. Error messages are cryptic, to say the least. You need to define the Access file via an ODBC libname statement then write the update SQL as if it's a SAS file that you're updating.

I've run updates when other users are in the MSAccess system, but these were record adds/deletes, not updates of existing ones, just need to ensure that exclusive access isn't set within ODBC & that all users have sufficient authority under NT. Not too sure what happens if they try to update records while you're at it at the same time. That's asking for trouble as you'll probably hit the lost update problem.

I can post some sample code on Monday if noone else has done so by then.

Good luck

Kevin