I'm using a database created several years ago with Access 2000, and it's
currently being used at the client site with Access 2003 (yes we'll upgrade
someday when client gets new hardware). The database is being regularly
used, and I have made very few design changes recently.
The current version of our database, doesn't allow new records to be added
(via the "Purchases" form; the Add New Record button with the "*" is
disabled. I also verfied that new records can't be added to the query this
form is based on "Purchases Query". I checked the properties for the
"Purchases Query" and the query hasn't been edited since Jan 2004, but we've
obviously been able to add purchases since Jan 2004.
The recent change I made to the database involved creating a copy of an
unrelated query and editing that copy. I also did a "Compact and Repair",
which I do from time to time. Is there a chance there's corruption causing
the problem? I've further tried to "Compact and Repair" but the problem
persists.
If curious here's the SQL code for the "Purchases Query"; it's pretty basic:
SELECT Purchases.PurchaseID, Purchases.StudentID, Purchases.EmployeeID,
Purchases.PurchaseDate, Students.FirstName, Students.LastName,
Students.PartnerFirstName, Students.PartnerLastName, Purchases.Discount,
Purchases.Memo
FROM Students INNER JOIN Purchases ON Students.StudentID=Purchases.StudentID;
Any thoughts on fixing this problem, or narrowing it down?
--Laura
|
|
0
|
|
|
|
Reply
|
 Utf
|
2/22/2008 5:59:06 PM |
|
Laura
Is the database "split" or are the forms, queries, reports AND tables all
stored in a single .mdb file?
Can you go directly to the query that used to work as the source and run it?
Do you get records? Check to see if that query is "updateable". If not,
take a look in Access HELP on "updateable queries".
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
"LauraB" <LauraB@discussions.microsoft.com> wrote in message
news:1B09B85C-73D6-439D-894D-E615DFA93B4F@microsoft.com...
> I'm using a database created several years ago with Access 2000, and it's
> currently being used at the client site with Access 2003 (yes we'll
> upgrade
> someday when client gets new hardware). The database is being regularly
> used, and I have made very few design changes recently.
>
> The current version of our database, doesn't allow new records to be added
> (via the "Purchases" form; the Add New Record button with the "*" is
> disabled. I also verfied that new records can't be added to the query
> this
> form is based on "Purchases Query". I checked the properties for the
> "Purchases Query" and the query hasn't been edited since Jan 2004, but
> we've
> obviously been able to add purchases since Jan 2004.
>
> The recent change I made to the database involved creating a copy of an
> unrelated query and editing that copy. I also did a "Compact and Repair",
> which I do from time to time. Is there a chance there's corruption
> causing
> the problem? I've further tried to "Compact and Repair" but the problem
> persists.
>
> If curious here's the SQL code for the "Purchases Query"; it's pretty
> basic:
> SELECT Purchases.PurchaseID, Purchases.StudentID, Purchases.EmployeeID,
> Purchases.PurchaseDate, Students.FirstName, Students.LastName,
> Students.PartnerFirstName, Students.PartnerLastName, Purchases.Discount,
> Purchases.Memo
> FROM Students INNER JOIN Purchases ON
> Students.StudentID=Purchases.StudentID;
>
> Any thoughts on fixing this problem, or narrowing it down?
> --Laura
>
>
|
|
|
0
|
|
|
|
Reply
|
Jeff
|
2/22/2008 6:10:36 PM
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0611_01C87554.1F7F2CB0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Hi Laura,
Just some thoughts;
Did you check the form properties, make sure that allow edits, allow=20
additions, etc. are set true/yes?
Did you originally define a relationship between the student table and =
the=20
purchases table? You might check and verify that the relationship hasn't =
been lost somehow.
You might try adding DISTINCTROW as in SELECT DISTINCTROW blah blah blah =
to=20
a copy of the query (unique records =3D true in the query properties =
sheet)=20
and see what happens.
I am assuming you opened the Purchases query? That is pulling up records =
so=20
we are getting a join, etc. and these are local database tables, so =
altered=20
permissions on a SQL Server preventing writes aren't a possiblity?
I noticed an inner join, but given the table and field names it sounds =
as if=20
the relationship should be a one student to many purchases - and the =
query=20
might be better formed as a
SELECT (fields list) FROM Students LEFT JOIN Purchases on =
Students.StudentID=20
=3D Purchases.StudentID;
Seems as if you would not be able to pull up a new student until after =
the=20
student had purchased something, by using an inner join. I can think of=20
scenarios where it might be practical to preload a student or list of=20
students into the system and have the data pre-populate the form based =
on=20
studentid, at time of purchase.
If all else fails - might add a dummy record directly to the student =
table=20
and a dummy purchase record to the dummy student - just to make sure the =
tables themselves weren't locked in a read only state for some reason.
As a last consideration, I wonder about pranks, a bright student =
tweaking=20
something for effect.
Hope this helps...
Gordon
"LauraB" <LauraB@discussions.microsoft.com> wrote in message =
news:1B09B85C-73D6-439D-894D-E615DFA93B4F@microsoft.com...
> I'm using a database created several years ago with Access 2000, and =
it's=20
> currently being used at the client site with Access 2003 (yes we'll =
upgrade=20
> someday when client gets new hardware). The database is being =
regularly=20
> used, and I have made very few design changes recently.
>=20
> The current version of our database, doesn't allow new records to be =
added=20
> (via the "Purchases" form; the Add New Record button with the "*" is=20
> disabled. I also verfied that new records can't be added to the query =
this=20
> form is based on "Purchases Query". I checked the properties for the=20
> "Purchases Query" and the query hasn't been edited since Jan 2004, but =
we've=20
> obviously been able to add purchases since Jan 2004.
>=20
> The recent change I made to the database involved creating a copy of =
an=20
> unrelated query and editing that copy. I also did a "Compact and =
Repair",=20
> which I do from time to time. Is there a chance there's corruption =
causing=20
> the problem? I've further tried to "Compact and Repair" but the =
problem=20
> persists.
>=20
> If curious here's the SQL code for the "Purchases Query"; it's pretty =
basic:
> SELECT Purchases.PurchaseID, Purchases.StudentID, =
Purchases.EmployeeID,=20
> Purchases.PurchaseDate, Students.FirstName, Students.LastName,=20
> Students.PartnerFirstName, Students.PartnerLastName, =
Purchases.Discount,=20
> Purchases.Memo
> FROM Students INNER JOIN Purchases ON =
Students.StudentID=3DPurchases.StudentID;
>=20
> Any thoughts on fixing this problem, or narrowing it down?
> --Laura
>=20
>
------=_NextPart_000_0611_01C87554.1F7F2CB0
Content-Type: text/html;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.6000.16609" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>Hi Laura,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Just some thoughts;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Did you check the form properties, make =
sure that=20
allow edits, allow <BR>additions, etc. are set true/yes?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Did you originally define a =
relationship between=20
the student table and the <BR>purchases table? You might check and =
verify that=20
the relationship hasn't <BR>been lost somehow.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>You might try adding DISTINCTROW as in =
SELECT=20
DISTINCTROW blah blah blah to <BR>a copy of the query (unique records =
=3D true in=20
the query properties sheet) <BR>and see what happens.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I am assuming you opened the Purchases =
query? That=20
is pulling up records so <BR>we are getting a join, etc. and these are =
local=20
database tables, so altered <BR>permissions on a SQL Server preventing =
writes=20
aren't a possiblity?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I noticed an inner join, but given the =
table and=20
field names it sounds as if <BR>the relationship should be a one student =
to many=20
purchases - and the query <BR>might be better formed as a</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>SELECT (fields list) FROM Students LEFT =
JOIN=20
Purchases on Students.StudentID <BR>=3D =
Purchases.StudentID;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Seems as if you would not be able to =
pull up a new=20
student until after the <BR>student had purchased something, by using an =
inner=20
join. I can think of <BR>scenarios where it might be practical to =
preload a=20
student or list of <BR>students into the system and have the data =
pre-populate=20
the form based on <BR>studentid, at time of purchase.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>If all else fails - might add a dummy =
record=20
directly to the student table <BR>and a dummy purchase record to the =
dummy=20
student - just to make sure the <BR>tables themselves weren't locked in =
a read=20
only state for some reason.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>As a last consideration, I wonder about =
pranks, a=20
bright student tweaking <BR>something for effect.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Hope this =
helps...<BR>Gordon<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>"LauraB" <</FONT><A=20
href=3D"mailto:LauraB@discussions.microsoft.com"><FONT face=3DArial=20
size=3D2>LauraB@discussions.microsoft.com</FONT></A><FONT face=3DArial =
size=3D2>>=20
wrote in message </FONT><A=20
href=3D"news:1B09B85C-73D6-439D-894D-E615DFA93B4F@microsoft.com"><FONT =
face=3DArial=20
size=3D2>news:1B09B85C-73D6-439D-894D-E615DFA93B4F@microsoft.com</FONT></=
A><FONT=20
face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> =
I'm using a=20
database created several years ago with Access 2000, and it's <BR>> =
currently=20
being used at the client site with Access 2003 (yes we'll upgrade =
<BR>>=20
someday when client gets new hardware). The database is being =
regularly=20
<BR>> used, and I have made very few design changes recently.<BR>> =
<BR>> The current version of our database, doesn't allow new records =
to be=20
added <BR>> (via the "Purchases" form; the Add New Record button with =
the "*"=20
is <BR>> disabled. I also verfied that new records can't be =
added to=20
the query this <BR>> form is based on "Purchases Query". I =
checked the=20
properties for the <BR>> "Purchases Query" and the query hasn't been =
edited=20
since Jan 2004, but we've <BR>> obviously been able to add purchases =
since=20
Jan 2004.<BR>> <BR>> The recent change I made to the database =
involved=20
creating a copy of an <BR>> unrelated query and editing that =
copy. I=20
also did a "Compact and Repair", <BR>> which I do from time to =
time. Is=20
there a chance there's corruption causing <BR>> the problem? =
I've=20
further tried to "Compact and Repair" but the problem <BR>> =
persists.<BR>>=20
<BR>> If curious here's the SQL code for the "Purchases Query"; it's =
pretty=20
basic:<BR>> SELECT Purchases.PurchaseID, Purchases.StudentID,=20
Purchases.EmployeeID, <BR>> Purchases.PurchaseDate, =
Students.FirstName,=20
Students.LastName, <BR>> Students.PartnerFirstName, =
Students.PartnerLastName,=20
Purchases.Discount, <BR>> Purchases.Memo<BR>> FROM Students INNER =
JOIN=20
Purchases ON Students.StudentID=3DPurchases.StudentID;<BR>> <BR>> =
Any=20
thoughts on fixing this problem, or narrowing it down?<BR>> =
--Laura<BR>>=20
<BR>></FONT></BODY></HTML>
------=_NextPart_000_0611_01C87554.1F7F2CB0--
|
|
|
0
|
|
|
|
Reply
|
gllincoln
|
2/22/2008 9:09:08 PM
|
|
Is the query selecting a linked table on another file server folder where
rights have been changed? We have had similar issues after upgrading our
file servers to Active Directory - where the folder rights have not migrated
properly. Also, you have said that it is 2000 on 2003 machine?? make certain
you get a clean compile.
--
Mike L
"LauraB" wrote:
> I'm using a database created several years ago with Access 2000, and it's
> currently being used at the client site with Access 2003 (yes we'll upgrade
> someday when client gets new hardware). The database is being regularly
> used, and I have made very few design changes recently.
>
> The current version of our database, doesn't allow new records to be added
> (via the "Purchases" form; the Add New Record button with the "*" is
> disabled. I also verfied that new records can't be added to the query this
> form is based on "Purchases Query". I checked the properties for the
> "Purchases Query" and the query hasn't been edited since Jan 2004, but we've
> obviously been able to add purchases since Jan 2004.
>
> The recent change I made to the database involved creating a copy of an
> unrelated query and editing that copy. I also did a "Compact and Repair",
> which I do from time to time. Is there a chance there's corruption causing
> the problem? I've further tried to "Compact and Repair" but the problem
> persists.
>
> If curious here's the SQL code for the "Purchases Query"; it's pretty basic:
> SELECT Purchases.PurchaseID, Purchases.StudentID, Purchases.EmployeeID,
> Purchases.PurchaseDate, Students.FirstName, Students.LastName,
> Students.PartnerFirstName, Students.PartnerLastName, Purchases.Discount,
> Purchases.Memo
> FROM Students INNER JOIN Purchases ON Students.StudentID=Purchases.StudentID;
>
> Any thoughts on fixing this problem, or narrowing it down?
> --Laura
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
2/24/2008 8:24:01 PM
|
|
|
3 Replies
579 Views
(page loaded in 0.092 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16776 articles. 
71 followers. 