Converting An Access Database To Sql Server (1 Viewer)

[Groundrush]

Hi everyone,

Need some advice please

I have a database (v2003) with a front & back end, each user has a copy of the front end & the back end is situated on a local server.

There are about 30 users each having their own user name & password, but there usually never more than 14 users logged in at any one time.
Each year I start a new database avoiding the database getting bogged down with too much data, this is the 3 year of use with only a few minor problems.
This year April will be the start of the 4th year.

Anyway our IT dept wants more control over my database & they want it locking down & securing onto SQL server (off site) but I don't think it's as simple as what some may think.

I have no experience in SQL & limited vba programming skills. I have no idea if the current database will actually work as I have a feeling that a lot of my queries VBA code will not work & a lot of re programmuing will be needed.

The database consists of about 80 tables, 300 queries, 21 modules, 128 forms & 50 reports.

I know there is an upsizing wizard in access but do not have a server set up yet to try it out.


Can anyone please tell me what to expect - pros & cons of moving to SQL & what I should look out for, will I have to redo all my queries, modules & vba coding?

Also what training should I ask for?

I am not very good at VBA but very willing to learn, any suggestions?

Thanks

 

[PeterOC]

Hi Groundrush,
Moving databases to sql server can actually be more straightforward than you would think. I can be as simple as importing your database tables into a sql database and linking to the tables via odbc.

I've done a similar project by using an upsizing wizard but that was going the route of changing the database into an access data project (adp).
I wouldn't advise changing your database into an adp.

Before embarking on a project like this though I'd advise getting your company to put you on some sql server training and for you to read as much as you can online about this.

I should have a lot of useful links somewhere. I'll see if I can dig them out for you.

P

 

[boblarson]

some sql server training

This is a great place:

LearnDevNow

(US $99 for a year subscription) for training - the videos are fantastic. I've just renewed for another year.

 

[Groundrush]

Thank you.