moving form access to sql server

[RabbieBurns]

Currently we have a series of 8 databases that are all just .mdb access databases, which basically is our home made reporting system.

 

Once one round of reporting is completed, the mdbs are archived and replaced with blank ones etc.

 

The frontend for inputting the data to the databases is done through ASP.

 

Im wondering if it might be worth as a project changing it so it uses SQL server. Ive never used sql server before (used mysql a bit though) so Im not really sure what things I would need to consider if I was to decide to go down this route.

 

Would any of the SQL queries in the ASP need changed or would it be a simple case of changing the data sources?

 

Can sql server import the existing Reports that Ive made in access for the printing of the students' reports?

 

Would it make archiving each term any simpler?

 

Basically just looking for some pro's and con's, but Im inclined to think the old 'if it aint broke dont fix it'

[My220x]

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/accessmigration.mspx

 

Tells you about migrating to sql server from access

[srochford]

Most of the SQL queries will work - you just have to change your connection string - but watch out for the "like" wildcard - it's "*" in Access but "%" in T-SQL

 

Instead of replacing the databases each time you could just have a year/term/whatever field which you select on - that might just make it easier if ever you want to go back to a previous year etc data. With SQL you don't really have to worry about the size of the file (whereas Access just gets horrible with use)

[RabbieBurns]

thanks for the replies. I think Im just going to leave it as it is for now.