Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL upgrade (Access 97 & 2000)

    Can anyone help me find a white paper or something to help me justify upgrading my Access 97 & 2K database's to SQL. I have been told I WILL upgrade but that I need to submitt in paper a reason on how to do it and why I need to do it.

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

    Re: SQL upgrade (Access 97 & 2000)

    Someone is telling you that *you* have to justify what they're requiring? That sounds as reasonable as most of my clients' requests. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    The main reasons I can think of for upsizing to SQL Server (or any database server, for that matter) are number of simultaneous users, overall speed of response, allowable size of the database, reduction of network traffic, and complex operations that simply won't work (or take hours) in Access but run immediately in SQL Server. You can do stuff with stored procedures that is impossible (or at least impractical) using queries.

    As to how, you can use the upsizing wizards to upsize the tables. It's even possible (assuming it works, that is) to upsize most of your queries. If you include forms in the upsizing and create a 2000 ADP in the process, things like select statements used as recordsources and rowsources *may* get upsized, although you'll have to wade through a bunch of ambiguously named views and stored procedures to figure out which ones are which.

    You'll run into immediate problems with any queries that use functions, because SQL Server 7 doesn't support functions at all. SQL 2000 is supposed to, but I haven't worked with it enough to be familiar with exactly how it does that. I don't believe it will support any of your Access functions, though, even in SQL 2000.

    I hope you either have a SQL Server expert on tap or are being allowed adequate time and training to come up to speed yourself, because this is not just a convert and run kind of an operation. SQL Server is a whole 'nother animal from Access/Jet, with its own crotchets and peculiarities; and writing stored procedures is *not* like creating queries.

    A book that might be of use to you in fulfilling their justification requirement and also in learning how to cope is Russell Sinclair's "From Access to Sql Server", from Apress (ISBN: 1-893115-24-0). It covers a lot of the problems of upsizing and migrating and will provide much of the background you'll need to write your justification. Both SQL Server 7.0 and 2000 require you to learn Transact-SQL for writing stored procedures, so you might also be interested in the O'Reilly book, "Transact-SQL Programming" by Kline, Gould & Zanevsky (ISBN: 1-56592-401-0).
    Charlotte

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

    Re: SQL upgrade (Access 97 & 2000)

    Oh, by the way, here's the <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q241/7/43.ASP>link</A> for the knowledgebase article that will let you download the Microsoft whitepaper on upsizing Access 2000 to SQL Server 7.0.
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL upgrade (Access 97 & 2000)

    Thank you on your information on SQL. I had to drop that project to work on something else. Now I am back on this. Right now I have to compare SQL/VB with Coldfussion. Anybody have any idea's or point me in a good direction.

Posting Permissions

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