To start off, can I just say that I know very little about SQL databases but I have a MS access database which we are outgrowing and I think we will need to make some changes. I was wondering if some one could offer some advice. There are many programs available for upsizing to SQL but I presume these are for the back end only, is this correct? Is upsizing a bad idea or a very difficult one?
I was also wondering about keeping my Access front end and connecting it to a SQL back end, is this a good idea?
How 'bout Visual Basic on a SQL Server back end? We are converting, albeit at a snails pace, our processes using Access backend to SQL Server. This is a different(another job) than the other issue I posted on re: Read Only in SQL Server Stored procedures. Here at least, I have full privileges, even updates on table than my a.m. job. Is there a good tutorial page on VB with SQL Server? I know both to some extent though my VB is rusty.
I have upgraded Access apps to SQL Server a few times and been surprised how much the built-in Upgrade Wizard can do correctly for you. I'd give it a try. Note that you don't want to convert to an Access 'Project' file if you need to have front-end tables as well as a back-end database. You can't use front-end tables in the .adp files.
Depending on the complexity of your relationships in the DB you can get almost everything converted over with the Wizard. Some basic testing will show you where things flopped. You won't have too much trouble figuring out how to re-create things in SQL Server--it's very user-friendly.
If you will keep your front end in Access I highly recommend the enormous Access 2000 Developer's Handbook, Volume 2: Enterprise Edition (Litwin, Getz & Gilbert) Hopefully they have a 2002 version out by now but the 2000 version saved my you-know-what many times and gives great info on how to work with SQL Server from Access.
We have several large production apps running in Access to SQL Server. In terms of SQL Server and Access playing nicely, they generally do. Using the upsizing wizard will help ensure that the back end is set up to talk to Access properly.