Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with SQL Server (Acc2K)

    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?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with SQL Server (Acc2K)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with SQL Server (Acc2K)

    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.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with SQL Server (Acc2K)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with SQL Server (Acc2K)

    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.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with SQL Server (Acc2K)

    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).

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working with SQL Server (Acc2K)

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •