locked
Export Database Sql Server 2008 to Access RRS feed

  • Question

  • Hi

    I need to export a database Sql Server 2008 (tables, views and UDFs) to Access Database.

    I used the procedure explained on page:

    http://www.codeproject.com/Questions/483713/HowplustoplusExportplusSQLplusTablesplusToplusAcce

    Specifically the solution 1.

    This procedure exported tables and views satisfactorily but not the UDFs.

    However, a problem occurs with the bit fields of the tables. If the value of this field is true, it transforms -1 in access.

    Also, at the end of the export, the following type out messages in a final report:

        The execution was successful
           -Initializing Data Flow Task (Success)
           -Initializing Connections (Success)
           -Setting SQL Command (Success)
           -Configuring the Source Connection (Success)
           -Configuring destination connection (Success)
           -Validating (Success)
           -Prepare for implementation (Success)
           -Run previously (Success)
           -Copying rows (Warning):
               Messages:
                  *Warning: Preparation SQL Task 1: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)
                  *Warning: Preparation SQL Task 1: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)
                  .....
                  *Warning: Preparation SQL Task 2: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)


    The lasts warnings involve some type of error in the copying of data?

    Anyone can tell me correct procedure for the export? (including UDFs)

    Thanks

                  
    Thursday, October 3, 2013 3:55 PM

Answers

All replies

  • Hello,

    The SQL Server Import and Export Wizard can only copy data form the existing tables or views in the SQL Server database to Access database.

    For the bit field: in Access, Yes/No (Boolean) values are stored using the same values for Boolean variables in VBA, 0 and -1. The bit data type in SQL Server uses 0 and 1.You can refer to the sample code and workarounds in the following article for data conversion:
    http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

    In order to work with SQL Server data ( tables, views, stored procedures, user-defined functions, and database diagrams) in Access, you can create a Access project and connect to SQL Server database.
    For more details,please see:http://office.microsoft.com/en-in/access-help/ways-to-work-with-sql-server-data-in-access-HA001034599.aspx

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Marked as answer by Fanny Liu Monday, October 14, 2013 11:01 AM
    Tuesday, October 8, 2013 8:18 AM
  • When a field is the primary key of a table, is not exported with this attribute.

    Neither the relationships between tables, foreign key, ...

    How can export this?


    Tuesday, October 8, 2013 2:12 PM
  • Another problem:

    The views are exported as tables.

    What is the equivalent of the views in access?
    Thursday, October 10, 2013 6:23 AM
  • * Views equivalent in MS-Access is Queries.

    * It's much better to use Linked Tables instead of convert the whole database. Read: Link tables in an Access project by using the Link Table Wizard (ADP)


    Please remember to 'Mark as Answer' the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Fanny Liu Monday, October 14, 2013 11:01 AM
    Thursday, October 10, 2013 6:44 AM