More Access to MSDE questions
-
My company is considering moving from Access(Jet) to MSDE and eventually SQL Server. I've spent a long time looking around on the internet to see what I can learn, but I still have some major questions.
I'm not sure what to use on the front end (especially as it relates to the forms and reports I've developed). A lot of what I read seems to imply that you can just stick with Access, but some people seem to think that is a bad idea. We are planning on transitioning to a more web-based user interface. I haven't done any web development in several years (and that was limited), so I don't really know what my options are, and I can't seem to find much on the subject. I would prefer something fast, flexible, powerful, and reliable, and of course the cheaper the better. I am a programmer by trade, so it doesn't have to be super user friendly, but of course that would be nice.
Also, what should I do with all of my VBA code? A lot of the logic behind my forms and reports is in VBA(DAO), because it made them much more powerful and WAY faster. I would even say that most of the "links" that currently exist between our data actually happen in that code. Of course, I would love to lose as little of the work I've done as possible, but not at the expense of the database.
Also, for the suggestions you make, can you point me to any good resources (books, web pages) that could help me out along the way?
Thanks a lot for all of your help.
Jenni
-
Hi jenni
If you used SQL and Access Projects you get the best of both worlds. The ease of development using Access and all your vba code can be re-used. Although you will require Access 2000 and above, and any DAO code will work, although I recommend your switch to ADO.
-
Hi Jenni,
Just be careful deploying MSDE as it is not designed to handle heavy loads and has several limitations.
See the specs here: http://www.microsoft.com/sql/msde/productinfo/features.mspx
and note the Performance area - maximum of 5 concurrent workloads before they all get throttled. i.e 5 user connections.
It's OK for a proof-of-concept exercise, but if you're moving to a web-based concept, you might need to go up to SQL Server.
SQL Server also has much better backup and recovery options than Access.
-
Look at SQL 2005 express instead of MSDE - still free, but fewer limitations. Similarly, VS 2005 is pretty good for web front ends or fat client front ends, but you'd be looking at a significant re-write and learning curve moving from Access VBA /DAO to VB.Net / ADO.Net
-
I know I'll probably get hammered for this from this forum, but if you're looking to port it to the web, Macromedia Coldfusion is very powerful and it's very easy to learn. The price tag isn't terribly high either. There will definately need to be almost a complete re-write as far as your code is conccerned, but I find the learning curve is smaller with CF than with vb.net/ado.net.
-
There are actually two different ways you can connect Access to SQL Server. One is through a simple ODBC connection, which simply connects the SQL server tables to your Access database. This gives you access to the tables just as though they were in a separate Access db file. All the queries still execute in Access. The disadvantage to this is that the query processing is all happening on the client computer in Access, so potentially large amounts of data are flowing from the MSDE server to the client computer. The advantage, however, is that your queries that directly access values on Access forms (say, a reporting query that takes a date range) don't need any modification.
The second method is to use an Access "project" (IMHO, this is a terribly confusing name). An Access project cannot have its own queries in the usual sense, though forms and reports still function like normal. Instead of queries, Access projects let you access SQL Server views, stored procedures, and user-defined functions (UDFs). These are *vastly* more powerful and faster than Access queries, but any data you wish to pass into the query from a form must be done as a stored procedure parameter or UDF parameter. Views cannot accept any parameters at all. The integration of these data retrievers to Access forms is more limited and cumbersome to program. If your database winds up being "large" (I'm not sure what threshold that really is), executing stored procedures and UDFs instead of plain-jane Access queries can have a MASSIVE performance improvement.
I have a database project I am doing this same thing with. I opted for the first method because I had some very simple queries in Access I didn't want to rewrite. I also had one very complicated reporting query which I converted to a stored procedure and call from Access. There is somewhat clunky way you can make such calls even when you use an ODBC connection to SQL Server. Converting that complicated query to a stored proc cut the execution time down from several minutes to about 5 seconds. In the end I am pleased with the arrangement. If I was starting from ground zero, I would store ALL the data and queries in SQL Server and use an Access Project because it would run faster.
Hope I didn't make you even more confused.
-
Thank you all for giving me so much helpful information. I have a lot to think about.
One more question...What do you think about using .ASP as the front end? My thought is that it appears to be free, simple, and I could probably still use a decent amount of my vba code/logic. However, I don't really know what I'm talking about. Any thoughts?
Thanks again,
Jenni
-
Oh yeah... and it's web-based.
Jenni
-
Hi Jenni,
You also need to consider the support requirements as you move from Access (single tier) to SQL Server (N-Tier). You will need Servers to host the SQL Server, which is not cheap. You also need to know the cost of the SQL Server License, Window servers. What about the configuration? Just one server or clustered - active/passive. If you are using ASP.Net as a front-end you will need to purchase VS2003 for each developer. What about source control? ASP.Net has many pieces to it and generally need source control, we use Visual Source Safe in our shop. Do you have a SQL Server DBA, it is very important to have an experience SQL Server DBA on an ASP.Net project. On a N-Tier project coding is the least of your worries, it is all the pieces that need to work together. Hope this gives you a few more things to thing about.
Mike from St Paul
-
So, I need VS2003 to use ASP? I talked to Microsoft on the phone yesterday and I thought she said that I could make my own ASP files without VS2003, but maybe we misunderstood each other.
Jenni
-
No you do not need VS2003 to use ASP. It is helpful to have it for developing ASP.NET but it is still not "required".
-
I think my last post got lost......No you do not need VS2003 to build ASP.Net web applications, but in my opinion, it worth the price. If you want something free, check out MATRIX. I've heard good things about it. Again, building an ASP.Net web application is much easier with VS2003.
Mike
-
Keeping the cost down is kind of an important factor in the near future. Do you think it would be a decent compromise to just get Visual Basic .NET for now?
-
If cost is a significant factor, check out the express editions of VB and SQL . . .they're free. OK, limited functionality compared to the full VS 2005 package, but a good way to try 'em out
-
Realizing it would not have as much functionality as the full suite (I can handle that), is there any reason it would be bad to use these express editions?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply