MsAccess freezing

[INFOS]

After operations on many records - MsAccess and everything else
(whole windows) just stop. I can continue only with ctrl-alt-delete.
For example if I run next code on 50.000,00 records:

Dim recset As Long
Dim COUNTER As Long
recset = DCount("*", "testtable")
COUNTER = 1

If recset > 0 Then
DoCmd.OpenForm "testform"
DoCmd.GoToRecord , , acFirst
Do
Forms![testform]![fieldA] = Forms![testform]![fieldB]
COUNTER = COUNTER + 1
DoCmd.GoToRecord , , acNext
Loop Until COUNTER - recset = 1
End If

If testtable has many records - everything stop after operation
is done (and it is done ok). Why is that happening? Is there any better way
to update big recordsets? Don't tell me to use update query - I can't do that
in this situation.

Thank you in advance !

 

[ssmithri]

This may be way off, but if I follow your code correctly, I do not believe you will ever get to "1".

Let's say you have 10 records in the table "recset"

Counter increments all of the way up to 10... where your formula will result in 10-10=0.

The next loop around, counter increments to 11, but then you have a command to go to the next record. What happens if there is no next record?

It may be as simple as saying Loop Until COUNTER - recset = 0

Maybe?

 

[The_Doc_Man]

The culprit has to do with opening forms and dynamically paging through them, I think. I'll show you a fairly simple test. I'm going to suggest that you run the Windows Performance Monitor that is (usually) found under Start >> Programs >> Accessories >> System Tools. The later your version of Windows, the more options you will have on the monitor control panel. You want to look at this and identify where the memory-related numbers are shown. If you can add memory factors to the real-time graph, better still!

Start the Perf monitor. Minimize the window but do not close it.

Start your Access code. Trigger your process that steps through the form's recordsource using DoCmd. Now immediately maximize the window with the perf monitor and see what resource is dropping to zero. 'cause this is almost surely a resource issue.

Don't tell me to use update query - I can't do that

Sure you can; you just don't want to or perhaps THINK you can't. But you really can. You just haven't told us what else is going on behind the form.

There is another way to skin this cat (MMMMEEEEEEOOOOOWWWWW! ) that does not involve paging through forms. Which is, I think, the source of your problem.

Windows garbage collection (memory reclamation, in this case) is not very good. I am betting that one of two things is happening. Either (A) you are consuming virtual space such that your swap file gets consumed, or (B) you are fragmenting system memory (one of the scratchpad pools, Gates alone knows which one). The Win Perf Mon will possibly find out which one for you. If you see one of the resources in the Perf Mon display drop to zero, that's your problem. No questions, no doubts.

So how do you do this without an update query?

If you can open a form in VBA, you can just as easily use VBA to open a recordset to whatever it is that drives the form (form's recordsource). Find the table or query that is the source of the form's fields. Directly open the implied recordsource rather than the form.

Now in your recordset loop, do an Edit on the recordset, copy field A to field B, do an Update on the recordset, and loop until you are done that way.

If your problem is that the form is driven from an implied query (i.e. when you open the form's recordsource in design mode you see an SQL statement, not a query or table name), cut/paste the query and make it a "real" query. Then open THAT as the recordset.

If the reason you can't do this in an update query is because of something else the form does for you when you do a "Forms_OnCurrent" event or some other event like "AfterUpdate", duplicate that code in the loop via cut/paste if no other way exists to do that. What you want to avoid is getting the graphics interface involved because of something called "system resources."

I think if you set aside the fixation on the form and perhaps just do this through a recordset operation, you will put far less strain & drain on memory. It will probably be a lot quicker, too!

 

[INFOS]

Reply for THE DOC MAN

I agree with you what causes the problem - but is there any way to do this job beside update query. I will explain better what I need on this example:

I have table "MAIN" with one field - "txt1"
and other table "TRANSACTIONS" with two fields "txt1" and "num2".
Field "txt1" in table "MAIN" is the primary key and it is related "one to many" with the field "txt1" in the table "TRANSACTIONS".
Table "MAIN" has 50000 records and table "TRANSACTIONS" has 500000 records.
What I do is next: I make form "UPDFORM" with record source - table "MAIN" and the form has field "txt1" with control source "txt1" and other calculated field "calcF" with control source - Dsum("[num2]";"[TRANSACTIONS]";"[txt1] = Forms![UPDFORM]![txt1]").
And then I run the code on this form to update field "txt1" with the value from the calculated field "calcF" and I must do that for every record.

If this is possible on some other way than this (better for the memory resources) - please tell me.

Thanks




the field "fieldX"

 

[The_Doc_Man]

And then I run the code on this form to update field "txt1" with the value from the calculated field "calcF" and I must do that for every record.

If you have a calculated field on the form, you can write a query with a calculated field to the same effect, same formula, same functions.

Anything you could have done in a form in the way of aggregates, you can do in a query. Really! (That's not true when the form runs VBA code like OnCurrent or OnChange type events, but that does not appear to be what you are doing based on your second description.)

There is NO better way with regard to system resources than to do the thing with an update query. PARTICULARLY now that I know that a simple DSum is involved. There isn't a thing in the world that would stop you from having a DSum in a query.

What is moderately surprising is that you were willing to go to the trouble of writing VBA code to open a form and navigate records, but weren't willing to write a (much simpler) update query. Trust me. Queries are cool.

The very worst that could happen is that you would have to write two queries. One to compute the sums as a simple select, then another one that JOINS to the first one in a way that supports the update operation. And a query of a query is perfectly normal with Access. Rather commonplace, in fact.

 

[INFOS]

Reply for the DOCMAN - try to solve this

Thanks for Yours help but I can't work it out with querys. You told me this:
"The very worst that could happen is that you would have to write two queries. One to compute the sums as a simple select, then another one that JOINS to the first one in a way that supports the update operation."
Problem is that I can't make update query that supports update.

Try to solve this situation:
You got:

"Table_A" with fields:
"CODE" (text) -primary key
"TOTAL" (number)

"Table_B" with fields
"CODE" (text) - external key
"AMOUNT" (number)


"Table_A" has 3 records:
CODE X Y Z
TOTAL null null null

"Table_B" has 5 records:
CODE X X Y Y Z
AMOUNT 12 8 16 2 10

Operation I need is next: I want to sum filed AMOUNT from "Table_B",and write that number in "Table_A" in the field "TOTAL" for every CODE.
"Table_A" will have this records then:
CODE X Y Z
TOTAL 20 18 10


I told you that I use code that opens the form with recordsource: "TableA"
and it has calculated field "Dsum("[amount]";"[Table_b]";"

 = Forms![myform]![code]")

What is the problem with query - I can't have expression in query like this:
"Dsum("[amount]";"[Table_b[B]]";"[CODE] = Querys![myquery]![code]")[/B]
Or can I ??????????

Other solution: If I make "SumQuery" on "Table_b" which sums field AMOUNT
grouped by field "CODE" and then I make "MyQuery" with the "Table_A" related
with the "SumQuery" by field "CODE" ("one to one" or "one to many relationship") - then it is not QUERY THAT SUPPORTS UPDATE OPERATION.

SO WHAT WOULD YOU DO ?
IS THERE SOMETHING IN MSACCESS THAT I DON'T KNOW HOW TO USE !

Thanks for yours time,
I'm waiting "eagerly"for yours answer.

 

[Louie]

Your Query could make Table_A as follows:

SELECT Table_B.Code, Sum(Table_B.Amount) AS SumOfAmount INTO Table_A
FROM Table_B
GROUP BY Table_B.Code;

 

[Rich]

The reality is that you should not be trying to store a calculated field in a table, why not just use a query to return the result?

 

[INFOS]

Reply for the LOUIE

You gave me "make-table" query as a solution, but what do You suggest me:
is this better solution for the application then mine. I have to make this action
a lot of times in a day on many records. So I would have to make table around 50 times a day and delete it then. Is that good - because application
is bigger and bigger every time I do that and I can't make compacting till
the end of a day because there is 10 users and I can't ask them to stop.

 

[INFOS]

Reply for RICH

I need to save calculated fields in a table 'cause after that operations
fields used to create expression (of the calculated field) will change.
EXAMPLE: I need avarage prices in exact moment. Hour later prices change.

 

[The_Doc_Man]

Ouch! Now I see the problem. This is an issue in table structure and normalization, technically. The question is, what do you DO with the prices for that hour before they change again? I assume you do SOMETHING with them, like calculate the cost of a particular sale. Then, having used the prices, what else besides a potential sale price depends on the computed prices once you compute them? And if prices change, do you ever have to go back and determine ex post facto what the prices must have been?

Here is the "real" issue. The prices in your table as you store them are NOT normalized. Because those prices do not depend on the prime key of the table. (They ALSO depend on the date&time, which presumably is NOT part of the prime key - 'cause I don't see any date/time data in any part of this discussion.) The prices that you see when you compute them get used in some way, I'm sure, and since the price isn't time-tagged, neither are the other items. Your normalization problem is probably pervasive, which means you are eventually going to be stuck at figuring out how a particular price became a particular value.

What you really need is a table where you have the date/time as a field and your code as a field and a price (value) as a dependent field. That is, you need to time-tag every contributor to your prices. Your prime key in this case should be the combination of date/time and item code.

THEN you could write a query (and not have to store it) where you provide date-ranges as part of the query criteria. Your totals could be computed in a query for any date range and you would not need to store anything beyond the raw data. The query itself could be written as a parameter query (which the Help files describe nicely enough). So your starting and ending dates could vary according to whatever business rule is in effect.

I would be very surprised if a summation query could not be written to accomplish this task very easily.

 

[INFOS]

Reply for the DOC MAN

I have pushed You in the wrong direction. I'm trying to use simple imaginary examples to describe a problem. But here's my whole situation now (excuse my bad english):

It is application for the company which we use for inputing data about goods supllys and we make invoices for the customers. We have a lot of supplys of same goods with the different price. All the supplied articles are in the table "SUPPLYS". When I make invoice for the customer I need to calculate average cost of the goods in that moment (moment of sale) and store that information in the table "INVOICES". In that moment I have maybe 200 records on one invoice and I need average price (cost) from the table "SUPPLYS" which has 500000 records.
I have explained to You what i do now - I make table with 200 records and calculated field for every record with sumary informations from table "SUPPLYS" for every article code. If I choose solution with "make-table" query - I would need to make table by summing every time all 500000 records for every invoice (or can I make table from summary table "SUPPLYS" but only for articles in my current invoice - "invoice number 4 for example...???).

So far - my solution with form (on maybe 200 records) isn't so bad (but I would prefer query solution). But from time to time I need to do this operation on all invoices - then I need to run code (i have describe before) on maybe 200000 records. Don't ask me why I need that (It would take me to many sentences) - let's I just need that.


Thanks for Yours help so far.

P.S. Is it possible to "make-table query" make new table with the primary key.

 

[Louie]

It would help me to understand your problem and maybe provide better help, if you could show the table structures of both the Supply and Invoice tables with just a few data entries, not 500000. Also a Results table i.e. where do you want the results put? Also would it be ok if the solution involved procedure code and not just one query?

Louie ...

 

[Louie]

Sorry I pushed post twice.

Louie ...

 

[INFOS]

Reply for LOUIE

I need solution that wont cause freezing of windows - I don't care is it code
or query.

Table Supplys has theese fields:
Supply_number - number - 1,2,3,4,....
Supply_date - date/time - 01.01.04, 02.01.04, ...
Supply_article - text - a55, b64, c546, ...
Supply_quantityIN - number - 1, 564, 684... (BOUGHT)
Supply_price - number - 2.35, 6.64, 8.66,...
Supply_quantityOUT - number - 15, 7, 56... (SOLD)

Table Invoices has theese fields:
Invoice_number - number - 101,102,103,104,....
Invoice_date - date/time - 04.01.04, 07.01.04, ...
Invoice_article - text - a55, b64, c546, ...
Invoice_quantity - number - 15, 7, 56...
Invoice_wholesaleprice - number - 3.41, 7.66, 9.52
Invoice_costprice - number THIS IS THE FIELD I NEED TO UPDATE WITH THE
INFORMATIONS ABOUT AVARAGE PRICES IN THE MOMENT OF SALE FROM
TABLE SUPPLYS - I HAVE INFORMATION IN THE TABLE SUPPLYS ABOUT
QUANTITYS ON STOCK AND COST PRICES.

I have skip irelevant fields.

 

[Louie]

From the last Info you gave me I made the following tables with the data you supplied:


Invoices
iNumber iDate iArticle iQuanity iWholeSalePrice iCostPrice
101 4/1/2004 a55 15 $3.41 $0.00
102 7/1/2004 b64 7 $7.66 $0.00
103 10/1/2004 c564 56 $9.52 $0.00



Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
1 1/1/2004 a55 1 $2.35 15
2 2/1/2004 b64 564 $6.64 7
3 3/1/2004 c564 684 $8.66 56


Will you please write the equation or words that would define the field iCostPrice give the data you supplied?

I know you front end is Access, but since the size of the data you mentioned is greater than 500,000 records, are you using Access or SQLServer as your backend?

Louie …

 

[INFOS]

Reply for LOUIE

I'm using access as a back-end application. Frozen screen is not a result
of 500000 records - same thing happening with 50000 records (for update).

It is important the order of imputing data in theese tables:
Follow me now:
1)First I've got two supplys - it makes theese data in tables:

Table-Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
1 1/1/2004 a55 100 $2.00 0
2 2/1/2004 b64 200 $6.00 0


2) Then I've got one invoice - it makes theese data in tables:

Table-Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
101 4/1/2004 a55 0 $2.00 50

Table-Invoices
iNumber iDate iArticle iQuanity iWholeSalePrice iCostPrice
101 4/1/2004 a55 50 $5.00 $2.00

3) Now I got supply of article a55 again (but different price):

Table-Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
3 10/1/2004 a55 100 $3.00 0


4) Now I'm selling this article again but know it is different cost price

 

[INFOS]

part 2

Sorry I've pressed enter.

Here's the result of action 4 on table data:

Table-Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
102 15/1/2004 a55 0 $2.66 50

Table-Invoices
iNumber iDate iArticle iQuanity iWholeSalePrice iCostPrice
102 15/1/2004 a55 50 5.00 $2.66

__________________________________________________
After all the actions data in tables looks like this:

Table-Supplies
sNumber sDate sArticle sQuanityIn sPrice sQuantityOut
1 1/1/2004 a55 100 $2.00 0
2 2/1/2004 b64 200 $6.00 0 (irelevant-just example)
101 4/1/2004 a55 0 $2.00 50
3 10/1/2004 a55 100 $3.00 0
102 15/1/2004 a55 0 $2.66 50

Table-Invoices
iNumber iDate iArticle iQuanity iWholeSalePrice iCostPrice
101 4/1/2004 a55 50 $5.00 $2.00
102 15/1/2004 a55 50 5.00 $2.66
_______________________________________________________

So sale of goods is recording in both tables.
_______________________________________________________

Why $2,66:

First I bought 100 pieces of article a55 and (cost) amount of $200 (100x2,00)
Then I've sold 50 pieces - cost prices $2,00 - so $100 of goods cost
---Now I have 50 pieces (100-50) and cost amount $100 (200-100)
Then I bought 100 pieces for price of $3,00 = $300,00
---Now I got 150 pieces (100-50+100) and cost amount $400 (200-100+300)
---Avarage price in this moment is 400/150 = 2,66666666666
So on next invoice (102) cost price is 2,66
---Now I got 100 pieces (100-50+100-50) and cost amount $266,7 (200-100+300-133,3)

If I buy (next Supply) 100 pieces for $1,00 now - avarage cost for the next
invoice would be: $1,8335. Pieces: 100-50+100-50+100 = 200 Cost-Amount: 200-100+300-133,3+100 = $366,7 366,7/200 = 1,8335

Do You understand what I need ?

 

[Louie]

Given Article, the following function would compute Average cost:

Function GetAverageCost(Article As String) As Currency

Dim dB As Database, Rs As Recordset

Set dB = CurrentDb()
Set Rs = dB.OpenRecordset("SELECT sArticle, Avg(Supplies.sPrice) AS AvgOfsPrice FROM Supplies " _
& "WHERE (sQuanityIn > 0) GROUP BY sArticle " _
& "HAVING (sArticle='" & Article & "'); ")
If (Not Rs.EOF) Then
GetAverageCost = Rs!AvgOfsPrice
Else
GetAverageCost = 0#
End If
Rs.Close
End Function

You can use this function in an append query to create the Invoice entry or call the following Sub i.e. Call CreateNewInvoice(102, "a55", 25, 0) :

Sub CreateNewInvoice(NewInvoiceNumber As Integer, Article As String, Quantity As Integer, WholeSalePrice As Currency)

Dim dB As Database, Ri As Recordset

Set dB = CurrentDb()
Set Ri = dB.OpenRecordset("Invoices")
Ri.AddNew
Ri!iNumber = NewInvoiceNumber
Ri!iDate = Date
Ri!iArticle = Article
Ri!iQuanity = Quantity
Ri!iWholeSalePrice = WholeSalePrice
Ri!iCostPrice = GetAverageCost(Article)
Ri.Update
Ri.Close

End Sub

We could have computed WholeSalePrice, but wasn't sure of its defination.

Lou ...

 

[INFOS]

Anyone?

Can Anyone Help Me With This ?