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