Results 1 to 15 of 15
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Access FE SQL BE (Access XP)

    I would like to know what is involved in moving an existing Access database to SQL with a Access FE.
    Can you use DAO with SQL tables etc.
    Any links would be most helpful unless this is a straight forward procedure.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    Thanks Hans
    FYI however the first of your 2 links is not available.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access FE SQL BE (Access XP)

    Link corrected by HansV

    You may find ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download Center useful.

    And a related MSKB article with some links: HOW TO: Use the Microsoft Access 2002 Upsizing Wizard.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access FE SQL BE (Access XP)

    Sorry about that; I have corrected the typo.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    Thanks Hans

    That's better

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

    Re: Access FE SQL BE (Access XP)

    You can use DAO with any kind of linked or local tables, but Jet is involved, which makes the process slower than using ADO to call server-side stored procedures.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    So I can get a SQL Server database defined as a starter, then I can use a Access FE to run forms, queries, reports using linked tables to SQL Server. Am I correct?

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

    Re: Access FE SQL BE (Access XP)

    Right. However, if you use the upsizing wizard, only allow it to upsize your tables, not queries or anything else.
    Charlotte

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    Thanks Charlotte.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access FE SQL BE (Access XP)

    Adding to what others have said, you should be aware that there are some issues with the upsizing wizard. For one, it makes all text fields nvarchar rather than varchar which causes substantial slowdowns in the ODBC driver - not sure why - so you usually have to change them (unless you really need unicode capability). It also will on occasion not upload indexes and DRI constraints, and for some unknown reason will sometimes refuse to upsize a table. Those issues aside, it is a useful tool for quickly upsizing to a SQL database.
    Wendell

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    Thanks Wendell

    It's never straight forward, is it.

    BTW what does the DRI stand for in DRI constraint?

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access FE SQL BE (Access XP)

    Sorry about the lingo - it stands for Declarative Referential Integrity, or basically the same thing you have with Access in the Relationships window. According to the party line, if you have it established in Access, and you upsize all the affected tables at the same time, it should also set up the SQL Server the same way. In actual practice, it seems that sometimes it gets it right, but in any but the trivial cases you have to check it carefully to make sure all the rules got transferred. So we usually set it up ourselves so we know exactly what has been done. Often in testing or in building data for a conversion, you need to do things that violate the integrity anyhow, so that's another reason for setting it up manually. Hope that clarifies things a bit.
    Wendell

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    It certainly does clear things up.

    BTW have you any good reference material on SQL Server.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access FE SQL BE (Access XP)

    If you mean books and the like, the SQL Server Books OnLine is where I usually go, but there is a good book devoted entirely to the subject of using SQL Server with Access as the front-end. You can find Microsoft Access Developer's Guide to SQL Server by Baron and Chipman on Amazon. Rick Dobson also has a good if slightly dated book at a more technical level, while Alison Balter's Enterprise book looks at it from a more pragmatic perspective. If you want to answer a few questions and find some other resources, check out the SQL Server web resources toward the bottom of this web page of ours.
    Wendell

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access FE SQL BE (Access XP)

    Thanks Wendell, I'll check those out.

Posting Permissions

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