none
Importing Access Data RRS feed

  • Question

  • Hi. I'm considering giving LightSwitch a try. I'm currently using Access 2010 with my data in a back-end database. How difficult is it to import the data from my access database?

    James

    Friday, November 18, 2011 12:06 AM

Answers

All replies

  • Not dificault at all, I would have a look at the acces data migration tools first. I would suggest at looking at migrating the access backend data into a small SQL Server Express or Full SKU SQL Database.
    http://www.virtualrealm.com.au - SQL Server DBA, MVP and Lightswitch Enthusiast.
    Friday, November 18, 2011 3:53 AM
  • Can I do this directly from within LightSwitch?
    I'm unfamiliar with SQL migration.

    James

    Friday, November 18, 2011 11:32 AM
  • No, you need to use acces , sqlserver or external tool
    Friday, November 18, 2011 12:31 PM
  • So i need to export the database within access? I see no export selection for sql.
    Where do i get these tools from and how do i know which is best for me?

    Sorry about all the questions I'm just so used to access I've never worked with this before.

    James

    Friday, November 18, 2011 12:43 PM
  • James,

    Access includes an Upsizing wizard that allows you to convert to SQL Server.

    http://support.microsoft.com/kb/237980

    In Access 2010, you'll find this option under the Database Tools section of the Ribbon Bar. There's a 'Move Data' section that includes SQL Server button.

    This option changes the data source of your Access application to point to SQL Server so I'd suggest doing this on a copy of you database.

    Tim

     

    Friday, November 18, 2011 1:00 PM
  • I have access 2010

    The connection fails. I see no selection under the " What SQL Server would you like to use for this database."  SQL Server error: 2 and SQL Server error 17. If you need the entire error let me know.
    I do have Windows 7 services tweaked. Maybe I have something turned off that shouldn't be???

    James

    Friday, November 18, 2011 2:44 PM
  • I'm not sure if Access 2010 is any better but pulling the data from Access to SQL Server, using SQL Server Integration Services (SSIS), used to give you a much better result than pushing data from Access to SQL Server using the Access Upsizing Wizard.

    The Access Upsizing Wizard used to do strange things such as push Currency columns through to SQL Server as FLOAT rather than MONEY. (FLOAT aka REAL or Double columns are not accurate enough for monetary values.)

    Using SSIS to pull data from Access is easy. Start SQL Server Management Studio, create a new database with the right name on the server, right-click the database name and choose Tasks | Import Data. Point the Import Wizard at the Access database and tell it which tables to import. There is extensive help available in SQL Server Books Online.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.
    Friday, November 18, 2011 3:00 PM
  • Can't seem to find SQL Server Management Studio in the downloads and I'm sure it isn't already on my system.
    Friday, November 18, 2011 4:02 PM
  • You can download a copy of SQL Server Express that includes Management Studio.

    http://www.microsoft.com/download/en/details.aspx?id=22973

    When you run installer, only select the option to install client tools. Untick the option to install another SQL Express instance.

    Tim

    Friday, November 18, 2011 4:23 PM
  • Thanks. I saw SQL Server Express there but wasn't sure.

    Thanks again,

    James

    Friday, November 18, 2011 4:44 PM
  • I installed Microsoft SQL Server 2008 but I can't find SQL Server Management Studio anywhere on my system.
    Friday, November 18, 2011 7:15 PM
  • Well, if you can't find SQL Server Management Studio which is part of SQL Server Express with Tools, perhaps you can use SQL Server Migration Assistant for Access.
    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.
    Friday, November 18, 2011 11:59 PM