Creating a quick and dirty interface to a SQL Server database

[stephen2005]

This is probably more of a "where do I begin sort of question". I have a database containing about 2.5 million records which I want to be able to View/Add to/edit. Just simple stuff. What I am looking for are some basic tools to do that without writing too much code, or better yet, using no code at all. (I only know the absolute basics of using ASP.NET). Is there an existing application that can plug into SQL Server to read/edit data? I understand that MS Excel has some capability to do that in a crude way. Are there any other Microsoft applications or technologies which could do that? Even code generation tools would be usefull.

 

[bborissov]

Check for SQL Server Management Studio Express.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JoeAtWork]

MS Access with linked tables to SQL Server is a common solution.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[philhege]

It's fairly simple to use the Object Datasource in .NET. Associate your control source binding with the ODS. There's an ODS wizard that associates the CRUD actions with methods in your application, which in turn would call stored procedures in the database.

I'd shy away from Access for a table of that size. AAMOF, I'd shy away from Access for any sort of linked table solution that allows the user to open the table in datasheet view.

Good luck!

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[stephen2005]

Thanks for the suggestions guys. I'm using MS access at the moment and have found that to be unsatisfactory due to performance issues and the fact that the database is currently nearing the 2 GB access database size limit. In fact I don't much like access period and would prefer to completely avoid using it if possible.

I had a quick look at Object Datasource. I would prefer to avoid getting involved in writing too much code, unless most of it could be automatically generated using a tool of some sort. That sounds close, but it would mean that I would probably have too get involved in writing a significant amount of code. My .NET skills are basic at best.

I am in the process of evaluating SQL Server Management Studio Express.

 

[JoeAtWork]

Studio Express was never meant to be a GUI to your database. It's a database management tool.

How would a user be able to find any particular record out of 2.5 million? Or do a search? The best you might be able to do is create some stored procedures, but running these is probably too low level for the typical user.

It's unusual for me to defend an Access solution (seeing how I've trashed it in other posts), but it fits your stated requirements (little or no programming). And you can hide your linked tables from the users (though it is easily defeated, but that is the price of Quick & Dirty solutions). The poor performance you have experienced may likely be due to an unoptimized database design.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[tlbroadbent]

I realize this is a an older thread but wanted to add this post.

Rather than use linked tables in MS Access, create an Access Project for the SQL Server database. This provides native connectivity, avoiding ODBC and its performance problems, and allows you to modify data (if your login has permission). It also allows you create and alter objects, such as tables, views and stored procedures right in Access. This also requires appropriate permissions.

The only purpose for an Access project is to provide a GUI front-end for SQL Server. Check out this Access project article on SQL Server Central.

Other advantages: The interface is simple and uncomplicated. Permissions are controlled at the SQL Server. No coding is required but you can create forms, reports and modules in an Access project. This provides great flexibility.

Creating a project requires the appropriate version of Access to match your SQL Server version. Access 2007 is compatible with SQL Server 2000/2005. I don't know about SS 2008. Earlier versions of Access are not fully compatible with SS 2005. Access 2000 and later versions work with SQL Server 2000.

I don't use Access projects extensively but they are handy when I need a quick solution.

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)