Search and Update - Difficult update query

[Guest]

I have 162 tables in my database. Names of the Tables are 1, 2, ...... so on
till 162. Every table has only one field (field name = Expr2) of type NUMBER
(DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify" which
has 701 records. First field (field name="Main" of data type "BYTE") of
Verify has serial numbers from 55 to 755. Names of the rest of the fields are
1, 2, 3, ..... so on till 162. All these remaining fields (except "Main") are
blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main" field
of "Verify" table in first field (Expr2) of the first table (1) and then
update field 1 (field name = 1) of verify with the count (number of times the
number 55 appeared in table 1) continue this till 701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
.. . . . . .
.. . . . . .
.. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to 755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
.. _ _ _ _
.. _ _ _ _
.. _ _ _ _
755 _ _ _ _


Not sure if I have explained the scenario properly. Please help...

Thanx
Maxi

 

[Marshall Barton]

I have 162 tables in my database. Names of the Tables are 1, 2, ...... so on
till 162. Every table has only one field (field name = Expr2) of type NUMBER
(DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify" which
has 701 records. First field (field name="Main" of data type "BYTE") of
Verify has serial numbers from 55 to 755. Names of the rest of the fields are
1, 2, 3, ..... so on till 162. All these remaining fields (except "Main") are
blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main" field
of "Verify" table in first field (Expr2) of the first table (1) and then
update field 1 (field name = 1) of verify with the count (number of times the
number 55 appeared in table 1) continue this till 701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
. . . . . .
. . . . . .
. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to 755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
. _ _ _ _
. _ _ _ _
. _ _ _ _
755 _ _ _ _

Not sure if I have explained the scenario properly. Please help...

I don't think anyone will be much help on this setup.
Without trying to be derogatory, I suggest that you either
read a chapter on database "normalization" to find out how
to structure your data as relational tables or use Excel to
process your spreadsheet structure.

 

[Guest]

Somehow the font is not working for this post.

Please view the later half of my post in courier new font. I have kind of
explained my scenario in a text-pictorial representation.

Maxi

 

[Guest]

Hi! Marsh

I will surely try to find some helpful links on normalisation and relational
databases. Since I am a beginner with Access, it is going to take me a lot of
time but thanx for the inputs, atleast it will give me a start.

Excel won't help coz there are 352716 records in each table. Even if I try
to export them, Excel will only accept 65536.

I can do this in Excel without any help but can you suggest of any method of
transferring these 352716 recrods into 6 different worksheets of the same
workbook? (65536, 65536, 65536, 65536, 65536, 25036)

Maxi

 

[Stephen Haley]

I am sure that someone will do better than this but here are 2 methods to be
getting on with, both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String) As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

method 1 sql update

UPDATE verify SET verify.[1] = fnreccount([main],"1"), verify.[2] =
fnreccount([main],"2"), verify.[3] = fnreccount([main],"3") etc etc

method 2 vba
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intValue As Integer
Dim x As Integer
strSQL = "SELECT * FROM verify"
Set rst = New ADODB.Recordset
rst.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
options:=adCmdText
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
For x = 1 To 255
rst(x) = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the value
in Field Main ie 352716 might be better to use longint.

rgds
Stephen

 

[Guest]

Thank you for your reply Stephen,

I have two questions

Question 1:

What exactly you mean by:

both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String) As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

Do you want me to add this in module? If yes, how can I do it without Sub()
and End sub

Question 1:

I did not understand this: Do yo want me to change the data type?

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the value
in Field Main ie 352716 might be better to use longint.

I am sure that someone will do better than this but here are 2 methods to be
getting on with, both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String) As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

method 1 sql update

UPDATE verify SET verify.[1] = fnreccount([main],"1"), verify.[2] =
fnreccount([main],"2"), verify.[3] = fnreccount([main],"3") etc etc

method 2 vba
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intValue As Integer
Dim x As Integer
strSQL = "SELECT * FROM verify"
Set rst = New ADODB.Recordset
rst.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
options:=adCmdText
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
For x = 1 To 255
rst(x) = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the value
in Field Main ie 352716 might be better to use longint.

rgds
Stephen

I have 162 tables in my database. Names of the Tables are 1, 2, ...... so
on
till 162. Every table has only one field (field name = Expr2) of type
NUMBER
(DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify" which
has 701 records. First field (field name="Main" of data type "BYTE") of
Verify has serial numbers from 55 to 755. Names of the rest of the fields
are
1, 2, 3, ..... so on till 162. All these remaining fields (except "Main")
are
blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main"
field
of "Verify" table in first field (Expr2) of the first table (1) and then
update field 1 (field name = 1) of verify with the count (number of times
the
number 55 appeared in table 1) continue this till 701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
. . . . . .
. . . . . .
. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to
755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
. _ _ _ _
. _ _ _ _
. _ _ _ _
755 _ _ _ _


Not sure if I have explained the scenario properly. Please help...

Thanx
Maxi

 

[Marshall Barton]

Yeah, you're right about there being too much data for
Excel. I was so blown away by the data structure that I
didn't even notice how many tables and records you were
trying to use.

Without knowing the problem you're trying to solve and the
data associated with each "entity" in your problem space, I
couldn't begin to suggest a normalized table structure.
However, anytime I see a sequence of tables named with
something other than a descrition of an entity, or fields
within a table that have a repeating naming convention, I am
going to think there just has to be a better way to attack
the problem.

You've explained HOW you're trying to solve a problem when
it would be more productive to explain WHAT you're trying to
accomplish. Hopefully, someone will be able to suggest a
better approach toward a solution.

 

[Stephen Haley]

Question 1
This is a user created function not a SUB routine hence the "Public Function
........End Function" format so you need to paste it in exactly as is into an
existing or new module for the project. Doesnt matter what you call the
module.

Question 2
If you think that there is the remotest chance that more than 32767 records
could exist for one of the numbers then yes you must change all the data
types in the table verify to Long Integer. If you try to store a number
greater han 32767 then it will fail and and error will be generated.

rgds
Stephen

Thank you for your reply Stephen,

I have two questions

Question 1:

What exactly you mean by:

both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String)
As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

Do you want me to add this in module? If yes, how can I do it without
Sub()
and End sub

Question 1:

I did not understand this: Do yo want me to change the data type?

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the
value
in Field Main ie 352716 might be better to use longint.

I am sure that someone will do better than this but here are 2 methods to
be
getting on with, both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String)
As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

method 1 sql update

UPDATE verify SET verify.[1] = fnreccount([main],"1"), verify.[2] =
fnreccount([main],"2"), verify.[3] = fnreccount([main],"3") etc etc

method 2 vba
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intValue As Integer
Dim x As Integer
strSQL = "SELECT * FROM verify"
Set rst = New ADODB.Recordset
rst.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
options:=adCmdText
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
For x = 1 To 255
rst(x) = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the
value
in Field Main ie 352716 might be better to use longint.

rgds
Stephen

I have 162 tables in my database. Names of the Tables are 1, 2, ......
so
on
till 162. Every table has only one field (field name = Expr2) of type
NUMBER
(DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify"
which
has 701 records. First field (field name="Main" of data type "BYTE") of
Verify has serial numbers from 55 to 755. Names of the rest of the
fields
are
1, 2, 3, ..... so on till 162. All these remaining fields (except
"Main")
are
blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main"
field
of "Verify" table in first field (Expr2) of the first table (1) and
then
update field 1 (field name = 1) of verify with the count (number of
times
the
number 55 appeared in table 1) continue this till 701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
. . . . . .
. . . . . .
. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to
755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
. _ _ _ _
. _ _ _ _
. _ _ _ _
755 _ _ _ _


Not sure if I have explained the scenario properly. Please help...

Thanx
Maxi

 

[Guest]

I keep getting error
Runtime error '2001': You cancelled the previous operation
on the line:
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))

Please help........

Maxi

Question 1
This is a user created function not a SUB routine hence the "Public Function
........End Function" format so you need to paste it in exactly as is into an
existing or new module for the project. Doesnt matter what you call the
module.

Question 2
If you think that there is the remotest chance that more than 32767 records
could exist for one of the numbers then yes you must change all the data
types in the table verify to Long Integer. If you try to store a number
greater han 32767 then it will fail and and error will be generated.

rgds
Stephen

Thank you for your reply Stephen,

I have two questions

Question 1:

What exactly you mean by:

both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String)
As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

Do you want me to add this in module? If yes, how can I do it without
Sub()
and End sub

Question 1:

I did not understand this: Do yo want me to change the data type?

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the
value
in Field Main ie 352716 might be better to use longint.

I am sure that someone will do better than this but here are 2 methods to
be
getting on with, both require the function below

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String)
As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

method 1 sql update

UPDATE verify SET verify.[1] = fnreccount([main],"1"), verify.[2] =
fnreccount([main],"2"), verify.[3] = fnreccount([main],"3") etc etc

method 2 vba
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intValue As Integer
Dim x As Integer
strSQL = "SELECT * FROM verify"
Set rst = New ADODB.Recordset
rst.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
options:=adCmdText
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
For x = 1 To 255
rst(x) = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub

One point to note is that in theory it is possible to blow your integer
datatype in the verify table if every record in a table contained the
value
in Field Main ie 352716 might be better to use longint.

rgds
Stephen



I have 162 tables in my database. Names of the Tables are 1, 2, ......
so
on
till 162. Every table has only one field (field name = Expr2) of type
NUMBER
(DOUBLE) with 352716 records in each table.

I have one more table with 163 fields. Name of the table is "Verify"
which
has 701 records. First field (field name="Main" of data type "BYTE") of
Verify has serial numbers from 55 to 755. Names of the rest of the
fields
are
1, 2, 3, ..... so on till 162. All these remaining fields (except
"Main")
are
blank and are of NUMBER (INTEGER) type.

Here is what I want:

The Query/SQL should look the first record (number 55) from the "Main"
field
of "Verify" table in first field (Expr2) of the first table (1) and
then
update field 1 (field name = 1) of verify with the count (number of
times
the
number 55 appeared in table 1) continue this till 701 records.

Continue the same thing with all the remaining fields till 162


1 2 3 4 5 .... 162 <=Table names
Expr2 Expr2 Expr2 Expr2 Expr2 Expr2 <=Field name
1 1 1 1 1 1 <=Records
2 2 2 2 2 2
. . . . . .
. . . . . .
. . . . . .
352716 352716 352716 352716 352716 352716 <=EOF

(These 352716 records per field per table are random numbers from 55 to
755)

Verify <=Table name
Main 1 2 3 .... 162 <=Field names
55 _ _ _ _
56 _ _ _ _
57 _ _ _ _
. _ _ _ _
. _ _ _ _
. _ _ _ _
755 _ _ _ _


Not sure if I have explained the scenario properly. Please help...

Thanx
Maxi

 

[Guest]

This is what I have in my module

It gives error....... please help

Option Compare Database

Public Function fnRecCount(ByVal value As Byte, ByVal tblStr As String) As
Integer
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function

Sub searchreplace()
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim intValue As Integer
Dim x As Integer
strSQL = "SELECT * FROM verify"
Set rst = New ADODB.Recordset
rst.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
options:=adCmdText
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
For x = 1 To 255
rst(x) = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub

 

[Guest]

I copied your code exactly the way it was and then I realised that the filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To 255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I guess
it is going to take a very long time to finish. Will post you the result if
it is exactly the way I wanted it.

Thanks for your errors

Maxi

 

[Stephen Haley]

DOH
Might well be my fault - dont know why but thought you had 255 tables
instead of 162
So the line
For x = 1 To 255
should be
For x = 1 To 162
sorry
Or it could easily be my ado which is still poor but getting better so if
that still fails try DAO as follows:-

Sub searchreplace()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("verify")
If Not rst.BOF Then rst.MoveFirst
If Not rst.EOF And Not rst.BOF Then
Do While Not rst.EOF
intValue = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = fnRecCount(intValue, CStr(x))
Next x
rst.Update
rst.MoveNext
Loop
End If
rst.Close
End Sub



Other problem may be that dcount is not just man enough for the job if so
might be best to build an array
and read each table into it - let me know if we need to try another way


stephen

 

[Stephen Haley]

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen

 

[Stephen Haley]

Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer also
best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

 

[Stephen Haley]

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub

Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen

 

[Guest]

Thank you for your inputs.

You were right, (it failed once) and then I changed the BYTE to INTEGER but
did not change the entire thing to LONG ( as I did not see your post at that
time) and tried running this code on my PC P4 2.8 GHz 256 MB and it is over
14 hours now and it is still not completed.

Now that I have seen your modified code, I will try running this on some
other PC. Lets see how long will it take.

Maxi

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub

Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen


I copied your code exactly the way it was and then I realised that the
filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To
255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I
guess
it is going to take a very long time to finish. Will post you the result
if
it is exactly the way I wanted it.

Thanks for your errors

Maxi

 

[Guest]

Its done !!! Issue resolved !!!!!

Finally, it took 14 hours and 37 minutes. BYTE was the only problem in the
earlier code that you gave me. I didn't even have to change it to LONG.
INTEGER did the trick.

I will still try and run the other code to check the efficiency if it can be
done FAST.

Thanks a million

Thank you for your inputs.

You were right, (it failed once) and then I changed the BYTE to INTEGER but
did not change the entire thing to LONG ( as I did not see your post at that
time) and tried running this code on my PC P4 2.8 GHz 256 MB and it is over
14 hours now and it is still not completed.

Now that I have seen your modified code, I will try running this on some
other PC. Lets see how long will it take.

Maxi

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub

Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen


I copied your code exactly the way it was and then I realised that the
filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To
255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I
guess
it is going to take a very long time to finish. Will post you the result
if
it is exactly the way I wanted it.

Thanks for your errors

Maxi

 

[John Nurick]

If speed is crucial, consider leaving your data in text files (IIRC
that's where it started) and writing code in a faster language than VBA
to compile the statistics in memory and then write a file containing the
summary table. I'd expect this to be at least 50 times faster than
VBA+recordset operations.

Its done !!! Issue resolved !!!!!

Finally, it took 14 hours and 37 minutes. BYTE was the only problem in the
earlier code that you gave me. I didn't even have to change it to LONG.
INTEGER did the trick.

I will still try and run the other code to check the efficiency if it can be
done FAST.

Thanks a million

Thank you for your inputs.

You were right, (it failed once) and then I changed the BYTE to INTEGER but
did not change the entire thing to LONG ( as I did not see your post at that
time) and tried running this code on my PC P4 2.8 GHz 256 MB and it is over
14 hours now and it is still not completed.

Now that I have seen your modified code, I will try running this on some
other PC. Lets see how long will it take.

Maxi

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub


Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen


I copied your code exactly the way it was and then I realised that the
filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To
255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I
guess
it is going to take a very long time to finish. Will post you the result
if
it is exactly the way I wanted it.

Thanks for your errors

Maxi

 

[Guest]

Thanx John but I never heard of IIRC yet and was wondering how one can do
this. In the mean time John and Stephen, I have started another post with a
subject "Search and Update - Another difficult update query". Can you please
help me with that?

Maxi

If speed is crucial, consider leaving your data in text files (IIRC
that's where it started) and writing code in a faster language than VBA
to compile the statistics in memory and then write a file containing the
summary table. I'd expect this to be at least 50 times faster than
VBA+recordset operations.

Its done !!! Issue resolved !!!!!

Finally, it took 14 hours and 37 minutes. BYTE was the only problem in the
earlier code that you gave me. I didn't even have to change it to LONG.
INTEGER did the trick.

I will still try and run the other code to check the efficiency if it can be
done FAST.

Thanks a million

Thank you for your inputs.

You were right, (it failed once) and then I changed the BYTE to INTEGER but
did not change the entire thing to LONG ( as I did not see your post at that
time) and tried running this code on my PC P4 2.8 GHz 256 MB and it is over
14 hours now and it is still not completed.

Now that I have seen your modified code, I will try running this on some
other PC. Lets see how long will it take.

Maxi

:

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub


Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen


I copied your code exactly the way it was and then I realised that the
filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To
255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I
guess
it is going to take a very long time to finish. Will post you the result
if
it is exactly the way I wanted it.

Thanks for your errors

Maxi

 

[John Nurick]

IIRC is short for "if I recall correctly".

Thanx John but I never heard of IIRC yet and was wondering how one can do
this. In the mean time John and Stephen, I have started another post with a
subject "Search and Update - Another difficult update query". Can you please
help me with that?

Maxi

If speed is crucial, consider leaving your data in text files (IIRC
that's where it started) and writing code in a faster language than VBA
to compile the statistics in memory and then write a file containing the
summary table. I'd expect this to be at least 50 times faster than
VBA+recordset operations.

Its done !!! Issue resolved !!!!!

Finally, it took 14 hours and 37 minutes. BYTE was the only problem in the
earlier code that you gave me. I didn't even have to change it to LONG.
INTEGER did the trick.

I will still try and run the other code to check the efficiency if it can be
done FAST.

Thanks a million

:

Thank you for your inputs.

You were right, (it failed once) and then I changed the BYTE to INTEGER but
did not change the entire thing to LONG ( as I did not see your post at that
time) and tried running this code on my PC P4 2.8 GHz 256 MB and it is over
14 hours now and it is still not completed.

Now that I have seen your modified code, I will try running this on some
other PC. Lets see how long will it take.

Maxi

:

err just tried it and it is horibly slow 40 secs per col ie nearly 2hrs

My array idea was by far the best as I suspected - did 10 cols of 38000recs
in arround 3 secs so total time should be 1min!


Try this instead

Sub searchreplace2()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intValue As Integer
Dim x As Integer
Dim y As Integer
Dim arry(700, 162) As Long
Set db = CurrentDb()
Debug.Print Now
For x = 1 To 162
Set rst = db.OpenRecordset(CStr(x))
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
arry(y - 55, x) = arry(y - 55, x) + 1
rst.MoveNext
Loop
End If
rst.Close
Next x
Set rst = db.OpenRecordset("verify")
If Not rst.EOF And Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
y = rst(0)
rst.Edit
For x = 1 To 162
rst(x).value = arry(y - 55, x)
Next x
rst.Update
rst.MoveNext
Loop
End If
Debug.Print Now()
rst.Close
end sub


Just ran a quick test here and I think you are going to run into another
problem in that you used the byte data type which stupidly I didnt think
about but merely aped
BYTE can only hold from 1 to 155 yet you say you have up to 755 in that
field in which case it cant be byte.
You will need to change the byte declaration in the function to integer
also best to change the function declaration to long as well
Like I said too much of a rush but hopefully we got there now

Public Function fnRecCount(ByVal value As Integer, ByVal tblStr As String)
As long
fnRecCount = DCount("[exp2]", tblStr, "[exp2]=" & CStr(value))
End Function


rgds
stephen

Sorry picked up the 162 but not the spelling mistake which was
almostcertainly the problem causing the runtime error. Simple answer is
never do things when the wife is expecting her dinner to be cooked - too
much rush....

Stephen


I copied your code exactly the way it was and then I realised that the
filed
name is expr2 and NOT exp2. May be this was a reason for RUNTIME Error

Then again I go another error for which I changed the line For x = 1 To
255
to For x = 1 To 162

Don't know whether I did it correctly but the code is still running. I
guess
it is going to take a very long time to finish. Will post you the result
if
it is exactly the way I wanted it.

Thanks for your errors

Maxi