Solved

SQL With Access97

Posted on 1998-08-19
6
215 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:msali
  • 3
  • 3
6 Comments
 

Accepted Solution

by:
shaloo earned 20 total points
ID: 1089624
#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
 

Author Comment

by:msali
ID: 1089625
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
 

Author Comment

by:msali
ID: 1089626
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Expert Comment

by:shaloo
ID: 1089627
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
 

Author Comment

by:msali
ID: 1089628
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
 

Expert Comment

by:shaloo
ID: 1089629
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question