Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: MS Access to SQL Server

    How would I go about migrating an access database into SQL. I know I can use DTS for the tables. Is there another way to do this?

    For this database the frontend will be access and the backend will be SQL Server. Any help would be appreciated.

    Thanks

    Lystra

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you're only use to Access, it will require a development style shift...

    I don't think I'ver seen a straight conversion work.....

    And it's all because the way Access treats data....

    pay close attention to the comments in Pat's link about saving the data to a "local" access table....then it get tricky from there....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2004
    Posts
    191
    Thanks all.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Watch out for dates...SQL upsizes Access date/time to smalldatetime. That's fine if you don't care about seconds (or dates that are way outside normal ranges), but if seconds count, then you'll want to be sure that the Access date/time fields get moved to SQL datetime.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    More to my point.....

    You might want to consider a re-write....

    What about all of thos Access nested queries?

    Got any of those?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you are going to just "upsize" the tables and leave the rest in Access, be aware that you need to apply primary keys to all the SQL tables. Otherwise, you won't be able to update them. This is always fun to figure out.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, you can update a table which has no primary key

    but yes, you will have fun trying to pin down which row you want to update

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another word of caution: if you leave the frontend in access, but move the tables to sql server, then you simply must change all your queries to pass-through queries, so that they can be executed by sql server

    this will mean syntax changes to the queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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