Splitting Database

Posted on 2011-05-02
Last Modified: 2012-05-11
After using the 2010 database splitter tool to create a back-end in SQL, there are duplicates of all the tables - all prior tables have "_local" added to the names... why does it do this instead of just creating the new database, copying tables and data and linking to the SQL and removing the local tables?
Question by:gcgcit
    LVL 12

    Expert Comment

    Did you already split the Access Database and then Upsize your database to the SQL Server?

    The tables with _local are tables you already have upsized to your SQL Server, see below explanation;

    The Available Tables list box includes all linked tables except for SQL Server tables already in an SQL Server database. Linked tables that point to an SQL Server database that has been selected for upsizing automatically appear in the Export to SQL Server list box and can't be removed. Any table that has a name ending in "_local" is excluded from the list of available tables to prevent upsizing tables that have already been upsized. If you do want to upsize these tables again, rename them before you run the Upsizing Wizard by removing the suffix "_local". Tables that are not currently visible in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) are also excluded, including hidden tables and system tables.

    See for more info below article;

    LVL 84
    In addition to what Daniel writes, you can also just Delete the links to the SQL database. This does NOT delete the tables - it simply removes the links created in Access. You can then recreate those links. Note too that Access will often add "dbo_" to the front of your object names - you'll usually need to rename your tables to work with existing objects.

    Author Comment

    Can I delete the tables that say _local and just leave the tables that are linked to SQL?
    LVL 12

    Accepted Solution

    Did you select the option Link SQL Server tables to existing application?

    If you select this option, the Upsizing Wizard modifies your Access database so that your queries, forms, reports, and data access pages use the data in the new SQL Server database rather than the data in your Access database. The Upsizing Wizard renames the Access tables you upsize with the suffix "_local." For example, if you upsize a table named Employees, the table is renamed Employees_local in your Access database. Then, the Upsizing wizard creates a linked SQL Server table named Employees.

    Note: After the upsizing operation is complete, the tables that were renamed with the "_local" suffix will no longer be used. However, it is a good idea to retain the local tables until you verify that the upsizing was successful. At a later date, you can delete the local tables to reduce the size of your Access database. Be sure to back up your database prior to deleting any tables.

    Queries, forms, reports, and data access pages based on the original Employees tables will now use the linked SQL Server Employees table. Many of the properties of the fields in the original local table are inherited by the new local table including, Description, Caption, Format, InputMask, and DecimalPlaces.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now