Editing an Existing Access Database - Operation not supported

[yrstruly]

Editing an Access Database:

• I want to create new tables and update existing tables, queries, and other objects within my database.

Copying the Database:

• I have created a copy of the database using the Copy function.

Best Practice for Editing an Access DB with Forms:

• Which method is better for editing an existing Access database that includes Forms:
  a) Using the Copy function
  b) Using Save As

Error When Editing Linked Tables:

• I am receiving the error: "Operation not supported" when attempting to modify linked tables.
• How can I access and edit the backend database instead of the frontend when making changes to tables?

Database Splitting:

• When should I split the database into frontend and backend?
• What impact does splitting have on the database’s functionality?

Working on a Copy and Connecting to the Original Source:

• My goal is to work on a copy of the database, connect it easily to its original data source, and replace the original with the modified version once my work is completed.
• For example, I want to work on ABC DB (Copy) and later switch it to be the production database without disrupting operations.

[June7]

Not sure any benefit to Save As over Copy. Either can save a duplicate with different name as 'backup'.

Either manually open backend and do edits or VBA has to create and set a database object variable and manipulate that.

Should probably split db immediately.

[Micron]

I would split right at the start also. I cannot think of a reason to keep tables with forms/reports/code in the beginning. I can think of a reason to not do so, and that is, corrupting something and losing everything up to that point.

I don't think there is enough info to answer why you get that error. Do you know what the operation is (append/delete/update something else)? Are they Access tables? Sql Server? Something else?

[orange]

I agree with the other responses --- split now/early.
We need more info re your current set up and status to offer more focused advice.

[yrstruly]

I think I encountered an error because I used a reserved keyword in Access DB.
Scenario:

• Multiple users are accessing the database and forms.
• Data is manually entered via Forms and Datasheet View.

Question:

If I split the database, will users still be able to enter data using only the front-end interface, or will that be restricted?
Additional Context:

• I made a quick copy of the database.
• My changes will only involve adding tables and relationships, so the integrity of the existing data should not be affected.

What is the best approach to achieve this?
I only want to relink the original data source tables if necessary once development is complete. Is it required to unlink the tables in the development copy while making changes, or can I keep them linked throughout the development process?

[Micron]

I think something might be missing from your info. I think you are thinking that you split, make one front end (fe), and everyone shares it on a network. Possible, and it will operate normally with concurrent users, but the widely held view is that eventually you will corrupt the fe. The proper way is that each user gets their own fe copy and does not share.

[Welshgasman]

I think I encountered an error because I used a reserved keyword in Access DB.
Scenario:

• Multiple users are accessing the database and forms.
• Data is manually entered via Forms and Datasheet View.

Question:

If I split the database, will users still be able to enter data using only the front-end interface, or will that be restricted?
Additional Context:

• I made a quick copy of the database.
• My changes will only involve adding tables and relationships, so the integrity of the existing data should not be affected.

What is the best approach to achieve this?
I only want to relink the original data source tables if necessary once development is complete. Is it required to unlink the tables in the development copy while making changes, or can I keep them linked throughout the development process?

Not showing at https://learn.microsoft.com/en-us/of...reserved-words

[orange]

Note: Every user gets their own copy of the FrontEnd as Micron stated.

[yrstruly]

We work on a remote server. So please advice, how do i disribute each user their own front end copy?

[Micron]

That is another issue, and maybe not related to your original post. Even if you split the error may remain. There are thousands if not millions of web pages on how to distribute an Access database and they may not all be suitable to your situation. So the best thing for that would be for you to research it and find a method that you like. Some simply enforce a download of a new fe every time the user tries to open it via a shortcut. Some keep version specific info in a table that resides in the fe and compares it to an updated back end (be) table with version info. And so on...

[Welshgasman]

I chose the latter method, as copying a new file everyday just seemed wrong to me.
Only get a new version when needed. That was my view.
I used code by Bob Larsen, so search on his name and extra words.

Could even search here?
https://www.accessforums.net/showthread.php?t=86007

[yrstruly]

It appears the previous developer already split the db, which is good. Now its making the correct copy format, alter tables and relationships and use this new developed db as the primary db.

[jojowhite]

to see the path what and where is the backend database.
open MsysObjects table.
look for any linked table and see the Connection string.
after you successfully located the BE, you can open the BE
and modify the table as you wish.

[Gicu]

Just a small correction to the post above, the Connect field of the MsysObjects table will hold the connection string for ODBC linked tables, if your back-end is an Access database then the full name will be in the Database field of the same table.
Here is an example of a launcher that can be used on a remote server, simply add it to each user's special folder (Documents, Desktop, etc.).
https://forestbyte.com/ms-access-uti...a-db-launcher/
Cheers,

[yrstruly]

Development will take place in both Fe and Be environments. I plan to create copies of both and conduct separate development in each. Additionally, I need a VB script to update the development database with the latest data from the current production database. Do you have any suggestions?