127 Column Limit

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill - 31 May 2007 23:41 GMT

I inherited a small Access database that uses replication to support a
mobile application. While I'm not new to MS-Access, I am new to its
replication functionality.

1. Am I imagining a 127 column limit to tables in a replicated database? One
of the first changes I had to make to the database was to add columns to a
fairly wide table. All but one of the new columns are visible in Access.
That is, 127 columns are visible and the 128th isn't. But I know the column
was added successfully since I can see it (along with all the
replication-related columns) if I import the table to a non-replicated
database (and also Access won't let me add the column again in the
replication master).

2. How does one make a copy of the replication master for
development/testing? I've already found out I can't assume a non-replicated
database works the same as the replicated ones. And I'm certainly not going
to develop in the production system. What does one do?

TIA,
b

Reply to this Message

David W. Fenton - 01 Jun 2007 02:34 GMT

> I inherited a small Access database that uses replication to
> support a mobile application. While I'm not new to MS-Access, I am
[quoted text clipped - 4 lines]
> was to add columns to a fairly wide table. All but one of the new
> columns are visible in Access.

Do you have SHOW HIDDEN OBJECTS turned on? If not, you won't see the
replication fields.

> That is, 127 columns are visible and the 128th isn't. But I know
> the column was added successfully since I can see it (along with
> all the replication-related columns) if I import the table to a
> non-replicated database (and also Access won't let me add the
> column again in the replication master).

There is something wrong with your data schema if you think you need
127 columns. I've never had any tables beyond about 50 colums, and
even those could have been better normalized.

The limit on the number of fields in a replicated table is going to
depend on what kind of fields you have. Replication adds at least 3
fields, but for every memo field, it adds at least one more field.
If you have a lot of memo fields, you are likely to encounter
corruption problems and should probably split those out into a
separate memo table.

But 127 fields is without question a poorly normalized structure and
should be rethought from the ground up.

> 2. How does one make a copy of the replication master for
> development/testing? I've already found out I can't assume a
> non-replicated database works the same as the replicated ones. And
> I'm certainly not going to develop in the production system. What
> does one do?

Use a replica for your development and when you have to make schema
changes, make your replica the design master. Then when you roll out
your schema changes, you'll have to redo them in the real design
master.

Keep in mind that you should only be replicating data tables -- you
shouldn't be replicating the front end at all. You should have your
app split into back end (data tables only) and front end
(forms/queries/reports/etc.) with links to the back end. And the
front end should not be replicated because replication simply does
not work for front ends.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Bill - 01 Jun 2007 15:06 GMT

David,

Thanks for the quick response!

No, I didn't have SHOW HIDDEN OBJECTS on. I don't really want to see them
but when I do, now I'll know how.

As far as the schema and normalization... You are certainly correct. There
is always a better way to do things. However the take-away, for anyone else
reading this post, should be don't expect an Access application to work with
replication just because it worked without replication. If you do, the best
advice you may get is to start over and rethink it from the ground up.

To make a test-only design master, I navigated to Tools>Replication>Recover
Design Master. I clicked Yes, Yes, and OK. Now, I appear to have two
unlinked design masters, which is what I wanted.

Thanks again!
b

>> I inherited a small Access database that uses replication to
>> support a mobile application. While I'm not new to MS-Access, I am
>> new to its replication functionality.

Reply to this Message

David W. Fenton - 01 Jun 2007 20:20 GMT

> As far as the schema and normalization... You are certainly
> correct. There is always a better way to do things. However the
> take-away, for anyone else reading this post, should be don't
> expect an Access application to work with replication just because
> it worked without replication.

Excellent point to make here -- a non-replicated app that works
absolutetly perfectly can break when you replicate your back end
tables.

A prime example is the circular relationship, where you try to add a
record and put that record's PK in the circular relationship's FK
column (because the record is its own parent) -- it will break in
replication because the FK can't be validated until the record is
saved and the new PK saved, and the record can't be saved until the
FK field is filled out.

That was a real-world example I encountered in one of my early apps.

There are plenty of other examples like this.

But, thanks for emphasizing the point -- it really is a *very*
important one!

> If you do, the best
> advice you may get is to start over and rethink it from the ground
> up.

A replicated schema has certain restrictions that non-replicated
ones do not have.

> To make a test-only design master, I navigated to
> Tools>Replication>Recover Design Master. I clicked Yes, Yes, and
> OK. Now, I appear to have two unlinked design masters, which is
> what I wanted.

Yes, I said that would be the case, and said that you'd then have to
make the changes again in the DM once you were ready to push them
out.

Schema changes shouldn't happen very often at all once an app is in
production use, so this really oughtn't be much of an issue.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message