Splitting Database

Posted on 2011-05-02
Medium Priority
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
  • 2
LVL 12

Expert Comment

ID: 35507913
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 85
ID: 35508258
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

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

Accepted Solution

danishani earned 2000 total points
ID: 35514594
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.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 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