User Interface for SQL

[jeffshex]

Hello all!
We currently have an Access DB and are planning a conversion to SQL. Obviously all of our user interface is done with the Access forms.
Our goal is not only system stability out of this conversion, but also speed up the performace for the users in offsite locations.
I know we can still use the Access forms as the user interface, but I have heard that Access will still pull down the complete SQL table just to perform something minor - thus making it just as slow as before.

Any recommendations on what to use for the front end user interface? Our resources are limited and we don't have an in house programmer.
If programming a home grown app is the best/only way to go - what language?

Thanks all!

 

[mrdenny]

You can use what ever front end you can write. As long as it can make an ODBC or OLE connection to the database.

Some of your options are:
For a Win32 client:
Access
Visual Basic
C++
Visual Basic.NET
C#.NET
Python
Java

For a web app:
ASP
PHP
JSP
Java
ASP.NET

SQL Server doesn't come with an interface development package. Your only option will be to write some sort of front end. If you know access, you can use access. If done correctly an Access interface can work well. However you can not simply bind forms to a linked table to get it to work well. If all you do is bind forms to a linked table your performance will not be good.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jeffshex]

All of our users are familiar with the Access interface. It would save time, money, and frustrated users to keep it the same.
I know about linking the SQL tables in Access, but I'm unsure as to get the performance out of it. If you could expand on your thoughts of this I would be very intrested.

The downside to it is that some of the remote connections are stuck on a slower connection such as DSL.

I only know the basics of VB programming, so I'm sure whipping up a seperate user interface in whatever code would probably take a long time.

Here are some of the concerns I have heard.
1. The Access front end will still pull the entire table over, even if you are just calling for one row.

2. If the above is the case, and two users happen to query the same row for an update at the same time; the last one to get submitted will obviously overwrite the other.

I need to get a better feel for things before I come to a final decision on things.

Please let me know your thoughts on the matter.
Thanks again!

 

[r937]

1. no

2. that happens with every database, not just access

the problem is "query for update" -- don't do that

r937.com | rudy.ca

 

[ceh4702]

You may need some outside help. You dont just go out and purchase MS SQL 2000 and hope you can find some minimal help to get it working.

Let me introduce a Concept Model.

SQL Is just one piece of the puzzle. You need a front end product, MS SQL Server, and also a Database. If you are not going to use MS Access, then what are you going to use for a database? Maybe you will still use access with Microsoft SQL; you didnt explain that part.

Microsoft has books on Using Microsoft SQL Server with other .net products like Visual Basic or with Access as a database.

I have never deployed a database with Visual Basic, but I have used it before. If you are using Access you are using Visual Basic to some degree. You would still have to learn some code to go behind the screens and how to connect the database using ODBC Drivers.

Another option might be to build A MS DHMTL web based front end. Basically just some Web forms with some code behind them and a connection to a database. This would be a web based itranet/Interment capable product.

I think you really need to find some help from some developers locally in your area. You need to find some people who do this sort of thing like from Oracle, or others who work in your industry sector and set up some presentations so you can see the products available. You should not just pick a product and hope for the best.

If you do not like my post feel free to point out your opinion or my errors.

 

[jeffshex]

Some things are out of my control and we have to use what's available to us. SQL server has been purcahsed awhile back and that is what we have to use - I can't get around that.

I've been reading up on the Access Data Project(ADP). It seems that this will provide the front end we will use for the time being. Apparently the only thing that stays in the Access file is the Form, Macro, Report, and VB Code. All the other tables, stored procedures, views, etc... will be ran on the server.
So we will be seeing how that goes.

In the long run, yes, we will move to a .Net or something perhaps of a web interface.

So if anybody has some good resources or knows of some excellent books about this ADP feature - I'm interested.

Thanks to everyone though on their input...keep it coming!

-JB

 

[mrdenny]

If you check out the Access forums you'll probably get better info on ADP than you will in here.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[RiverGuy]

Access ADP forum:
forum958