Solved

SQL With Access97

Posted on 1998-08-19
6
210 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 36
SQL Replication question 9 43
Sql server function help 15 31
SQL invalid column name 5 13
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 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