Splitting Database

[MTSPEER]

Hello,

I understand how to split the database and create the back end table, but I don't want all the tables to be linked. Is there any way you can picka nd choose which ones you want to be linked to the backend table? Or is there a way to unlink certain tables to the backend database?



Thank you

[orange]

Why would some tables in the back end not be linked to the front end? Just curious.

[nick404]

Are you wanting to link some tables to a different back end perhaps? Otherwise there is no harm in just linking all of them.

[MTSPEER]

I can't have all of them linked because the users import specific files for themselves and they will use a couple of the tables to validate information. So I need to unlink the tables where users import their personal files to validate

[nick404]

Validate how? By viewing the tables? With linked tables you can still open the table and view most recent data.

User is importing personal files into one of these tables as well?

[CJ_London]

Is there any way you can picka nd choose which ones you want to be linked to the backend table?

for those tables you don't want to link, just delete the linked tables in the front end or don't link them in the firstplace. However sounds like you data isn't normalised if users have 'personal tables'

[nick404]

If you are using the database splitter wizard you cannot select which ones to link. It simply links all of them since it is the wizard and strives to make it easy. As Ajax said you can just delete the linked tables and then re-create new ones in the front end.

Otherwise create a another database, import the tables you want from the soon-to-be 'front end' as linked tables, and then that will be your backend.

Check out this link on doing it manually as I described above. https://support.office.com/en-us/art...036e45fcf6#bm3
make sure to select "import as linked tables"

[MTSPEER]

I didn't know you could link manually. THanks for the link. But the user validates the information from the personal table with queries from the linked tables. And I just want to unlink the personal table everyone uses to import different data. I used the Splitting wizard so I couldn't pick which ones I wanted to link

[nick404]

Unfortunately you can't 'un-link' a table in the sense of telling access hey, un-link this table from this one, but, what you can do is delete the tables from the front end and back end that you don't want linked, Then
Recreate the table in your front end- it won't be linked.

[MTSPEER]

okay, but won't the queries be ruined that had the table that you deleted?

[nick404]

If you rename everything exactly the same they should be fine. If your queries aren't too long, go into SQL view and edit all the names of tables and fields to what you have.

I would do this:
1. Make your new tables first, before you delete anything.
2. Literally highlight the fields from the linked tables in design view and paste them into the new table's design view.
3. Delete the linked one(s) and name the new one(s) with the same name. Should be fine.
However, it is best to make a copy of the db before you do this in case you mess up you have something to fall back on.

[MTSPEER]

Sounds like a good idea. I will try this out!

[nick404]

Let us know if it works/not works!

[kagoodwin13]

Not sure if it was said, but maybe in different words, but yes it is possible to manually split and link individual tables.

1. Make a copy of your current Access database (serving as both front end and back end)
2. Delete all objects except for tables (forms, queries, reports, etc. - delete those)
3. Delete the tables you want as "local" in the copied database (don't also delete them from the current version!)
4. Copy the version with just tables to a network shared drive
5. Open the original Access database
6. Click the External Data tab in the ribbon
7. In the "Import & Link" section, click Access
8. Browse to your back end (database copy with tables only)
9. Choose "Link to the data source by creating a linked table."
10. Click OK
11. Use the original database as the front end, with linked tables to the back end, and local tables still on the front end

I think you should think about why you want local tables and back end tables though. Likely you have a data process gap that needs improvement. Databases run best when there is zero chance for error. If you have a chance for error, your database will break. Can you tell us more about why your users need to "validate" data?

[June7]

It is possible to programmatically delete and set table links.

However, if users are importing their own individual data into frontend, each time you distribute a revision to the frontend, their imports will be lost.