Working with SQL Server (Acc2K)

Topic

New Reply

We are considering moving our Access database to SQL server, mainly for speed and security reasons. I am just starting to research this but wonder how easy it is to modify queries and tables on SQL server? I sure like the convenience of Access for modifications – and I have to to that weekly at least. Will it be as easy, or more of a hassle?

Reply | Quote

Replies

There are a lot of depends
I’m not a SQL Server expert, so take everything I say with a pinch of salt
I’ve found the Enterprise Manager and Query Analyzer pretty similar to what you can do in Access (and more), but they are not the same, so I’m still going through that “Now how do I do …” period

Reply | Quote

SQL Server is a wonderful product, but it is not as friendly as Access in any way. Way more powerful in its operations, but not nearly as friendly. If you are modifying table design on a weekyly basis, there’s something wrong with the design in the first place and it might be better to get it right in Access before you migrate to SQL Server. SQL Server doesn’t have queries, it has Views (which are sort of like simple queries) and Stored Procedures which are more like a cross between a complex query and code. Writing good stored procedures is just as much an art as writing good VBA code, so be prepared for a learning curve of consequence. Furthermore, SQL Server normally runs on a server machine in a networked environment. That means that unless you have admin rights to the server machine itself, you may have restrictions on what you can do in SQL Server.

Reply | Quote

Thanks for the info. I’m not modifiying design of existing tables – everything works fine. Rather I am adding features (new table and forms and reports) and streamlining processes as I learn new ways of doing things in Access. This is a part -time job in addition to my normal duties and we add things as we find we want/need them. For our purposes this is a very nice advantage of using Access – instead of having to bring in a special programmer we can do it all ourselves at a huge time and cost savings. I am just wondering how moving to SQL server will affect that process. Sounds like it may negatively affect the ease of use.

Reply | Quote

Maybe we aren’t fully communicating here. SQL Server would have tables and “queries” (views and stored procedures). Forms and reports, the user interface, would still live in Access. You can, in fact use Access/jet to handle your queries if the result sets aren’t too big, but in so doing you lose a lot of the advantage of SQL Server as a back end. To use it to best advantage, you need to be willing to learn to handle T-SQL and to build stored procedures, triggers and constraints. If you just put your tables in SQL Server and link them to your mdb, you’re not getting your money’s worth from SQL Server.

Reply | Quote

Thanks again. So what you seem to be saying is that all my queries would have to be re-written to take full advantage of SQL Server (sorry, I am just beginning to research this so I have read up on it yet). My result sets aren’t that big (single returns , tens of records, at most hundreds of records) but from the sounds of it I wouldn’t want to go through the trouble without optimizing it for the server software. Our returns aren’t all that slow right now on our present servers, but it could grow since we are getting into a lot of graphics storage (photos).

Reply | Quote

If your result sets are that small but your tables are large, then SQL Server stored procs would benefit you but Jet queries will work. If you only have a few people using the database, SQL Server is probably overkill.

If you’re planning to store photos in your database, I have a word if advice: DON’T. If you search in this forum on photon you’ll find plenty of discussions on why not to do it that way. If you insist on storing images rather than links to them, Access isn’t practical and you’ll *have* to consider SQL Server.

Reply | Quote