Our replicated database recently became corrupted (for reason or
reasons undetermined) and data was lost. Indeed some fields from one
table were deleted!

As I was unable to restore the data to the Master Copy, I imported the
tables (back end) into a new database and this was successfully
completed. However, one particular form is served by an underlying
query which brings together three tables. Now here's the strange bit:
when the query is run in the front end database using the corrupted
database, the query works fine and records can be added to the various
tables through the query. However, when the same front end database is
connected to the new back end data database (the imported tables) the
query turns into a read only query and the DAO code fails when the
recordset reached the "addnew" section of code. I've tried everything
that I can think of to resolve this problem including recreating the
tables underlying the query, but to no avail.

Any thoughts anyone?

WillockBoy <stephen.layton@googlemail.com> wrote in
news:84b5e84d-4226-4094-aec1-c93c9ffdf379@t41g2000yqt.googlegroups.co
m: 

> Our replicated database recently became corrupted (for reason or
> reasons undetermined) and data was lost. Indeed some fields from
> one table were deleted!
> 
> As I was unable to restore the data to the Master Copy, I imported
> the tables (back end) into a new database and this was
> successfully completed. However, one particular form 

Is the front end replicated? If so, this is an error. Only data
tables should be replicated. Obviously, that means the app should be
split into front end (queries/forms/reports/etc.) and back end
(tables only). 

> is served by an underlying
> query which brings together three tables. Now here's the strange
> bit: when the query is run in the front end database using the
> corrupted database, 

OK -- false alarm! You're split. But I'll leave the warning above
for others. 

> the query works fine and records can be added to the various
> tables through the query. However, when the same front end
> database is connected to the new back end data database (the
> imported tables) the query turns into a read only query and the
> DAO code fails when the recordset reached the "addnew" section of
> code. I've tried everything that I can think of to resolve this
> problem including recreating the tables underlying the query, but
> to no avail. 

Are the tables otherwise editable? Are you using DAO 3.6 with a Jet
4 back end? 

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

WillockBoy <stephen.layton@googlemail.com> wrote in
news:84b5e84d-4226-4094-aec1-c93c9ffdf379@t41g2000yqt.googlegroups.co
m: 

> Our replicated database recently became corrupted (for reason or
> reasons undetermined) and data was lost. Indeed some fields from
> one table were deleted!

By the way, I hope you're trying to determine what caused the
corruption and planning to do something to fix it. There are very
few things that will cause replication to break if you're not
mis-using it. 

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

WillockBoy <stephen.layton@googlemail.com> wrote in
news:84b5e84d-4226-4094-aec1-c93c9ffdf379@t41g2000yqt.googlegroups.co
m: 

> I imported the
> tables (back end) into a new database and this was successfully
> completed. However, one particular form is served by an underlying
> query which brings together three tables. Now here's the strange
> bit: when the query is run in the front end database using the
> corrupted database, the query works fine and records can be added
> to the various tables through the query. However, when the same
> front end database is connected to the new back end data database
> (the imported tables) the query turns into a read only query and
> the DAO code fails when the recordset reached the "addnew" section
> of code. 

It's pretty unusual to do a DAO .AddNew in a recordset with more
than one table. I can't say why it ever worked, but it sounds like a
design error to me. 

Have you tried changing SELECT to SELECT DISTINCTROW, though? That
can very often make a non-updatable query updatable. It does require
that all the tables have primary keys (though I don't know for
certain if all the PKs have to be in the SELECT statement or not). 

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

On Mar 23, 9:12=A0pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> WillockBoy <stephen.lay...@googlemail.com> wrote innews:84b5e84d-4226-409=
4-aec1-c93c9ffdf379@t41g2000yqt.googlegroups.co
>
> It's pretty unusual to do a DAO .AddNew in a recordset with more
> than one table. I can't say why it ever worked, but it sounds like a
> design error to me.
>
> Have you tried changing SELECT to SELECT DISTINCTROW, though? That
> can very often make a non-updatable query updatable. It does require
> that all the tables have primary keys (though I don't know for
> certain if all the PKs have to be in the SELECT statement or not).
>
> --
> David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/

David

Many thanks for your replies and thoughts.

Unfortunately, I'm away from the office today and will not be able to
investigate some of your ideas until tomorrow. However:

1. Yes, the database is split between a back end tables database and a
front end (forms, reports, queries and modules)
2. I don't think that the front end is referencing Jet 3.6 with a Jet
4.0 back end, but I will check.
3. Yes, I will most certainly be attempting to find the reason for the
corruption.
4. A starting point will be changing the query to a SELECT
DISTINCTROW. I must admit that I hadn't realised that it was
inadvisable to use .AddNew when referencing a query based on multiple
tables. Thank you for that information, as I may well have written
other code in a similar way.

The thing which bugs me is that this particular code works when the
front end is linked to the old corrupt back end table but does not
when the front end is linked to the new back end with the imported
tables. However, each of the three tables underlying the query can
have records added to them individually, thus suggesting neither of
them is read only (records can be added to the query when viewed in
the corrupt back end but not in the new).

Speaking to the person who was using the database when it failed, it
seems that the sequence of events were:
1. Jet reported that there was more than one user accessing the
record, when there definitely wasn't.
2. It then reported that the record would be deleted ... which it
politely did as well as removing some of the fields in another table.

Judging by your comments ("There are very few things that will cause
replication to break if you're not mis-using it."), I must have built
in some "naff" code or relationships somewhere. Although, it must be
said that this same database (when not replicable) has worked
remarkably well for many years.

Just one more thing: If the repaired database was recovered into an
unreplicated database (using Micha's TSI Unreplicator), is it
advisable to remove (by using the make table query etc) the additional
replication fields from the tables?

I am most grateful.

WillockBoy <stephen.layton@googlemail.com> wrote in
news:c98a25cd-3d2b-4e34-979c-6a4edcdfc3a1@u9g2000yqb.googlegroups.com
: 

> 2. I don't think that the front end is referencing Jet 3.6 with a
> Jet 4.0 back end, but I will check.

I've lost the original post, but did you say what version of Access
you're running the front end in? If A2007, you should have the new
DAO reference (not 3.6 -- I'm too lazy to start up A2007 and wait
for the reconfiguration to check), but if you're using A2000, A2002
or A2003, then it should be DAO 3.6. 

> 4. A starting point will be changing the query to a SELECT
> DISTINCTROW. I must admit that I hadn't realised that it was
> inadvisable to use .AddNew when referencing a query based on
> multiple tables. Thank you for that information, as I may well
> have written other code in a similar way.

It's not so much "inadvisable" as something that would never occur
to me. Why would you *need* other tables in the recordset to which
you're adding records? 

> The thing which bugs me is that this particular code works when
> the front end is linked to the old corrupt back end table but does
> not when the front end is linked to the new back end with the
> imported tables. However, each of the three tables underlying the
> query can have records added to them individually, thus suggesting
> neither of them is read only (records can be added to the query
> when viewed in the corrupt back end but not in the new).

It makes me wonder if the new version lacks indexes present in the
old one. This could definitely change the updatability of SELECT
statement with multiple tables. 

> Speaking to the person who was using the database when it failed,
> it seems that the sequence of events were:
> 1. Jet reported that there was more than one user accessing the
> record, when there definitely wasn't.

That's usually a memo field pointer corruption.

> 2. It then reported that the record would be deleted ... which it
> politely did as well as removing some of the fields in another
> table. 

Sounds like you weren't editing memo fields unbound, which is
something that's even more important with a replicated data file
than it is with non-replicated data. 

> Judging by your comments ("There are very few things that will
> cause replication to break if you're not mis-using it."), I must
> have built in some "naff" code or relationships somewhere.
> Although, it must be said that this same database (when not
> replicable) has worked remarkably well for many years.

Was the back end replicated only recently?

> Just one more thing: If the repaired database was recovered into
> an unreplicated database (using Micha's TSI Unreplicator), is it
> advisable to remove (by using the make table query etc) the
> additional replication fields from the tables?

You'll have to before you can re-replicate. If you're not
re-replicating, it's just an annoyance to have the old fields. You
could move them to the end of the table to make them somewhat less
annoying. I do this in replicated databases as a matter of course,
just to make it easier to work with the data in queries and table
view. 

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

On Mar 24, 8:53=A0pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> WillockBoy<stephen.lay...@googlemail.com> wrote innews:c98a25cd-3d2b-4e34=
-979c-6a4edcdfc3a1@u9g2000yqb.googlegroups.com
> :
>
> > 2. I don't think that the front end is referencing Jet 3.6 with a
> > Jet 4.0 back end, but I will check.
>
> I've lost the original post, but did you say what version of Access
> you're running the front end in? If A2007, you should have the new
> DAO reference (not 3.6 -- I'm too lazy to start up A2007 and wait
> for the reconfiguration to check), but if you're using A2000, A2002
> or A2003, then it should be DAO 3.6.
>
> > 4. A starting point will be changing the query to a SELECT
> > DISTINCTROW. I must admit that I hadn't realised that it was
> > inadvisable to use .AddNew when referencing a query based on
> > multiple tables. Thank you for that information, as I may well
> > have written other code in a similar way.
>
> It's not so much "inadvisable" as something that would never occur
> to me. Why would you *need* other tables in the recordset to which
> you're adding records?
>
> > The thing which bugs me is that this particular code works when
> > the front end is linked to the old corrupt back end table but does
> > not when the front end is linked to the new back end with the
> > imported tables. However, each of the three tables underlying the
> > query can have records added to them individually, thus suggesting
> > neither of them is read only (records can be added to the query
> > when viewed in the corrupt back end but not in the new).
>
> It makes me wonder if the new version lacks indexes present in the
> old one. This could definitely change the updatability of SELECT
> statement with multiple tables.
>
> > Speaking to the person who was using the database when it failed,
> > it seems that the sequence of events were:
> > 1. Jet reported that there was more than one user accessing the
> > record, when there definitely wasn't.
>
> That's usually a memo field pointer corruption.
>
> > 2. It then reported that the record would be deleted ... which it
> > politely did as well as removing some of the fields in another
> > table.
>
> Sounds like you weren't editing memo fields unbound, which is
> something that's even more important with a replicated data file
> than it is with non-replicated data.
>
> > Judging by your comments ("There are very few things that will
> > cause replication to break if you're not mis-using it."), I must
> > have built in some "naff" code or relationships somewhere.
> > Although, it must be said that this same database (when not
> > replicable) has worked remarkably well for many years.
>
> Was the back end replicated only recently?
>
> > Just one more thing: If the repaired database was recovered into
> > an unreplicated database (using Micha's TSI Unreplicator), is it
> > advisable to remove (by using the make table query etc) the
> > additional replication fields from the tables?
>
> You'll have to before you can re-replicate. If you're not
> re-replicating, it's just an annoyance to have the old fields. You
> could move them to the end of the table to make them somewhat less
> annoying. I do this in replicated databases as a matter of course,
> just to make it easier to work with the data in queries and table
> view.
>
> --
> David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/

Further to your last post, I've checked up on several of the points
you've raised and, I think, have managed to get to the bottom of the
problem:

The database is Access 2003 and I've checked to make sure that it is
referencing DAO 3.6

The reason that the form's underlying query uses three tables is that
this particular form is a purchase order form creating orders for
contractors. The three tables are the main tblPurchaseOrders (holding
the main order information), the tblPurchaseOrderDetails (a one to
many relationship with the tblPurchaseOrders table - i.e. one order
can have many details) and the Contractors' table (tblContractors).
From what you are suggesting this would seem to be bad practice? I
have amended the query to be a SELECT DISTINCTROW query.

I followed up on your suggestions about the memo field corruption
(absolutely spot on and I have corrected the table so that the data is
not now stored in memo fields). You were also quite right about the
indexes.

I'm now ploughing my way through reconstructing the back end table,
having learnt some very valuable (and time consuming) lessons.

As always, very many thanks. much appreciated.

On Mar 24, 9:22=A0pm, WillockBoy <stephen.lay...@googlemail.com> wrote:
> On Mar 24, 8:53=A0pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> wrote:
>
>
>
>
>
> >WillockBoy<stephen.lay...@googlemail.com> wrote innews:c98a25cd-3d2b-4e3=
4-979c-6a4edcdfc3a1@u9g2000yqb.googlegroups.com
> > :
>
> > > 2. I don't think that the front end is referencing Jet 3.6 with a
> > > Jet 4.0 back end, but I will check.
>
> > I've lost the original post, but did you say what version of Access
> > you're running the front end in? If A2007, you should have the new
> > DAO reference (not 3.6 -- I'm too lazy to start up A2007 and wait
> > for the reconfiguration to check), but if you're using A2000, A2002
> > or A2003, then it should be DAO 3.6.
>
> > > 4. A starting point will be changing the query to a SELECT
> > > DISTINCTROW. I must admit that I hadn't realised that it was
> > > inadvisable to use .AddNew when referencing a query based on
> > > multiple tables. Thank you for that information, as I may well
> > > have written other code in a similar way.
>
> > It's not so much "inadvisable" as something that would never occur
> > to me. Why would you *need* other tables in the recordset to which
> > you're adding records?
>
> > > The thing which bugs me is that this particular code works when
> > > the front end is linked to the old corrupt back end table but does
> > > not when the front end is linked to the new back end with the
> > > imported tables. However, each of the three tables underlying the
> > > query can have records added to them individually, thus suggesting
> > > neither of them is read only (records can be added to the query
> > > when viewed in the corrupt back end but not in the new).
>
> > It makes me wonder if the new version lacks indexes present in the
> > old one. This could definitely change the updatability of SELECT
> > statement with multiple tables.
>
> > > Speaking to the person who was using the database when it failed,
> > > it seems that the sequence of events were:
> > > 1. Jet reported that there was more than one user accessing the
> > > record, when there definitely wasn't.
>
> > That's usually a memo field pointer corruption.
>
> > > 2. It then reported that the record would be deleted ... which it
> > > politely did as well as removing some of the fields in another
> > > table.
>
> > Sounds like you weren't editing memo fields unbound, which is
> > something that's even more important with a replicated data file
> > than it is with non-replicated data.
>
> > > Judging by your comments ("There are very few things that will
> > > cause replication to break if you're not mis-using it."), I must
> > > have built in some "naff" code or relationships somewhere.
> > > Although, it must be said that this same database (when not
> > > replicable) has worked remarkably well for many years.
>
> > Was the back end replicated only recently?
>
> > > Just one more thing: If the repaired database was recovered into
> > > an unreplicated database (using Micha's TSI Unreplicator), is it
> > > advisable to remove (by using the make table query etc) the
> > > additional replication fields from the tables?
>
> > You'll have to before you can re-replicate. If you're not
> > re-replicating, it's just an annoyance to have the old fields. You
> > could move them to the end of the table to make them somewhat less
> > annoying. I do this in replicated databases as a matter of course,
> > just to make it easier to work with the data in queries and table
> > view.
>
> > --
> > David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.c=
om/
> > usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/
>
> Further to your last post, I've checked up on several of the points
> you've raised and, I think, have managed to get to the bottom of the
> problem:
>
> The database is Access 2003 and I've checked to make sure that it is
> referencing DAO 3.6
>
> The reason that the form's underlying query uses three tables is that
> this particular form is a purchase order form creating orders for
> contractors. The three tables are the main tblPurchaseOrders (holding
> the main order information), the tblPurchaseOrderDetails (a one to
> many relationship with the tblPurchaseOrders table - i.e. one order
> can have many details) and the Contractors' table (tblContractors).
> From what you are suggesting this would seem to be bad practice? I
> have amended the query to be a SELECT DISTINCTROW query.
>
> I followed up on your suggestions about the memo field corruption
> (absolutely spot on and I have corrected the table so that the data is
> not now stored in memo fields). You were also quite right about the
> indexes.
>
> I'm now ploughing my way through reconstructing the back end table,
> having learnt some very valuable (and time consuming) lessons.
>
> As always, very many thanks. much appreciated.

Just one further point, if I may?

When I investigated your thoughts about the potential index problem, I
was presented with "error 3709". I rebuilt the table into another
table, re-created the primary key and deleted and re-configured the
relationships.

Is this the correct method of resolving this problem?

On Wed, 24 Mar 2010 14:22:35 -0700 (PDT), WillockBoy
<stephen.layton@googlemail.com> wrote:

>The reason that the form's underlying query uses three tables is that
>this particular form is a purchase order form creating orders for
>contractors. The three tables are the main tblPurchaseOrders (holding
>the main order information), the tblPurchaseOrderDetails (a one to
>many relationship with the tblPurchaseOrders table - i.e. one order
>can have many details) and the Contractors' table (tblContractors).
>From what you are suggesting this would seem to be bad practice? I
>have amended the query to be a SELECT DISTINCTROW query.

Yes. It would be bad practice.

It's routine to use the tools that Access provides - subforms, combo boxes,
and so on - rather than creating One Great Master Query to do everything. If
you had a Form based on tblPurchaseOrders, with a subform based on
tblPurchaseOrderDetails, and a combo box on the form based on tblContractors,
you would not be having this difficulty! You may want to use the Contractors
combo box NotInList event to allow for the possibility that you'll need to
enter a new contractor into tblContractors in the course of entering a new
purchase order.

-- 

             John W. Vinson [MVP]

WillockBoy <stephen.layton@googlemail.com> wrote in
news:52f0b928-449c-460e-abc2-99738ba31832@r1g2000yqj.googlegroups.com
: 

> The database is Access 2003 and I've checked to make sure that it
> is referencing DAO 3.6

The issue is not what format the db is but what version of Access
it's running in. A2007 will "fix up" the references to use its
versions of the relevant libraries, automatically, so if you checked
the references in A2007 they would be different from what you'd see
in A2003. The default DAO for A2003 is 3.6, and that's the reference
that should be present when you view references from A2003. 

> The reason that the form's underlying query uses three tables is
> that this particular form is a purchase order form creating orders
> for contractors. The three tables are the main tblPurchaseOrders
> (holding the main order information), the tblPurchaseOrderDetails
> (a one to many relationship with the tblPurchaseOrders table -
> i.e. one order can have many details) and the Contractors' table
> (tblContractors). From what you are suggesting this would seem to
> be bad practice? I have amended the query to be a SELECT
> DISTINCTROW query. 

Hmm. I guess I missed that you were adding a record with DAO using a
recordset of a form. As John Vinson tells you, it's nonstandard
practice to do what you're doing. 

Naturally, this doesn't explain why what worked before doesn't work
now, but maybe it's better to simply convert to a standard approach
to the problem than to try to figure out why the nonstandard one no
longer works. 

> I followed up on your suggestions about the memo field corruption
> (absolutely spot on and I have corrected the table so that the
> data is not now stored in memo fields). You were also quite right
> about the indexes.

That's not what I suggested. Memo fields are perfectly valid and
required in numerous situations. The issue is how you structure
their storage and how you edit them. In replicated apps, they are
particularly vulnerable to corruption if edited in bound controls
(they can be in a bound recordsource without problems). The way to
avoid that danger is to edit them with unbound controls. 

> I'm now ploughing my way through reconstructing the back end
> table, having learnt some very valuable (and time consuming)
> lessons. 

Have you looked into the index issue? That's the only one that I can
think of that would explain the problem you've described. 

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

WillockBoy <stephen.layton@googlemail.com> wrote in
news:b279336a-a56c-4954-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
m: 

> When I investigated your thoughts about the potential index
> problem, I was presented with "error 3709". I rebuilt the table
> into another table, re-created the primary key and deleted and
> re-configured the relationships.

Error 3709 is the "The search key was not found in any record."
error and is almost always an indication of corruption, often memo
corruption. 

In other words, probably not related to your main indexes.

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

On Mar 25, 8:28=A0pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> WillockBoy<stephen.lay...@googlemail.com> wrote innews:b279336a-a56c-4954=
-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
> m:
>
> > When I investigated your thoughts about the potential index
> > problem, I was presented with "error 3709". I rebuilt the table
> > into another table, re-created the primary key and deleted and
> > re-configured the relationships.
>
> Error 3709 is the "The search key was not found in any record."
> error and is almost always an indication of corruption, often memo
> corruption.
>
> In other words, probably not related to your main indexes.
>
> --
> David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/

Thank you John and David for your responses. I will work my way
through the database and change the forms which use underlying queries
invoking more than one table.

Apologies for misunderstanding what you were originally saying. I've
looked at the memo field issue again and have created an unbound
control which stores the typed-in data into the Memo field when the
after update event is triggered.

Is there any reason why the unbound control can't be an unbound memo
field?

Many thanks.
Stephen Layton

On Mar 25, 10:18=A0pm, WillockBoy <stephen.lay...@googlemail.com> wrote:
> On Mar 25, 8:28=A0pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> wrote:
>
>
>
>
>
> >WillockBoy<stephen.lay...@googlemail.com> wrote innews:b279336a-a56c-495=
4-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
> > m:
>
> > > When I investigated your thoughts about the potential index
> > > problem, I was presented with "error 3709". I rebuilt the table
> > > into another table, re-created the primary key and deleted and
> > > re-configured the relationships.
>
> > Error 3709 is the "The search key was not found in any record."
> > error and is almost always an indication of corruption, often memo
> > corruption.
>
> > In other words, probably not related to your main indexes.
>
> > --
> > David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.c=
om/
> > usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/
>
> Thank you John and David for your responses. I will work my way
> through the database and change the forms which use underlying queries
> invoking more than one table.
>
> Apologies for misunderstanding what you were originally saying. I've
> looked at the memo field issue again and have created an unbound
> control which stores the typed-in data into the Memo field when the
> after update event is triggered.
>
> Is there any reason why the unbound control can't be an unbound memo
> field?
>
> Many thanks.
> Stephen Layton

Forgive my stupidity! Just realised that I don't think there's any
such thing as an unbound memo field. I guess I mean an unbound text
box.

New.
The says of "READ.." I was in tears for so sakit kena kaca. Takuya removed 
it all. That is why I am so scared of glass. Plastic pun aiyo. So what? 
Can`t all mind their thoughts and speaches?

Monei and Grugs..
As in language. We do speak but we can`t teach. It`s an innerself knowledge.

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message 
news:Xns9D46A76825FE7f99a49ed1d0c49c5bbb2@74.209.136.92...
> WillockBoy <stephen.layton@googlemail.com> wrote in
> news:52f0b928-449c-460e-abc2-99738ba31832@r1g2000yqj.googlegroups.com
> :
>
>> The database is Access 2003 and I've checked to make sure that it
>> is referencing DAO 3.6
>
> The issue is not what format the db is but what version of Access
> it's running in. A2007 will "fix up" the references to use its
> versions of the relevant libraries, automatically, so if you checked
> the references in A2007 they would be different from what you'd see
> in A2003. The default DAO for A2003 is 3.6, and that's the reference
> that should be present when you view references from A2003.
>
>> The reason that the form's underlying query uses three tables is
>> that this particular form is a purchase order form creating orders
>> for contractors. The three tables are the main tblPurchaseOrders
>> (holding the main order information), the tblPurchaseOrderDetails
>> (a one to many relationship with the tblPurchaseOrders table -
>> i.e. one order can have many details) and the Contractors' table
>> (tblContractors). From what you are suggesting this would seem to
>> be bad practice? I have amended the query to be a SELECT
>> DISTINCTROW query.
>
> Hmm. I guess I missed that you were adding a record with DAO using a
> recordset of a form. As John Vinson tells you, it's nonstandard
> practice to do what you're doing.
>
> Naturally, this doesn't explain why what worked before doesn't work
> now, but maybe it's better to simply convert to a standard approach
> to the problem than to try to figure out why the nonstandard one no
> longer works.
>
>> I followed up on your suggestions about the memo field corruption
>> (absolutely spot on and I have corrected the table so that the
>> data is not now stored in memo fields). You were also quite right
>> about the indexes.
>
> That's not what I suggested. Memo fields are perfectly valid and
> required in numerous situations. The issue is how you structure
> their storage and how you edit them. In replicated apps, they are
> particularly vulnerable to corruption if edited in bound controls
> (they can be in a bound recordsource without problems). The way to
> avoid that danger is to edit them with unbound controls.
>
>> I'm now ploughing my way through reconstructing the back end
>> table, having learnt some very valuable (and time consuming)
>> lessons.
>
> Have you looked into the index issue? That's the only one that I can
> think of that would explain the problem you've described.
>
> -- 
> David W. Fenton                  http://www.dfenton.com/
> usenet at dfenton dot com    http://www.dfenton.com/DFA/

 

Hmm..very innocology.
Please loves, post the resipe to BETA. and I hope now that takde tipu 
beta..re-edit.Alwayse main thinks. And conti adds.

Monei and Takuya..The lame Sempoernae.

"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message 
news:Xns9D46A7A9E77E2f99a49ed1d0c49c5bbb2@74.209.136.92...
> WillockBoy <stephen.layton@googlemail.com> wrote in
> news:b279336a-a56c-4954-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
> m:
>
>> When I investigated your thoughts about the potential index
>> problem, I was presented with "error 3709". I rebuilt the table
>> into another table, re-created the primary key and deleted and
>> re-configured the relationships.
>
> Error 3709 is the "The search key was not found in any record."
> error and is almost always an indication of corruption, often memo
> corruption.
>
> In other words, probably not related to your main indexes.
>
> -- 
> David W. Fenton                  http://www.dfenton.com/
> usenet at dfenton dot com    http://www.dfenton.com/DFA/

 

New.
Thank you all. I know my Daddy is very strong and powerful. That being was 
very evil and wanted to kill my lame Daddy. He poked me but Daddy did it 
1`st. And I, Be. How can you forget me???
hmmm!!!

"WillockBoy" <stephen.layton@googlemail.com> wrote in message 
news:904d821c-89bc-4cc3-b203-940348f4b5bb@l36g2000yqb.googlegroups.com...
> On Mar 25, 8:28 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> wrote:
>> WillockBoy<stephen.lay...@googlemail.com> wrote 
>> innews:b279336a-a56c-4954-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
>> m:
>>
>> > When I investigated your thoughts about the potential index
>> > problem, I was presented with "error 3709". I rebuilt the table
>> > into another table, re-created the primary key and deleted and
>> > re-configured the relationships.
>>
>> Error 3709 is the "The search key was not found in any record."
>> error and is almost always an indication of corruption, often memo
>> corruption.
>>
>> In other words, probably not related to your main indexes.
>>
>> --
>> David W. Fenton                  http://www.dfenton.com/
>> usenet at dfenton dot com    http://www.dfenton.com/DFA/
>
> Thank you John and David for your responses. I will work my way
> through the database and change the forms which use underlying queries
> invoking more than one table.
>
> Apologies for misunderstanding what you were originally saying. I've
> looked at the memo field issue again and have created an unbound
> control which stores the typed-in data into the Memo field when the
> after update event is triggered.
>
> Is there any reason why the unbound control can't be an unbound memo
> field?
>
> Many thanks.
> Stephen Layton

 

Ours is super seksi lah! even when we know.


"WillockBoy" <stephen.layton@googlemail.com> wrote in message 
news:24691e14-441a-4a73-8094-edf64ab95d77@k13g2000yqe.googlegroups.com...
> On Mar 25, 10:18 pm, WillockBoy <stephen.lay...@googlemail.com> wrote:
>> On Mar 25, 8:28 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
>> wrote:
>>
>>
>>
>>
>>
>> >WillockBoy<stephen.lay...@googlemail.com> wrote 
>> >innews:b279336a-a56c-4954-ae9a-19cd896afe6d@g28g2000yqh.googlegroups.co
>> > m:
>>
>> > > When I investigated your thoughts about the potential index
>> > > problem, I was presented with "error 3709". I rebuilt the table
>> > > into another table, re-created the primary key and deleted and
>> > > re-configured the relationships.
>>
>> > Error 3709 is the "The search key was not found in any record."
>> > error and is almost always an indication of corruption, often memo
>> > corruption.
>>
>> > In other words, probably not related to your main indexes.
>>
>> > --
>> > David W. Fenton                  http://www.dfenton.com/
>> > usenet at dfenton dot com    http://www.dfenton.com/DFA/
>>
>> Thank you John and David for your responses. I will work my way
>> through the database and change the forms which use underlying queries
>> invoking more than one table.
>>
>> Apologies for misunderstanding what you were originally saying. I've
>> looked at the memo field issue again and have created an unbound
>> control which stores the typed-in data into the Memo field when the
>> after update event is triggered.
>>
>> Is there any reason why the unbound control can't be an unbound memo
>> field?
>>
>> Many thanks.
>> Stephen Layton
>
> Forgive my stupidity! Just realised that I don't think there's any
> such thing as an unbound memo field. I guess I mean an unbound text
> box.

 

> That's not what I suggested. Memo fields are perfectly valid and
> required in numerous situations. The issue is how you structure
> their storage and how you edit them. In replicated apps, they are
> particularly vulnerable to corruption if edited in bound controls
> (they can be in a bound recordsource without problems). The way to
> avoid that danger is to edit them with unbound controls.
> --
> David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.com=
/
> usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/

I wonder whether you could expand on the best method of viewing and
editing a memo field?

I created an unbound text box on the form and used the OnCurrent event
to load the memo field text into the unbound text box
(me.unboundtextbox =3D me.memofield)
and the unbound text box AfterUpdate event to copy the edited text
back to the memo field
(me.memofield =3D me.unboundtextbox)

I removed the actual memo field from the form and all seemed to work
well on my home desktop machine. However, when I uploaded the new
forms onto the server working copy, the VBA event code would not work
until the memo fields were added to the form. Very strange!

Have I understood the principle correctly? Should the memo fields be
stored in a separate table? Should the memo fields be updated and
viewed using a record set? Do you have a better method to offer?

Apologies for making this topic somewhat extended but I want to avoid
replication errors, if possible. With thanks.

"WillockBoy" <stephen.layton@googlemail.com> wrote in message 
news:70554024-1ced-4055-9558-5d1af2a53ef5@33g2000yqj.googlegroups.com...
>> That's not what I suggested. Memo fields are perfectly valid and
>> required in numerous situations. The issue is how you structure
>> their storage and how you edit them. In replicated apps, they are
>> particularly vulnerable to corruption if edited in bound controls
>> (they can be in a bound recordsource without problems). The way to
>> avoid that danger is to edit them with unbound controls.
>> --
>> David W. Fenton                  http://www.dfenton.com/
>> usenet at dfenton dot com    http://www.dfenton.com/DFA/
>
> I wonder whether you could expand on the best method of viewing and
> editing a memo field?
>
> I created an unbound text box on the form and used the OnCurrent event
> to load the memo field text into the unbound text box
> (me.unboundtextbox = me.memofield)
> and the unbound text box AfterUpdate event to copy the edited text
> back to the memo field
> (me.memofield = me.unboundtextbox)
>
> I removed the actual memo field from the form and all seemed to work
> well on my home desktop machine. However, when I uploaded the new
> forms onto the server working copy, the VBA event code would not work
> until the memo fields were added to the form. Very strange!
>
> Have I understood the principle correctly? Should the memo fields be
> stored in a separate table? Should the memo fields be updated and
> viewed using a record set? Do you have a better method to offer?
>
> Apologies for making this topic somewhat extended but I want to avoid
> replication errors, if possible. With thanks. 

On Mar 27, 1:13=A0pm, WillockBoy <stephen.lay...@googlemail.com> wrote:
> > That's not what I suggested. Memo fields are perfectly valid and
> > required in numerous situations. The issue is how you structure
> > their storage and how you edit them. In replicated apps, they are
> > particularly vulnerable to corruption if edited in bound controls
> > (they can be in a bound recordsource without problems). The way to
> > avoid that danger is to edit them with unbound controls.
> > --
> > David W. Fenton =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0http://www.dfenton.c=
om/
> > usenet at dfenton dot com =A0 =A0http://www.dfenton.com/DFA/
>
> I wonder whether you could expand on the best method of viewing and
> editing a memo field?
>
> I created an unbound text box on the form and used the OnCurrent event
> to load the memo field text into the unbound text box
> (me.unboundtextbox =3D me.memofield)
> and the unbound text box AfterUpdate event to copy the edited text
> back to the memo field
> (me.memofield =3D me.unboundtextbox)
>
> I removed the actual memo field from the form and all seemed to work
> well on my home desktop machine. However, when I uploaded the new
> forms onto the server working copy, the VBA event code would not work
> until the memo fields were added to the form. Very strange!
>
> Have I understood the principle correctly? Should the memo fields be
> stored in a separate table? Should the memo fields be updated and
> viewed using a record set? Do you have a better method to offer?
>
> Apologies for making this topic somewhat extended but I want to avoid
> replication errors, if possible. With thanks.

This code seems to work well on the limited testing done to date:

On the AfterUpdate event of the unbound control

Dim db As Database
Dim RS As Recordset

Set db =3D DBEngine(0)(0)
Set RS =3D db.OpenRecordset((Me.RecordSource), dbOpenDynaset)

With RS
..Edit
Me.memHouseMaintNotes =3D Me.strMaintenanceNotes
..Update
End With

RS.Close
db.Close

WillockBoy <stephen.layton@googlemail.com> wrote in
news:70554024-1ced-4055-9558-5d1af2a53ef5@33g2000yqj.googlegroups.com
: 

>> That's not what I suggested. Memo fields are perfectly valid and
>> required in numerous situations. The issue is how you structure
>> their storage and how you edit them. In replicated apps, they are
>> particularly vulnerable to corruption if edited in bound controls
>> (they can be in a bound recordsource without problems). The way
>> to avoid that danger is to edit them with unbound controls.
>> --
>> David W. Fenton � � � � � � � � �http://www.dfenton.com/
>> usenet at dfenton dot com � �http://www.dfenton.com/DFA/
> 
> I wonder whether you could expand on the best method of viewing
> and editing a memo field?

I've posted this a bazillion times, but here's how you edit memo
fields unbound: 

1. include the memo field in the form's recordsource.

2. add a textbox to the form, but don't assign the memo field as the
ControlSource. 

3. in the form's OnCurrent event, copy the memo field data from the
form's RecordSource to the textbox, e.g.: 

  Me!txtMyMemo = Me!MyMemo

4. in the unbound textbox's AfterUpdate, write the content of the
textbox back to the RecordSource and save it, e.g.: 

  Me!MyMemo = Me!txtMyMemo
  Me.Dirty = False

That's it for the simplest way to edit memos unbound.

It can be problematic if you have a problem with saving your record
at that point, though (e.g., you have complex validation logic that
you want running at some other point, rather than every time your
unbound memo field is edited), but you have to save the memo
immediately after it's edited in the textbox, or you've wasted your
time making it unbound. 

> I created an unbound text box on the form and used the OnCurrent
> event to load the memo field text into the unbound text box
> (me.unboundtextbox = me.memofield)
> and the unbound text box AfterUpdate event to copy the edited text
> back to the memo field
> (me.memofield = me.unboundtextbox)

You need to save the record immediately in the AfterUpdate or you've
wasted your time making it unbound. 

> I removed the actual memo field from the form and all seemed to
> work well on my home desktop machine. However, when I uploaded the
> new forms onto the server working copy, the VBA event code would
> not work until the memo fields were added to the form. Very
> strange! 

In some versions of Access, and in some set of circumstances I do
not comprehend, Access will not recognize a field in the
recordsource of the form/report referred to in VBA code unless
theres a control on the form with that name. I have never had that
issue with unbound memo fields or in forms (except when the code
reference was to a different form than the one it was running in,
e.g., a subform's code referring to a field in the recordsource of
the parent form). 

> Have I understood the principle correctly? Should the memo fields
> be stored in a separate table? Should the memo fields be updated
> and viewed using a record set? Do you have a better method to
> offer? 
> 
> Apologies for making this topic somewhat extended but I want to
> avoid replication errors, if possible. With thanks.

As I said above, I don't really know why you're having an error --
it sounds like you're doing precisely what I suggested in the 3
steps above (though you did use the . operator instead of the !
operator, and that very well might be the cause of the problem, and
explain why I've never encountered it, since I never use the .
operator for references to controls/fields). 

Moving memos to a different table can add another layer of
insulation from the fallout from a memo pointer corruption. It
doesn't make it more save to edit memos in bound controls, but it
does mean that if something goes wrong, you lose only the record in
the memo table, rather than the record in the main table. 

There are two approaches to this:

1. create a 1:1 table with 1 or more memos that used to live in the
main record. 

2. create a 1:N table with a record for each memo, and an indicator
field that designates which memo it is (this is only necessary when
you have more than one memo field). 

In either case, you'd then use a subform to edit the memo. You would
probably be safe using bound controls in that case, as long as the
AfterUpdate saves the edit immediately. 

Updating in code with DAO is something you want to avoid. There is
no reason I can think of that you should ever be forced to do that. 

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

WillockBoy <stephen.layton@googlemail.com> wrote in
news:fd654e76-0229-44bb-a9e6-977928b6c63d@x12g2000yqx.googlegroups.co
m: 

> This code seems to work well on the limited testing done to date:
> 
> On the AfterUpdate event of the unbound control
> 
> Dim db As Database
> Dim RS As Recordset
> 
> Set db = DBEngine(0)(0)
> Set RS = db.OpenRecordset((Me.RecordSource), dbOpenDynaset)
> 
> With RS
> .Edit
> Me.memHouseMaintNotes = Me.strMaintenanceNotes
> .Update
> End With
> 
> RS.Close
> db.Close

This will cause an error if your record is dirty in the form when
you run it. 

You should never have to do this. I suggest you try changing your
AfterUpdate code to use the ! operator instead of the . operator and
see if that helps (as I mentioned in my previous post). 

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

"WillockBoy" <stephen.layton@googlemail.com> escribi� en el mensaje de 
noticias 
news:84b5e84d-4226-4094-aec1-c93c9ffdf379@t41g2000yqt.googlegroups.com...
> Our replicated database recently became corrupted (for reason or
> reasons undetermined) and data was lost. Indeed some fields from one
> table were deleted!
>
> As I was unable to restore the data to the Master Copy, I imported the
> tables (back end) into a new database and this was successfully
> completed. However, one particular form is served by an underlying
> query which brings together three tables. Now here's the strange bit:
> when the query is run in the front end database using the corrupted
> database, the query works fine and records can be added to the various
> tables through the query. However, when the same front end database is
> connected to the new back end data database (the imported tables) the
> query turns into a read only query and the DAO code fails when the
> recordset reached the "addnew" section of code. I've tried everything
> that I can think of to resolve this problem including recreating the
> tables underlying the query, but to no avail.
>
> Any thoughts anyone?