SQL With Access97

Hi,
I have created 2 tables in SQL server
t_Procedure and t_ProcedureSub
In access i have created a form (DataSource=t_Procedure) with subform (DS=t_ProcedureSub)

Every thing is ok.

but when i enter data in sub-form and come back to view it again the records show #DELETED where as if i use Isql/w Query and write select * from t_ProcedureSub all records are saved.

Why is access showing #DELETED?

THANKS IN ADV.
msa
msaliAsked:
Who is Participating?
 
shalooConnect With a Mentor Commented:
#DELETED usually indicates that the table you have attached has had a change in structure since you last attached it.  Try deleting the attached tables and re-attaching them.
0
 
msaliAuthor Commented:
I have figured out the problem.  It might be what u r saying but in my case it was different.  In my case when i defined the table i had 4 field in my table - i had unknowingly in my sql statement had identified all four field as primary keys.

thanks for you answer.  i am still going to reward you the points since your answer will help me in near future.  

ps. i am new at sqlserver and am using PowerSoft's Data Architect to design databases in  SQL Server with Access 97.  


0
 
msaliAuthor Commented:
Hi,
I have got the answer on my own but i will be awarding you the points since the solution u gave me might be useful to me next time;
btw: in my case while defining the table in sqlserver i had unknowingly (since i am new at sql server) identified all fields as primary keys. once i remvoed them as pks it is now working just fine.

on the other hands i have lot other questions for instance (just a recomendation) i have a procedure table with 3000 procedures defined in it; currently i have 20 customers and the rates for all customers are diferent for every procedure i.e., every customer must have their unieque 3000 procedure prices (Procedure defs are the same except for prices).  what is the best way to handle it (RECOMMENDATIONS ONLY)
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
shalooCommented:
msali, I don't fully understand the performance impact of 3000 procedures being repeated 20 times but could you not rewrite all procedures to call 1 procedure for the rate and based on 2 parameters (customer name and product) it could send out the price.  This procedure could either access a separate table or else just be a series of IF statements?
0
 
msaliAuthor Commented:
Ok here i go:
It is a hospital that performs 3000 different procedures e.g., ECG, Blood Test, Urine Analysis(Complete or Partial) etc...etc...
We have 20 companies on or customer list who get treated for same procedure but have settled different rates with each coustomer. and yet there are customers who are not from our member customer list but otherwize e.g., walk-in patients or other referals etc.

So what i was thinking was having a standard procedure table for regular customers and a special table for every customer.  When a patient comes in we know what company it is from and if that company is on our list.  if so that company's procedure table is attached with the customer chart.  (makes sence or no)

thaks any way.
0
 
shalooCommented:
yes, what you said sounds like a good way to do it.  it is certainly not the most efficient as far as db design goes but certainly acceptable and easy to use.  To be most efficient you woudl hve a master table with procedure id, name and default price and a second table with the procedure id, customer id, and actual price.  In the firs table the procedure id would be autonumber and unqiue in the second table the procedure id would be a long number.  on the second table put a combined index on both the id fields and your queries will be faster enough also.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.