Access 97 & SQL Server 1

[RichM1]

I have a large task to undertake, or atleast I think I do. I have an access bar coding program that I wrote a number of years ago in Access 97 which consists of a front end (forms, reports, modules, etc) and a backend (.mdb) which consists of all data. The company would like the program transitioned into SQL 2000 of which I don't know much about. In the access program I did a lot of SQL statement writing. Should I convert from Access 97 to 2000 (.mdb) or 2002 (using .adp)? I've been lead to believe that upgrading from Access 97 to either 2000 or 2002 is strongly preferred. I've used DAO in the current program and thinking of converting to ADO if I choose Access 2002, is this the wise thing to do? The current database has about 10-15 users, it isn't my idea to convert them but my customer is a large company and are requiring this. Any help would be greatly appreciated!! Thanks....

 

[Norris68]

This basically what I did for my company. The database was based on Access 2.0 and regularly crashed, taking the database with it. The solution would not have been possible with Acc97.

[ul][li]Database was already split into FE & BE in Acc 2.0.[/li]
[li]Run upgrade wizard on BE -> Acc2K staging MDB[/li]
[li]Run upsizing wizard on BE & transfer to SQL Server 2000. (Repeat with tweaks until successful)[/li]
[li]Run upgrade wizard on FE -> Acc2K MDB[/li]
[li]Spend several days modifying all DAO code to make it ADO.[/li]
[li]Create new ADP project, linked to SQL-based BE & import all of the converted FE stuff from MDB.[/li]
[li]Spend several more days ironing out minor problems.[/li]
[li]Sit back and enjoy the fact it doesn't crash every day or two![/li][/ul]

 

[KenReay]

Hi

While the actual conversion to make it run with (say) Access2002 and SQl Server2000 should not be too difficult (as indicated by Norris68), if the reason for doing this is to improve performance, the result may be disappointing. If you are switching form a file server to a client server approach you need to examine the desgin to minimise the volume of data transfered over the network, and maximise the work done on the server. Typical Access applications with bound forms populating with entire recordsets do not sit well with a client server approach.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RichM1]

Thanks for the replies. KenReay I totally agree that I may have to change a couple of screens that do populate with the entire recordset. I assume I can still do general search or queries on the SQL Server database to populate forms as necessary.

 

[markgrizzle]

In my case, a good compromise on the bound form issue was to modify the forms so they opened with a sql statement designed to return no records.

A control on the form, typically a list or combo, when selected, would then load the selected record.

 

[Terpsfan]

On this issue I would suggest you read the Access 2000 Developer's Guide to SQL Server. It's an excellent resource for migrating a project to SQL Server from Access. The thing to keep in mind is you will more than likely want to redesign your database architecture to meet a client-server or n-tier approach, separating the project into presentation, business and data tiers. This will result in the best performance of your database and code and make the whole project more modular and manageable. After a lot of trial and experimentation, I decided that I would rewrite my Access front ends in Visual Basic or .NET, relying heavily on class modules. Compiled code runs a lot faster than pseudo-compiled code. I also basically wrote one class that can populate, update and delete records for any form. That's the beauty of classes and code reuse. What I suggest is keeping a connection for only as long as you need it. I design unbound forms, disconnect the recordset from the database and then use a stored procedure with an ADO command object to update or insert. This allows for more concurrent users. Just one approach among many.