0

I have replicated .mdb database on network share with only data (code, forms, etc. are on another mdb). Is was created while ago and there was no problems with it on previous Access versions, including 2007.

Problem: When I open table in design view and change text field size eg. from 10 to 12 and try to save table, I have 3251 error "operation is not supported for this type of object".

Are there any specific Access 2010 limitation? How to solve?

I still can change field size from another computer with access 2007 (must recover design master of database before).

I can change field size by code/sql:

Dim sql

sql = "ALTER TABLE tblSample ALTER COLUMN samplefield TEXT(12);"

DBEngine(0)(0).Execute sql

I can add field in table design or datasheet view but problem remain when I try to edit or delete later.

What else I did without success: copy database and open from local location; added network location to trusted locations; open database exclusively by adding command key /excl; compact and repair

--- added

I did simple test. Created new table with 2 fields: ID (autonumber) and text on my database. Local only table. Filled 3 rows data of random text. Can change text field size on table design view. Then make table replicated. Open on design view, change text field size and can't save anymore, same 3251 error. When uncheck replicated on table properties, can edit again :) This is access bug

SOLUTION

Not real solution, but some ways to overcome:

  1. Really replication do not use anymore, database can be unreplicated. For database mobility there are possibilities to make custom import-export new data by hand and one way update some tables (clients, products lists). Normally snapshot of data is enough for me to have on hand.
  2. Stay as is. Access 2010 is good for client db design (forms, reports, etc.). For rarely table design changes use sql/code or access 2007.
amber
  • 11
  • 2
  • 1
    Similar question: http://stackoverflow.com/questions/19336631/modifying-replicated-database-using-access-2010 -- there was no final verdict, but it seems very much to be an Access 2010 issue (see the comment by Gord). – Andre Aug 17 '16 at 09:25
  • Try to copy table to another one using make table query – Sergey S. Aug 17 '16 at 09:26
  • @SergeyS. I have tested, to create table by query or by table design, is same result - if table is replicable, Access design do not allow to save changes. More explanation added above. Thank You and Andre – amber Aug 17 '16 at 14:27

1 Answers1

0

Old thread but I have also run into the same problems. I found that using Access DDL (e.g. ALTER TABLE) in the SQL window works to modify table design in a replicated database in Access 2010. It won't allow you to modify an existing field/column but you can at least add or drop fields from existing tables. You can use DDL to add a new temporary field to your table the way you want it, copy the data from the existing field to your temporary field, then drop the existing field. In your case you would then need to add a second field named the same as the original field you deleted, copy the data from your temporary field, then delete the temporary field in order to "rename" your field back to what it was.

Bruce
  • 1