|
We have a DB that was migrated from MS Access to SQL Server 2008 R2. I wanted to continue to use Access as a front end to the DB since users are comfortable with it. But Access is unable to open the database without crashing frequently, corrupting tables, etc. etc.
There are tables that are linked to other tables (iCommodity is an int key to Commodity.ID etc.) The largest table is 250,000 records ~ 100MB.
It appears that most of the issues revolve around extended properties. Erasing them gets Access to work temporarily but it usually crashes again after re-writing the properties.
Is there any known issue that causes this behavior? What do other people do? I can write a application to get into the data but it seems ridiculous that Access can't even open a table without crashing.
Thx
Mark Jackson
|
|
|
|
|
mjackson11 wrote: Is there any known issue that causes this behavior?
Yup. Access tries to get everything into memory.
But why stick to Access? You could migrate away from a bloated client to a cleaner UX using just about any other language. I think VB3+ODBC gave better control then Access [as a UI] ever did.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
Have a bit more patience with newbies. Of course some of them act dumb -- they're often *students*, for heaven's sake. -- (Terry Pratchett, alt.fan.pratchett)
|
|
|
|
|
Can you post a sample of the code which is causing Access to crash ?
Are you accessing the SQL tables via Access linked tables ?
Are you sure that you are fetching only the data you need and not entire tables ?
Maybe I can offer some options that can get you over these pain-points without an entire application re-write.
|
|
|
|
|
Oh so now YOU want him to snd codz plz 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is no source code as the tables are linked directly to the SQL server. Access crashes if you try to open the table. It works for smaller tables but once you get past 50-60 MB it gets very dicey about crashing.
I suspect it is pulling entire tables.
|
|
|
|
|
mjackson11 wrote: What do other people do?
Typically no one with any choice would use Access as a front end, you are going to have to upset your users, unless that is you want to write the UI to match the look and feel of Access. I can think of worse fates for a developer but not many.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
mjackson11 wrote: What do other people do?
Per the other suggest that MS Access attempts to load all of it....
If that is the case then create a view(s) that limits the data set greatly. And link to that.
|
|
|
|
|
Hello,
And thanks in advance for the help.
I am using this query to group exams and count:
SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count]
FROM
[DB].[dbo].[Accessions] A
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
INNER JOIN [DB].[dbo].[table2] BPG ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID]
INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID]
INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID]
WHERE
--XRAY,US,PICC,EKG
(BPG.[GroupName] IN ('XRAY','EKG','US','PICC'))
AND
(F.[Name] LIKE '%' + @Facility + '%')
AND
(A.[ExamDate] >= CONVERT(DATETIME, @StartDate + @Year) AND A.[ExamDate] < CONVERT(DATETIME, @EndDate + @Year)
AND
(A.[Status] != 'Cancelled' AND A.[Status] != 'ADMIN CANCEL' AND A.[Status] != 'Cancelled - Dry Run'))
GROUP BY BPG.[GroupName]
Let's say the query only returns 'XRAY' and 'EKG' with a count. How can I also return that 'US' and 'PICC' have zero count. 
Thanks for the help.
|
|
|
|
|
Try to change join condition and see if you get result which you need.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thank you. I am not sure what to change and that is why I asked for help.
|
|
|
|
|
RadioButton wrote: INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
I think this should be LEFT OUTER JOIN instead. Also you should change the count to be
count(nvl(a.ProcedureID,0)) so the NULL values will be counted as zero. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I tried your suggestion and that did not change anything. I am using MS SQL 2008 and the NVL function does not appear to be avaible so I tried the ISNULL function. I believe it does the same thing.
Thanks again.
|
|
|
|
|
Connect to the BPG table and all of its inner joins first.
Then do a LEFT OUTER JOIN to your Accessions (A) table and it's joins.
This will give you all of the group names and the 0's if there is nothing for that group.
SELECT BPG.[GroupName], COUNT(A.[ProcedureID]) AS [Count]
FROM
[DB].[dbo].[table2] BPG
LEFT OUTER JOIN
([DB].[dbo].[Accessions] A
INNER JOIN [DB].[dbo].[table1] BP ON A.[ProcedureID] = BP.[ProcedureID]
INNER JOIN [DB].[dbo].[table3] O ON A.[OrderID] = O.[OrderID]
INNER JOIN [DB].[dbo].[table4] F ON O.[OrderingFacilityID] = F.[FacilityID]
)
ON BP.[ProcedureGroup] = BPG.[ProcedureGroupID]
WHERE
.....
GROUP BY BPG.[GroupName]
Brent
|
|
|
|
|
Hi
I need to write trigger for more than one table i.e single trigger for multiple tables,user can not update,delete,insert on tables.If user try to modify on sql server table need to show alert.
I tried with after trigger for single table , it works fine .please suggest me better way.
regards,
Vishnu.
|
|
|
|
|
As far as I know you can't but this thread has come up with a possible solution that you could use assuming the code in the trigger is the same for all tables
Single Trigger on multiple tables[^]
Basically the solution they are suggesting is to have the triggers call a common stored proc
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
vishnukamath wrote: user can not update,delete,insert on tables.If user try to modify on sql server table need to show alert.
Database permissions were designed specifically for what you are looking for. If you are implementing access controls using triggers, I would say that is not a good idea.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
There is table contain
ID Qty
----------
1 2
2 4
3 1
4 5
Now if i had to choose rows where sum of Qty equals to 10, How can i do this ?
like 2+4+1 = 7 but if i add 5 then 12
so ignore 2, then 4+1+5 = 10
How can i achieve this ?
I want id's of that rows which contain combination/sum equal to 10 (number i put)
|
|
|
|
|
declare @qty as decimal(18,2)<br />
set @qty=(select qty from mytable where id=4)<br />
<br />
select sum(qty)-@qty<br />
from myTable
By this example you will have result: 2+4+1=7
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
I think this cannot be done in a single query. You need to create combination by looping
One person's data is another person's program.
--J.Walia
|
|
|
|
|
To help you more how large would the table be that you have to loop through?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
at any given time max rows can b only 50-100,i am getting these rows from Select statement.
I put them in temp table, iterate over them in loop, something like that ??
Then from these rows i have to get rows whose sums equals to Qty given (input).
Regards
|
|
|
|
|
I do not think that it is possible to write a single query to achieve what you are trying to do. There could be more than a single set of rows whose sum of qty = 10, how would you handle that?
|
|
|
|
|
So you want to solve a knapsack problem[^] in SQL? You do realize the problem is NP complete?
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
|
|
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.4
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
