July 22, 2003 at 1:20 pm
We have a custom client application for our employess to use, but frequently they have one-time data changes and need to edit data directly using queries, etc.
They are currently using ms-access to edit our data directly with nt authentication and defined permissions/roles in sql 2000 for internal security.
Basically we do not like the way ms-access manages connections to sql, and how it handles locking in the sql database.
I think query analyzer or enterprise manager directly on their desktops may be overwhelming for them, and more of a challenge to manage internal security.
Does anyone have any suggestions for alternatives to ms-access using linked tables to sql 2000? The program would have to be similar to ms-access in ease of use.
Amy Kunesh
July 23, 2003 at 4:09 pm
Anything with similar power will probably have similar liabilities. I'd recommend against Query Analyzer, for some reason users find it pretty daunting. When you say edit, is it just find a single row and edit, or they apply update queries? If the requirements are simple enough, you could throw together a small custom app that would let them do row edits. Another alternative might be to have them build the query in Access, send to you to be applied - which will work, but the Access update syntax is slightly different, you'd have to convert to TSQL, not hard.
Andy
July 24, 2003 at 2:54 am
Using Access you could build a small app with unbound forms. Create an insert, update and delete procedure in SQL Server. SAMS Microsoft Access Developer's Guide to SQL Server has examples of how to do this.
If it was just a question of updating a table with known data, the simplest way would be to have an unbound form which modified a passthru query. The passthru query could contain an SQL String or procedure and parameters.
July 24, 2003 at 9:39 am
Unfortunately the one-time data editing that they do for unique processes is usually many records in many tables- not just find one row and change one field.
Since we have upgraded to MSAccess/XP/2002, it seems like we are having less of a problem with connection management and locking in our sql database.
Although, on occassion they need to analyze/sum/group/sort a large amount of data, and this has caused some time-outs on our servers. So, a couple power users have tried to bring down copies of this data into local ms-access tables to analyze. Not a great option.
I can analyze the data from query analyzer using many t-sql statements and frequent temp tables, and the perfomance hit on the server is much less.
But we are basically trying to have a non-programmer power user here be able to do data manipulation from their desktops without having to be a dba, and without the performance hit to our production server.
I was thinking that a couple power users could use sql pass-through queries in ms-access, but it seems you can not put multiple sql statements into one pass-through query. So, my method of multiple statements and temp tables would be harder for them to accomplish from ms-access.
Amy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply