Here's what I personally have prioritized when I was in your situation:
1. I'm a big fan of moving everything I can to a server db regardless of how small the database is. There are many advantages to having a db on a server. Backups is a big one (especially point-in-time backups.) But it does mean investing "some" amount of time into learning how to use the server db. I would use SQL Server 2000 or 2005 (versus Express) just to avoid any problems you may have with limitations on SQL Server Express. It took me less than a few days to convert MSAccess tables into SQL Server, establish an ODBC connection, link them into the mdb, and get everything up and running when I first learned how (although establishing security on the server db probably took the longest.) I personally prefer SQL Server 2000 (which you could search and find) as you can get this rather cheaply. I found I could pickup learning SQL Server 2000 very quickly (again, within a few days) so the $ spent was beneficial for the licensing. It took me a lot longer to pickup MySQL and Oracle where MySQL may be free but I spent a fair amount of time learning it and dealing with problems. Also - I did have problems as well with MySQL and ODBC connections. And Oracle, well, I never really "got" how to use Oracle very well. Plus, the MSAccess upsizing wizard works superbly with SQL Server (it takes 1-2 minutes to convert mdb tables into SQL Server) so I'd personally try to stick with SQL Server versus MySQL or another backend. (I think I would have understood MySQL/Oracle better if I had a lot of unix experience.) If you've adapted fairly well to MSAccess 2003, you'll find SQL Server 2000 fairly easy to use. If you've adapted well to MSAccess 2007 or are new to the Server world, you may find SQL Server 2005 is a good choice. If you're not an MSAccess fan, you may have better luck with MySQL, Oracle, or another db server. But definately factor in the amount of time you're anticipating learning it (by trying out the demo versions) along with the cost of it.
2. Since it sounds like your MSAccess app is fairly complex or could be, I'd worry about re-writing some of the queries to stored procedures/views (ie. server-side) at a later time (especially since these could be done at any point and done form by form.) At this point, I'd simply link the tables into the mdb as nothing would really need to be changed vba code-wise. And there's nothing wrong with using linked tables - they work very well. It's the table/relational design (and MSAccess form design) which can bring down a system to it's knees when accuring mass amounts of records. Designing a good structure (along with good form designs) in my opinion is essential or you're just designing work-around after work-around.
3. Once SQL Server is up and running, the db is converted and the tables are linked into the mdb, I'd then (personally) focus on converting some of the forms which load/function slowly into unbound forms (if I were dealing with recordsets in the millions.) 25 meg worth of data in an mdb file doesn't sound like it would require unbound forms. This though would mean an extremely fast-running mdb file (if I had slow connections to the network, it would greatly help). But this is after I've exhausted other things I could do with the form design to increase speed and function. Again, I'm not sure then if I would really focus on converting the queries/code into stored procedures yet as this is probably one of the more time-consuming processes (depending on how quick you pickup the coding and setup.) Once you've designed unbound forms (if need be), I think you'll find the speed of the forms extremely fast and you need to look at whether you want to focus on improving the entire application or convert queries to server-side scripts. Designing unbound forms can be very time-consuming in itself so don't make this decision lightly. I've had some extremely fast good running applications (with 5+ million records) without using stored procedures/views but instead just linking the SQL Server tables and then designing unbound forms. But I'd only go the unbound form route if I were dealing with extremely large recordsets. I've gotten along just fine with many (100+ users) using an mdb with linked tables and bound forms without going any further. But this is your decision on where you'd want to go from this point. It's always good to learn how to write server-side scripts stored procedures/views/triggers on SQL Server but I wouldn't necessarily say this is a necessity to get a good fast application up and running. I might though instead focus on unbound forms to increase speed of forms.
4. Since data security/backups are a priority for me, I might focus on learning how to do tranlog backups with SQL Server, indexing, good relational designs, security and so on. Some of it is similar to MSAccess but there are a few differences. There's a lot of good stuff to learn about SQL Server and since this is housing the data, I might focus on things I can do to improve the safety of the data (ie. if somebody were to delete a mass set of records, how to restore the data using Point-in-Time restore of tranlogs so I could restore all the data from say 10:00 am this morning.) Again though, this sort of thing is a priority for me and I think you'll need to decide whether this kind of stuff is a priority for you or other things are a priority.
5. One other step I forgot above is to make it so I could easily update the mdb code without affecting users. For example, it's very time-consuming running around and have everyone close out of the mdb because 1 person has that mdb open and locked (or mde which I prefer to use) and you need to copy new code. I very much like the "cloning" script which is found in the code bank (http://www.dbforums.com/showpost.php...6&postcount=19 - if you're interested). I use this technique because it's then easy for me to update my code and copy it to the network "without" having other users close out of the mdb/mde file. Whatever method you use, I think it's important that you establish a system whereby you can easily make updates to the code and copy that new code for users to utilize without going through a bunch of hoops in order to copy that new code. Especially since you don't want to be spending time running around getting everyone to close out of the mdb just so you can copy and have them test your new code each time.
Again, this is what I find important. Your priorities may be different.
Last edited by pkstormy; 11-27-08 at 21:26.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)