August 19, 2009 at 9:13 am
I have an Access database that is several years old and the data in it is in disarray. The orginal designer didn't know much about Normal Forms or the importance of selecting datatypes or even of primary keys. The resulting application is a tedious thing that requires several bizarre steps in order to create a usable report. I'm going to be moving the data to a sql server 2005 database in a shared hosting environment.
The data in the old one will need to be reimported into this new one. It has to be saved. Is there a general guide as to how I should go about importing the new data? There are some relationships, such as Supplier and Inventory counts which are linked via a key. The key is something I will be changing. ..changing it from an unsigned integer to a signed integer and having it auto-increment.I've never had to import data from one system into another and I am anticipating it to be somewhat difficult..or at least requiring some forethought (lol).
I am asking for other people's experiences doing this. What worked for you in the planning stage and execution. Is there one best way to do this? Is this something that requires a stored procedure to process a single row at a time or can it be done in bulk? Is there anything you learned about doing that I absolutely have to know before hand 🙂 Anything I can do to make the job alot easier? Any free tools that do this sort of thing all the time 🙂
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
August 19, 2009 at 9:16 am
The Access upsizing wizard should move the data. That's what I'd do, that way things will continue to work as they are. I'd use this as a backup, and then start to make changes to the data in SQL and move to new tables.
There's no way to easily do this because it's hard to know how you have tables linked, and if that's declared. If it's not declared, you'll have to manually script the changes. However once the data is in SQL Server, it's easy to manipulate, IMHO.
August 19, 2009 at 9:37 am
The database I was sent is from Access 2000. I have Access 2007 on my machine. i guess there are alot of input forms that were apart of it (i have never worked with Access so I'm not too knowledgeable about it) because I get errors when I open the database. Lots of broken references like 4 or 5 msdatagrd.ocx version 1.0. Then I get an Action Failed message for the Autoexec macro. I also get an error about the replica being out of date. I'm wondering if I will be able to run any kind of tool on it? I'd like to avoid having to fix these errors simply because its source is older and it was running on an earlier version of windows.
She sent me a 'replica'. I tried to trun the splitter on it but can't do that on a replica. Am I going to need the actual database? I'm not sure how to instruct her on how to send this to me if I do need it.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
August 19, 2009 at 9:58 am
I'm not a big Access guy, so I'm not sure what you do there. You need to be able to open it. I might open Access and then see if you can open her mdb.
Worst case is I'd have her export each table to an XLS and pull those into SQL.
You can also try the import wizard in SQL Server and point it at the mdb. That might be the best solution.
August 19, 2009 at 10:22 am
The dataimport worked fine. At least it got it into sql server for me to work on it. Only one table failed to import but I can work around that. I'm so relieved.
I still have the task of maintaning the relationship between the data items (master-detail for instance) but I think that can be done with an update script.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
August 19, 2009 at 11:36 am
MothInTheMachine
Just in case you run into some unexpected glitches these links might assist you in over coming those glitches:
http://technet.microsoft.com/en-us/library/cc178973.aspx
From above:
With Microsoft Office Access 2007, you can open and use databases
created in Microsoft Access 2000, Access 2002, and Access 2003.
By using the changes and improvements in Office Access 2007,
you can also convert databases created with previous versions of
Access into the new Office Access 2007 file format
This also might be of assistance.
August 19, 2009 at 12:01 pm
ha ha ha..Thanks Ten Centuries! I appreciate the links and the humor. It helps keep this in perspective 🙂
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
August 29, 2009 at 4:15 pm
MothInTheMachine (8/19/2009)
I still have the task of maintaning the relationship between the data items (master-detail for instance) but I think that can be done with an update script.
:ermm: please... do elaborate.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply