5

We currently have a few users who have been using Access very succesfully to throw around large amounts of data.

We've now got to the point where the data is just too large to be held in Access, as well as wanting to hold it in a single place where multiple users can access it.

We have therefore moved the data over to SQL Server.

I want to provide a general tool that they can use to view the data on the server and do some simple things like run queries and filters and export the data for offline manipulation.

I don't want the support headaches that might come with rolling out SQL Management Studio, and neither do I want to have to create an Access database with links for each current database or ones that are created in the future.

Can anyone recommend a simple tool that will connect to a server, list all the databases and allow a user to drill into a table and look at the data.

Many thanks.

2
  • 2
    How many is a "few" users? Why does deploying SQL Management Studio cause you "support headaches?"
    – shufler
    Aug 5, 2009 at 16:33
  • 3
    Few users is 3 and they don't like SQL management studio because its more complex than access. Sorry, but those are my requirements and I am looking for an alternative, not a discussion on why my users are a pain in the butt! :D
    – Martin
    Aug 5, 2009 at 20:45

11 Answers 11

7

I use this program: Database .NET It doesn't require any installation, just run the .exe and you get most of the functionality of SQL Management Studio.

3

I have to ask, why hundreds of databases and only three users? I am curious if these databases are all similar to each other and if so might you not be better off consolidating the data into a smaller number of databases (with some modification to implement segregating the data from each of the original datasets). Then you could easily use Access as the front end. If I were in your position I would certainly want to leverage the users comfort level with Access rather than having to retrain the three users in some new tool.

Gary

2

Why not stay with MS Access as front end? Just re-create all your tables as linked tables and you have all the same functionality like you used to have.

Edit: just saw you explicitly mentioned that in your question. I beg to differ though. If the user can browse a sql server and their databases with any tool, they can do it with MS Access especially with the linked table wizard. I don't think you will find something "simpler" than that.

2
  • We have hundreds of databases that are all to be consolidated on a single server - the problem is Access has a terrible interface for linking all tables (and there may be hundreds), not to mention no easy way to flick between databases.
    – Martin
    Aug 5, 2009 at 15:57
  • Do the databases all have the same structure? Maybe an ADP would be a better front end to all the SQL Server dbs, since it doesn't really use linked tables. Sep 25, 2009 at 0:23
2

How about an Access Data Project? We've had a couple of these running for years just as a front-end a couple of users can use for custom queries. The learning curve should be minimal, although the logins should have as limited rights as possible.

2

Try Query Express. It's a Query Analyzer clone that's packaged as a single 100KB .exe download.

Query Express

2

Microsoft LightSwitch is [INCREDIBLE] at developing rapid UIs based on SQL Server databases for both General CRUD Operations as searching an analyzing tables.

Download the trial here, $199 worth every cent, and if your firm has VS Professional or enterprise, it's free.

http://www.microsoft.com/visualstudio/en-us/lightswitch

http://www.youtube.com/user/swanbryan?feature=mhee#p/u/9/559j-9HVPyU

1
  • As it happens this is the way we are intending to go. We have MSDN licenses so already have access. Thanks.
    – Martin
    Sep 26, 2011 at 16:19
1

I'd recommend using Excel. I believe this works in versions prior to 2007 but I'll give a 2007 how to.

  • On the Data tab select From Other Sources
  • Select From SQL Server
  • Fill in connection information
  • Select the table you'd like to view

A more through answer can be found in the Office Help Article: Use Microsoft Query to retrieve external data

1
  • Again this is a much more convoluted route to view the data than the users are used to. I want them to be able to click a database and see all tables and then click a table and instantly see the data.
    – Martin
    Aug 5, 2009 at 15:58
1

I would definitely go with MS Access as a front end. Setup multiple databases with logical groupings of linked tables.

This means that it relatively transparent to the users, they shouldn't notice any major change. The skills they have learned using Access are still being used.

1

You could set up a front end to SQL Server using ASP.NET.

I currently do this with a few clients who want to have data entry capability as well as running reports, queries, etc.

You could have a page with a drop down box to select which database to gather data from and a Gridview to display the data. You could also set up some parameterized queries where they can select date/values ranges for the various data that would come up.

Host it on the server and multiple people could access it from their machines.

0

Check out LINQPad. It's light-weight, inexpensive and you can do everything from T-SQL to .NET 3.5.

0

I also recommend LINQpad. It's been a relatively new addition to my toolkit. It is exceptionally powerful. I use it for SQL fairly frequently (despite its name it doesn't over-deprecate SQL), but also for writing small C# programs and making use of LINQ. The free version is superb, but the Auto Completion license is well worth it. If possible use the latest v4 Beta which supports the .NET Framework 4.0 and also includes other syntax outlining.

If your users are potentially capable of working with SQL Query Analyzer they could be very at home with LINQPad and this would enable you to roll out .linq (LINQpad) files with sample queries for working with the data.

However, on further reading of your question, perhaps a basic (even 'out of the box') ASP.NET Dynamic Data deployment would work well for you. It has some great scaffolding support, shields users from complexity but gives you a lot of extensibility.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .