SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Converting MSSQL to Access and vice versa

    My office is in the (slow) process of moving from an MS Access database solution to MSSQL Server. Currently they're still ironing out some details on how the setup is going to be, so for now I'm still stuck using Access.

    Recently we got a database sent to us that I have to build a web tool around. Since I thought we'd be using MSSQL by now, I asked for it in that format, so they sent me a backup (.bak) file of the database. I asked our server guys to convert it to Access, and when they did, the resulting database was missing the PK and auto-number attributes for the ID columns in each table, the latter of which is not all that easy to add afterwords.

    Since this is something I've seen before (we had the exact same problem when converting one of our Access dbs to MSSQL), and will likely see again, I'd like to do something to prevent it.

    Does anyone have a link to any sort of step-by-step process or tutorial on how to do these conversions the right way? I'd like to get a database I can use without running a bunch of scripts across it.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    google for conversion tools, there are tons of them
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    google for conversion tools, there are tons of them
    So there's NO way to do this from within MSSQL? A conversion tool would have to get reviewed and approved (this is government, after all). The last piece of software took 6 months to finally NOT be approved because the approval people thought we already had someething that performed the same function (we didn't).
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I just realized that I can try all the tools I want at home on my personal machine, then bring the new db in on a thumbdrive. Doh!
    <cfset myblog = "http://cydewaze.org/">

  5. #5
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    911
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Why not test the conversion tool first by yourself.
    And if it has no flaws and work perfectly then do it yourself convert them.
    You don't need to tell anyone since you know it will take 6 months? lol

    But if the implication is if they will caught you doing it and there is the possibility you will be remove in service, then don't do it.

    Just IMHO.
    I Love coding...
    Don't click this link.

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    In the end I just created new autonumber columns for each table, then wrote a ColdFusion script to associate the new ID numbers. It went easier than I thought.

    Besides, all the conversion tools ended up costing money, and I think it would have been 2050 before that purchase got approved.
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Microsoft has, or used to have, a free download upsizing wizard to convert from Access to MSSQL Server.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Doug G View Post
    Microsoft has, or used to have, a free download upsizing wizard to convert from Access to MSSQL Server.
    Indeed, but this time I had to go in the other direction.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    356
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Indeed, but this time I had to go in the other direction.
    Oh, sorry. I misread your original post.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon


Bookmarks

Posting Permissions

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