Upgrading to SQL Server Backend - which Frontend to use? 2

[mrf1xa]

We have a large Access database which is a bit too slow for the growing number of users and records it now holds, so we have finally got agreement to upgrade to SQL server for the backend.

My question is what is considered the best thing to use for the front end - can we continue using the Access Db for this ad just conenct it to the SQL server of would we be better off writing something in Visual Studio VB or similar?

Many thanks

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[dhookom]

I would continue with Access as the front-end unless your needs have changed. I would avoid Access ADPs in favor of simplying linking your SQL tables. You might want to take advantage of pass-through queries and much of the SQL Server functionality that is not available in Access.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

I agree with Duane. This is exactly what we have done with dozens of applications, and not only was the transistion seamless, many of the tasks formerly done in the mdb/accdb are now done in T-SQL on the server and run much faster and more efficently.

An làmb a bheir, ‘s i a gheibh.

 

[Hap007]

And I agree with Duane and Genomon,

Once you convert the backend to SQL, the FrontEnd should work just fine.

You may need to replace functions like DLookup, but with a few lines of code, you can write your own MyDLookup equivalent function.

If you open Recordsets in VBA Code, you may need to add a few open switches to make the DAO SQL statements work. An example might be:
Set rs = DB.OpenRecordset("qryPeriodInfo", dbOpenSnapshot)
might need the dbSeeChanges switch added
Set rs = DB.OpenRecordset("qryPeriodInfo", dbOpenSnapshot, dbSeeChanges)

Then as Geneomon stated, if you find areas of your application that are slow, you can always write T SQL and Stored procedures to speed up the slow processes.

I also converted an MS Access App to a MS Access Front End and a MySQL Backend. This took a little more work, but there was no server software expense for MySQL. So there are many ways to upsize to SQL and maintain the ivestment in your MS Access frontend code, forms and reports.

FYI, make sure your Access Data is clean (Null and Blank dates as an example) which should never be allowed in Access tables, sometimes get entered or saved. Those entries should be cleaned up prior to converting to SQL.

The app will be industrial strength when you are finished
Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[mrf1xa]

Thanks to all who have replied, especially Hap for those examples and pointers - a star for you!
Our main issue on speed is the poor network connection we have to live with - no scope to improve this presently. This is so bad it even prevents us splitting the Access Db front and back end - have tried running it this way and it is significantly slower than having the whole app on the server and having our users (10) use it directly. I know there are a whole host of reasons why this is bad practice, but network speed makes it simply unusable the "correct" way e.g. a search function that is almost instant on my C:\ drive takes around 10 seconds with the app on the server, and more like 45 seconds with the Db split. As there is more searching than updating done in this Db it is an issue for us

With this background does the advice above hold true? Thanks again!

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[alvechurchdata]

If it's network speed that's the problem then genomen's advice sounds good. Get as much processing as you can moved to T-SQL on the back end so that there's less traffic on the network. It's much better to have SQL Server send ten records to the frontend than it is to download a million records and then select the ten you need on the client.

As for the choice of front end - stick with Access for the moment. Use the familiar forms and reports whilst you get the data (and the queries) migrated to the back end. You will then have the option of writing a new front end if it turns out to be necessary but doing the transition in two steps is very much safer than jumping to a new database and a new front end in a single wild leap.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lameid]

I don't think it is network speed that is the problem so much as network setup.... I am betting you are not using Windows Server File shares but Netware as this is the only place I have seen problems with splitting the databasse on a network (Also the only place I have not been the network admin too...).

In my case on Netware my hunch is that there is some sort of File caching going on and lack of perisistent connection to the backend (I think it must be downloading the entire Backend everytime it makes a connection to the file). Because File caching is very bad in a network environment for multi-user environment, I just gave up and never tried to resolve beyond raising with IT... Ultimately my solution was move to SQL. If you know what the homophone soup as a client platform, this means you too.

Having said that if you simply upsize your database and link everything to SQL server you should expect everything to basically work (note Haps notes) however Access will basically do full table scans in some cases of the SQL database when it runs queries locally and can be slower than a file share (or faster depending on lock contention).... Which is why pass-through queries are a boon. My point is you may have to make your front-end SQL friendly before you eliminate performance problems if it is literally network speed.

I have worked with both MDB and ADP front-ends I am in agreement that MDB is the way to go, as is Microsoft as are many posters here that I trust.

Assuming your problem is network platform, if you can go with a Windows file share instead of your problem platform, I recommend putting the backend MDB on its own logical drive if possible to prevent file fragmentation issues. Ultimately SQL is the best platform, but this option could be cheaper depending on your environment. Alas, where I am, it is cheaper but not bureaucratically achievable.