locked
What is the best route for converting an Access 03 db to SQL 08 R2 RRS feed

  • Question

  • Hi, I have an Access 2003 db which is split with the tables in one and the front end (with linked tables) in the other.

    What is the best route to get a fully functioning SQL Server 08 R2 db out of it?  I want the front end to be web based - not Access based but do not mind having Access as a starting point if necessary.  I have just installed the evaluation x64 model on windows 7 home premium and need this up and running pdq.

    The front end has quite allot going on with queries and reports etc.  Can I just convert the backend db to SQL and then relink all the tables etc on the front end db?

    I used to use SQL server 7 and 2K and there were wizards for converting access dbs.  Have these gone or have they been improved?  I would be grateful if anyone can tell me the best procedure. 

    Any thoughts or suggestions are appreciated

    Thanks in advance.

     

    Thursday, November 25, 2010 11:24 AM

Answers

All replies

    • Marked as answer by Tom Li - MSFT Sunday, December 5, 2010 12:46 PM
    Thursday, November 25, 2010 12:18 PM
  • Thank you Alberto,

    Sorry for the delay in getting back.  I have been (with great frustration ) trying your suggestion(s)

    Sumary of problems:

    1) The DAO object is difficult to find but I assume installing the 'Access 2010 Runtime' file has the desired effect.

    2) Once these items are installed the Access db does not work and produces several errors when certain buttons are pressed saying this 'Object or Class does not support the set of events'.  To solve this, I tried going into the references and deselecting 'Microsoft 14 Object Library' but get the message 'Cant remove the control or reference; in use'.  The only way I could get the db/app working again was to uninstall the migration assistant and the Access 10 Runtime file.  Then it went back to working fine.

    3) Ignoring the above problem, I ran the migration program while everything was still installed.  The first error comes in the form of a 'Conversion Error' - "Nothing to Process"  This seems to be because when I selected the access db to convert, it was listed OK but when selecting tables, none appeared.  I tried to proceed with just the 'tables' box selected and got teh error.

    4)as the Convert process failed, obviously the following tasks (load converted objects into converted db; load data) also failed.

    5) After closing the wizard, the two databases (including the new sql db) appeared in the relative panes. but only the schema options were visible (no tables)

    6) The message in the 'Output' pane at the bottom of the interface has the following text:

    Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154. This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. Please run SSMA in 32-bit (or 64-bit) mode or consider updating your connectivity components from http://go.microsoft.com/fwlink/?LinkId=197502.
         An error occurred while loading database information.
    Conversion finished with 0 errors, 0 warnings, and 0 informational messages.
    There is nothing to process.

    Conclusion

    Not giving up with this!  It looks like a great tool which I will use many times if I get it working. so:

    1) how do I know if I am running this as a 32 bit or 64 bit app? And how do I do the opposite?

    2) Once I install the Access 10 Runtime necessary to install the Migration tool, how do I get the Access 03 db to work again?

    Many thanks for answers to any of this.

     

    Saturday, November 27, 2010 4:59 PM
  • Hello,

    You can install DAO from Microsoft Office.  

    Hope this helps.   
     
    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, November 29, 2010 11:50 AM
  • Thanks Alberto but that is exactly the problem.

    The only office on the machine (and there is no real reason to splash out on office 2010) os 03. The link on the SSMA takes you to the download section of Office where the only option is to download 2010 Runtime.  Once I do this, I cannot get the db to work again.  All the buttons stop with the error stated above.  In the references section you can see that it is the version 14 that has been automatically added in during the install.  That seems to be what is causing the problem.  You cannot deselect it (as I have indicated) so the only way seems to be to uninstall the Office Runtime 2010 and that cures it because the db then works perfectly ...... but SSMA stops working!

    Monday, November 29, 2010 1:15 PM
  • Monday, November 29, 2010 3:04 PM