Updatable ... not

[RD]

Geez!

I have a fairly stable application I built about 3 years ago. It
imports data from several Oracle tables and allows data input on a
main form and produces management reports.

Recently we lost the connection to the Oracle database. I fixed that
but all of a sudden we can't input any data! Can't do it in the source
query either although we can go directly into the table that holds the
updatable data. Obviously can't allow the user(s) to do that.

WTF? Why, all of a sudden, in an application that has given us almost
perfect service, does it not want to allow us to update records
anymore? Nothing changed, as far as I know, except the tnsnames file
and the connection string and neither of those even comes into play
for the main data entry form nor the underlying query nor the final
data table.

I'm this || close to shooting myself.

Regards,
RD

 

[John Spencer]

Some points of confusion:
Are you importing data or are you using linked tables?

What did you do to "fix" the lost connection to the Oracle database?

When you say "go directly into the table" do you mean that you are
using a link to the table
or are you working directly in the Oracle table (no Access involved)?

If you build a new query based on the table, can you edit records in the new
query?

I don't know that I will be a lot of help since I have not had the pleasure
(or the pain) of working with an Oracle database as the data source for any
Access applications.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[RD]

Some points of clarification:
The application is split into a front-end/back-end configuration.
I have code in the front-end that imports data from csv files into
the back-end.
The back-end also links to Oracle tables for data matching and
validation.
By fixing the connection I mean I updated the tnsnames.ora file and
ran the linked table wizard in order to make those tables accessible,
which they now are.
By going "directly into the table" I mean I open the database window
and open the actual table that holds the data and there I am able to
make changes to the data. The table is an Access table. Not allowed to
work on data in the Oracle table. Big no-no around here.
I built a new query, just like the original query, and no, I still
can't update any records.

I think this is an Access issue, not an Oracle issue even though it
didn't start happening until after I had to update the table links. At
this point in time I'm dealing strictly with a form, based on a query,
based on linked tables from an Access back-end. Neither the table I'm
trying to update nor the second table in the query (which is there
only for displaying data that isn't in the other table) is an Oracle
table.

And and and ... this application has been working smoothly for 3
years. I'm beating my head against a wall trying to figure out why
this query, which has been working for years, suddenly doesn't want me
to make any updates to any field in any record.

Well, any help you can give me will be much appreciated, John.

Thanks,
RD

 

[John Spencer]

Hate to say this but I can't figure out the behavior. What follows is
speculation.

If you can update the table, you obviously should be able to update a query of
the table.

Just for trouble-shooting. Can you create a small database and link it to the
access tables in the backend and then create a query against the table and
update data. If so, then you know it is the front-end. If not, then you
might suspect a problem with the back-end.

If it is the front-end perhaps some corruption got introduced into the front-end.

Also, it could be the query itself. Could you post the SQL of the query?


Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document named
Fix Corrupt Access Database towards the bottom this page:

http://www.rogersaccesslibrary.com/forum/fix-corrupt-access-database-v45_topic25.html


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[RD]

Okee dokee. I created a new db, recreated the query and still can't
update any data.

The SQL follows. Two tables, one called "download" (not my choice, it
was neccessary) and the other called "CS_CSLD" which is derived from,
but not identicle to, the Oracle table it comes from. Both tables are
Access tables residing in the back-end.

You asked for it >

SELECT [Download].[SAMPLE_MONTH] & "/" & [Download].[SAMPLE_YEAR] AS
[Sample Month], Download.CASE_NUM, CS_CSLD.CASE_NM, Download.AID_CODE,
Download.REVIEW_NUM, CS_CSLD.CSLD_NUM, Download.RCVD_TANF_ASSISTANCE,
Download.HOH_WORK_ELIG, Download.HOH_DOB, Download.A1_PART_STATUS,
Download.A1_UNSUB_EMPLOY_HRS, Download.A1_SUB_PRIVATE_EMPLOY_HRS,
Download.A1_SUB_PUBLIC_EMPLOY_HRS, Download.A1_WORK_EXP_HRS,
Download.A1_MAX_HRS_WORK_EXP, Download.FAM_MAX_HRS_WORK_EXP,
Download.A1_ON_JOB_TRAINING_HRS, Download.A1_JOB_SEARCH_HRS,
Download.A1_MENTAL_HRS, Download.A1_SUBST_HRS,
Download.A1_DOMESTIC_HRS, Download.A1_COMM_SRVC_HRS,
Download.A1_MAX_HRS_COMM_SRVC, Download.FAM_MAX_HRS_COMM_SRVC,
Download.A1_VOC_ED_HRS, Download.A1_JOB_SKILL_HRS, Download.A1_ED_HRS,
Download.A1_SCHOOL_HRS, Download.A1_CHILD_CARE_HRS,
Download.A1_OTHER_HRS, Download.HOH_SCP_CHILD_LT_6YRS,
Download.HOH_FED_DISREGARD, Download.HOH_FVO,
Download.A1_REASON_NOT_AIDED, Download.FAM_SANCT_3MOS,
Download.HOH_VERIFY_EXEMPT, Download.CASE_COMMENTS,
Download.ADULT2_WE_IN_HOME, Download.ADULT2_DOB,
Download.MEET_2P_DEFINITION, Download.ADULT2_PART_STATUS,
Download.A2_UNSUB_EMPLOY_HRS, Download.A2_SUB_PRIVATE_EMPLOY_HRS,
Download.A2_SUB_PUBLIC_EMPLOY_HRS, Download.A2_WORK_EXP_HRS,
Download.A2_MAX_HRS_WORK_EXP, Download.A2_ON_JOB_TRAINING_HRS,
Download.A2_JOB_SEARCH_HRS, Download.A2_MENTAL_HRS,
Download.A2_SUBST_HRS, Download.A2_DOMESTIC_HRS,
Download.A2_COMM_SRVC_HRS, Download.A2_MAX_HRS_COMM_SRVC,
Download.A2_VOC_ED_HRS, Download.A2_JOB_SKILL_HRS, Download.A2_ED_HRS,
Download.A2_SCHOOL_HRS, Download.A2_CHILD_CARE_HRS,
Download.A2_OTHER_HRS, Download.ADULT2_FVO,
Download.A2_REASON_NOT_AIDED, Download.A2_EXEMPT_REVIEW,
Download.ADULT3_WE_IN_HOME, Download.ADULT3_DOB,
Download.ADULT3_PART_STATUS, Download.A3_UNSUB_EMPLOY_HRS,
Download.A3_SUB_PRIVATE_EMPLOY_HRS, Download.A3_SUB_PUBLIC_EMPLOY_HRS,
Download.A3_WORK_EXP_HRS, Download.A3_MAX_HRS_WORK_EXP,
Download.A3_ON_JOB_TRAINING_HRS, Download.A3_JOB_SEARCH_HRS,
Download.A3_MENTAL_HRS, Download.A3_SUBST_HRS,
Download.A3_DOMESTIC_HRS, Download.A3_COMM_SRVC_HRS,
Download.A3_MAX_HRS_COMM_SRVC, Download.A3_VOC_ED_HRS,
Download.A3_JOB_SKILL_HRS, Download.A3_ED_HRS, Download.A3_SCHOOL_HRS,
Download.A3_CHILD_CARE_HRS, Download.A3_OTHER_HRS,
Download.ADULT3_FVO, Download.A3_REASON_NOT_AIDED,
Download.A3_EXEMPT_REVIEW
FROM CS_CSLD INNER JOIN Download ON (Download.REVIEW_NUM =
CS_CSLD.REVIEW_NUM) AND (CS_CSLD.SAMPLE_YEAR = Download.SAMPLE_YEAR);

Pretty simple, huh?

This problem is threatening my very good rep around here. I'm supposed
to be the guy that can make Access sit up and beg. Now I can't even
get it to work. Maybe I'm just tired.

Thanks for your help,
RD

 

[John Spencer]

Hmm. I suppose that Download and CS_CSLD both have primary keys?

Check the tables to make sure they both have primary keys and include the
primary keys in the query.

If you run a query on just download, are the results updatable?
SELECT * FROM Download
How about if you run a query on CS_CSLD?
SELECT * FROM CS_CSLD

Next thing: Try dropping the primary key status on both tables (open each
table in design view, remove primary key status, close and save table, compact
database, open each table, and reassign primary key status)

I am grasping at straws and hoping this will show something.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[RD]

Oh, geez! Ok, both have PKs and actually the PKs are the same (once
again, not my choice but neccessary) and are included in the query,
BUT the PK has changed! I was originally told that the REVIEW_NUM
would be unique by year. Turned out ... nope. So I had to add
SAMPLE_MONTH to the PK which already had SAMPLE_YEAR and REVIEW_NUM in
it. Now I have a three piece PK consisting of SAMPLE_YEAR, SAMPLE
MONTH and REVIEW_NUM which I did not have before. At least now I can
uniquely identify each record.

Now we're getting somewhere.

So, why (now that I have a truly unique key/identifier) would this not
allow updates? I have no PK constraint errors. In fact, I have no
errors at all, just the "doink" sound. Gawd I hate the "doink" sound.
Just how unhelpful can they make this?

I am so ready to blow my brains out.

Once again, thanks for your help.
RD

 

[RD]

OMFG.

I added a relationship for the new part of the PK and now it's
working.

I have such a headache.