Results 1 to 2 of 2
2005-08-12, 13:21 #1
- Join Date
- Aug 2005
- Thanked 0 Times in 0 Posts
SQL Server (Access 2002 To SQL Server)
Hi guys, gals and all you genious programmers out there...I call on you for some more of your knowledge. I am in deep thought about conerting my system to an access front end and a sql back end. We have a huge dataset as our back end and quite a big front end containing all the interfaces. There is lots of very complicated code. I know that in order to do this effectively we will need to use ADO. im looking really to find out the best way to go about making this change and if there is any books that anyone can tell me about where i can start to learn SQL stored proceedures and SQL Server in general. I would appreciate it if it would be one in laymens terms as im not extremely experienced in SQL. Any other useful hints or places to visit would be great....Im going to be using an ADP if that helps.
thanks very much
2005-08-12, 22:54 #2
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 65 Times in 64 Posts
Re: SQL Server (Access 2002 To SQL Server)
I think you are headed in the right direction, but are likely to end up using the wrong, or at least expensive, methodology. There is no question that SQL Server has many advantages as a database engine - performance, stability and reliability to name several. However the quickest path to a SQL Server with an Access front-end is to simply move your tables to SQL Service using one of several tools and then use the SQL Server ODBC driver to connect to the tables from Access. We use this approach for about 95% of what we do, and get excellent performance with large recordsets and complex interfaces. The general concensus is that developing an Access database using an ADP, or rebuilding an existing one and convereting to an ADP is much more time consuming than using ODBC. In general, ODBC connections work just like a jet connected table. There are subtle differences, such as Autonumbers being available only after a record is added, but in general, they work just fine. The question of whether to use ADO or DAO is a closer call - but if you have DAO already, it will work most of the time. Occasionally you can get a performance boost with ADP, but there also cases where DAO is faster.
Our bottom line is to do the quick and usually painless conversion to ODBC SQL Server tables, then look for performance or functional issues and fix them using appropriate technology. I should also note that the Microsoft party line is that there will not be any major functional improvements to ADPs, and the the MDB format will be where new enhancements and capabilities will show up. Let's see what others have to say.
As an afterthought, there are a couple of good books you might want to look at. One is the Access 2002 version of Alison Balter's Enterprise book - it contains a very pragmatic approach to making the choices you are faced with. The other is the Access to SQL Server book by Chipman and Baron - the two complement each other very nicely.Wendell