My school registration database has not been quite right after a recent
compact and repair (done while I was away). Though most of the many
forms and subforms are working normally, one form in particular will no
longer allow new entries into the subform - the recordset of the
underlying query is no longer updatable.
The subform in question enters the identity and other vital info for
children whose parent's ID provides the FK, linking the parent's info
on the main form to the children's info on the subform. We can enter
children by manually addressing the underlying tblchildren, being
careful to enter the correct adult ID#.
The wierd part is that the same form/sub-form works fine with an old
copies of the same tables, before the compact and repair. Something in
either tblparents or tblchildren has changed that is disabling the
query, but damned if I can see anything that doesn't seem right. Any
suggestions out there? I'm running Access 2K.
Thanks!
Gary 6 3064
On 9 Jun 2005 13:05:33 -0700, "GaryDave" <ga******@hotma il.com> wrote:
Your assumption that the Compact caused the problem is probably
incorrect. This error just isn't being reported by others.
I would debug as follows:
1: Get current with all Office service packs and Jet 4.0 service
packs.
2: Repair & Compact both front-end and back-end databases.
3: Review subform properties. Are AllowEdits and AllowAdditions still
set to True? Anything weird with LinkMasterField s and LinkChildFields ?
You're not setting any of these properties in code, right. In the
Debug window type something like:
?Forms!frmMainF ormName!subfrmC ontrolName.Form .AllowAdditions
4: If the problem is not with the subform, convince myself that the
query itself is indeed not updatable. Typically it would only be
something like "select * from tblChildren order by Birthdate", which
is always updatable (assuming the table has a primary key).
5: Assuming your subform query is more complicated than that, start
with "select * from tblChildren order by Birthdate" and incrementially
add features until it becomes not updatable (when run in query view).
-Tom. My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in particular will no longer allow new entries into the subform - the recordset of the underlying query is no longer updatable.
The subform in question enters the identity and other vital info for children whose parent's ID provides the FK, linking the parent's info on the main form to the children's info on the subform. We can enter children by manually addressing the underlying tblchildren, being careful to enter the correct adult ID#.
The wierd part is that the same form/sub-form works fine with an old copies of the same tables, before the compact and repair. Something in either tblparents or tblchildren has changed that is disabling the query, but damned if I can see anything that doesn't seem right. Any suggestions out there? I'm running Access 2K.
Thanks!
Gary
Tom -
Thanks for your really useful and detailed reply. I know that it's
highly unlikely if not impossible for a C & R to create the sort of
problem I'm experiencing, but it is absolutely the only change in the
environment that occured in the lead-up to the current issue, so what
else could it be? I have various versions of the front-end, older and
newer, and they are all experiencing the same issue, so the backend
must be the culprit, somehow.
I will go through the steps you recommend. I can't run the underlying
subform query except with the form actually open, as it relies on combo
boxes on the main form for some of its fields. But I can still add
fields one-by-one as you suggest, then open the forms and see if they
are still updatable.
If I have an success I'll report it here for the benefit of others.
Gary
On 9 Jun 2005 23:00:34 -0700, "GaryDave" <ga******@hotma il.com> wrote:
You can temporarily "stub out" those references to parent fields: just
put in a value that the combobox might have.
-Tom. Tom -
Thanks for your really useful and detailed reply. I know that it's highly unlikely if not impossible for a C & R to create the sort of problem I'm experiencing, but it is absolutely the only change in the environment that occured in the lead-up to the current issue, so what else could it be? I have various versions of the front-end, older and newer, and they are all experiencing the same issue, so the backend must be the culprit, somehow.
I will go through the steps you recommend. I can't run the underlying subform query except with the form actually open, as it relies on combo boxes on the main form for some of its fields. But I can still add fields one-by-one as you suggest, then open the forms and see if they are still updatable.
If I have an success I'll report it here for the benefit of others.
Gary
I've seen intances where a R & C could remove the primary key property
of a field and delete table relationships. If a record in a table was
corrupted and R & C was not able to recover it, it sometimes changes
the value of the fields in that record to be ###### and place a null
value in the primary key field. Since a primary key field cannot
contains a null value, the primary key property of this field is then
removed. And thus, consequently, sometimes alter the table
relationships.
Our procedure after a R & C, especially if it was run to correct a
problem, is to always look for a table called MSysCompactErro r that
would have been created by Access during the R & C process. This table
would give you the ErrorTable name and ErrorDescriptio n. Open the
ErrorTable in datasheet field and sort it ascendingly by the primary
key field to make sure that there is no null value. If there is then
delete that record. Then switch to design view and make sure that the
primary key property still exist. Then open the relationship window
and check to make sure that all relationship for that that table still
exist. Finally, we would try to append the deleted record (if any)
back to the table using our hourly backup of the database.
Good Luck.
Julie Vazquez
"GaryDave" <ga******@hotma il.com> wrote in
news:11******** ************@o1 3g2000cwo.googl egroups.com: My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in particular will no longer allow new entries into the subform - the recordset of the underlying query is no longer updatable.
The subform in question enters the identity and other vital info for children whose parent's ID provides the FK, linking the parent's info on the main form to the children's info on the subform. We can enter children by manually addressing the underlying tblchildren, being careful to enter the correct adult ID#.
The wierd part is that the same form/sub-form works fine with an old copies of the same tables, before the compact and repair. Something in either tblparents or tblchildren has changed that is disabling the query, but damned if I can see anything that doesn't seem right. Any suggestions out there? I'm running Access 2K.
Others have answered the question in your subject (with a NO, and
they are correct). You don't say what version of Access, but it if
it's Access 2000, it sounds like a typical problem that occurs with
the shipping version of Jet 4, which was horridly buggy. Your
problem sounds like a corrupted index, or a lost seed value for an
AutoNumber field.
A2K is completely useless until you've patched it to these levels:
1. Access SR1 or higher (you have to run the full Office service
pack; the last was SP3, but that also included the Draconian email
security patches for Outlook; SR1 does not include that, but has
fixes to Access that are sufficient to make it perfectly stable)
2. Jet 4 SP6 or higher (the current SP is 8).
No A2K installation should be running with anything less than these
minimums, unless you don't care about the safety of your data.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
All -
Problem solved!! Julie clued me in to look for a problem with the
Primary Key, and in fact it had been removed from one of the key
tables. I remember now that the IT person who did the C & R mentioned
something about a corrupt record, so that had already been deleted. I
was able to restore the ID field as the PK, and bingo - the recordset
is now updatable.
Now I will see about updating the SPs - last time I tried, I think I
ran into problems because MS had stopped posting older SPs past a
certain #, and the newest SP would not update an SP as old as mine.
But anyway, the immediate problem is solved, so thanks to all!!!!
Gary This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: shaun palmer |
last post by:
When doing certain Data entries in a Database, the statement "you
can't go to the specified record "comes up in a message box. Yet in
other Data entries using the same procedure, this statement does not
occurr. Why is that and what can be done to solve the problem?
We have approximately 19,000 records. The relationships are set as
illusrated below. On my Custom focus form, the record source is a
query
One is made up of Business...
|
by: Ken Winters |
last post by:
Whenever I run the Repair/Compact on my database, the security settings on
the file change.
A 3rd party application can only access the database over the network when
the username "Everyone" is added to the "Group or user names" with access to
the file. But each time the Repair/Compact utility is run, it removes
"Everyone" from the list.
This is under Win2000. Can anyone offer some advise or suggestions?
Thanks
|
by: Lauren Wilson |
last post by:
Hi folks,
Somewhere, I recently saw an article header titled:
"How to compact and Repair a back-end Access db from the front-end
using VBA" or words to that effect.
Now that I need it, I cannot find it. Does anyone know were to find
such an article?
|
by: ApexData |
last post by:
Hello
I have finally completed an application and am preparing to install it
on a network of 10 users.
The application will be split FE/BE. The application has its own
login, which controls the
users ability to either ViewOnly or FullRights. Using Access2K. 20MB
Database.
My questions pertain to CompactOnClose:
|
by: G Gerard |
last post by:
Hello
The more I use an application ( an mdb) created using MSAccess I notice
that the Byte size of the application keeps on increasing. Once in a while I
do a Compact and Repair Database (under Tools in MSAccess) on the
application and the size then returns to normal.
| |
by: Roger |
last post by:
if I make a copy of northwind.mdb sample and import data till it
reaches 350Gb in size
and then I make a second copy of northwind
then I open nw1.mdb and do a compact / repair that takes 30-60 seconds
opening the nw2.mdb won't happen until compact repair is done
ditto if importing lots of data in nw1
ditto if inserting sql server data in a table within nw1
|
by: Wally |
last post by:
Hi there,
One table is corrupted. I found compacting is the cause or triggers
the process. I have the solution to repair the wrong records but after
each compact the same table is corrupted again at the same place.
After repairing the table by cutting rows and appending data from
outside (sql from coldfusion or from Excel), user may appends row(s)
in this table. Until compacting everything is fine with those new
records. After compacting,...
|
by: Bob Alston |
last post by:
For those of you that have replicated databases, how often to you
compact and repair them?
Also what mathod do you use if you do this on an automated way?
How do you make sure that the automated process is successful and if
not, what process of notification is used?
Bob
|
by: Timmy! |
last post by:
A2003 on Win XP Pro.
A number of very stable apps I have are lately sometimes crashes when
I do a compact & repair, especially when I hold the shift key to do
so.
When this starts happening, I usually turn off or restart my
computer. Sometimes this stops the crashing, sometimes it doesn't.
I'm really not sure why this is happening, I've never experienced it
before.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |