Is there a difference?

[pikachew]

Hi all,
Is there a difference between rs("Fieldname") and rs.Fields("Fieldname")
i have always been using rs("Fieldname"), doesnt seem to be giving me a problem.
Although for a particular table in which many Tables need to be updated concurrently, there is a loss of data.
However for the rest of all my codes, rs("Fieldname") works perfectly.
Is the problem for me losing data in that particular table caused by using rs("Fieldname") instead of rs.Fields("Fieldname").
All my other codes using rs("Fieldname") works perfectly fine.

Please advise
Many Thanks

 

[The_Doc_Man]

To be excruciatingly precise and technically correct, never abbreviate the syntax of {record-set-name}.Fields( "Field-name" ) even though most of the time you are getting away with it. You are taking advantage of a default for tables. But ...

Although for a particular table in which many Tables need to be updated concurrently, there is a loss of data.

Do you mean "Many fields need to be updated concurrently" (in a single table)? In which case there should be no problem with defining your update to occur between a rs.Edit and a rs.Update

OR

Did you mean "Many tables need to be updated concurrently" (using a query with a JOIN element) ? In which case you may be running into ambiguitiesin the field names between two tables, particularly if you are updating one of the fields named in the JOIN clause.

 

[pikachew]

I am trying to update about 10 tables each time a visitor visits my site..
however there are often missing counts, meaning the total data in each table are not the same frequently They are supposed to be since all the tables get updated each time.
Im using MS Access 2003.
Below is an example of one of the function that updates one of the tables in my DB.
************************************************** *****
function GetIdRes(sName)
'Get ResID
sUserID = Request("UserID")
sSQL = "SELECT ResID, ResName, Total, UserID FROM Resolutions WHERE UserID = '"&sUserID&"' AND ResName = '" & sName & "'"
rs.Open sSQL,,,adCmdTable
if rs.eof then
rs.AddNew
rs("ResName") = sName
rs("Total") = 0
rs("UserID") = sUserID
end if
rs("Total") = rs("Total") + 1
rs.update
GetIdRes = rs("ResID")
rs.close
end function
************************************************** ****
I've got another 10 similar functions updating a corresponding 10 other tables
The Column "Total" is often different among the tables.

Please kindly advise...
Thanks

 

[pikachew]

To further illustrate the grevious problem that i have at hand.

Eaxmple.
i have 6 functions
Functions A,B,C,D,E,F
updating 6 corresponding tables Tables A,B,C,D,E,F
Meaning Function A only updates Table A, Function B updates Table B
each time my site (written in ASP) is hit by a visitor,
all the functions will be called.
All 6 tables have a coulmn called Total
Each time a visitor visits my site, all 6 functions will be called, updating their respective tables ( Coulmn "Total" in each table is incremented by 1)
Problem occurs when the "Total" value in the 6 Tables vary.
This occurs frequently when imy site has many visitors.
Example after 8 hits
Table A "Total" = 8
Table B "Total" = 8
Table C "Total" = 8
Table D "Total" = 7
Table E "Total" = 8
Table F "Total" = 8

Desperately need some advice on what could be the cause of this

 

[LaBelette]

If you have two simultaneous visitors, the two visitors may read the valeur in, let say, Table D. Both receive 6, and both update the total to seven.
Since it happens in a split second, the total may be ok in other tables.

Can you show us some code or give more details?

 

[LaBelette]

Pika?

By the way, are you the Pikachew I know?

The one who studied at Maisonneuve?

 

[pikachew]

Nope im not the pikachew you know..
Im from Singapore.


Here is another similar function im using
*********************************
function GetIdB(sB)
sUserID = Request("UserID")
sSQL = "SELECT WsID, WsName, Total, UserID FROM WinSize WHERE UserID = '"&sUserID&"' AND WsName = '" & sName & "'"
rs.Open sSQL,,,adCmdTable
if rs.eof then
rs.AddNew
rs("WsName") = sName
rs("Total") = 0
rs("UserID") = sUserID
end if
rs("Total") = rs("Total") + 1
rs.update
GetIdWinSize = rs("WsID")
rs.close
end function
****************************************
Lets jus say this is Function B, that updates Table B
the previous function is A that updates Table A

And you are right to say that they can hit simultaneously
**********************************************************
If you have two simultaneous visitors, the two visitors may read the valeur in, let say, Table D. Both receive 6, and both update the total to seven.
Since it happens in a split second, the total may be ok in other tables.
*********************************************************
But each visitor will cause all the tables to be updated at the same time doesnt it?
I mean we are down to a difference in seconds, does MS Access know how to differentiate between which one comes 1st.
Actually im the one testing the program now, i actually hit my site at a interaval of 1 or 2 second, which is almost concurrent but there is still a time lapse, however im still getting a loss of counts in some tables

Here is how the functions are called
**************************************************
'Open the database
OpenDB sConnStats

'Get ID's by Names
lIdA = GetIdA(sA)
lIdB = GetIdB(sB)
lIdC = GetIdC(sC)
lIdD = GetIdD(sD)
lIdE = GetIdE(sE)
lIdF = GetIdF(sF)
lIdG = GetIdG(sG)

******************************************************
This function updates a separate type of table different from the rest
******************************************************
sSQL = "SELECT * FROM Stats WHERE UserID = '"&sUserID&"'"
rs.Open sSQL,,,adCmdTable
'Save the data
rs.AddNew
rs("JaID") = lIdJa
rs("JsID") = lIdJs
rs("CookieID") = lIdCookie
rs("LanID") = lIdLan
rs("OsID") = lIdOS
rs("ColorID") = lIdColor
rs("BrowserID") = lIdBrowser
rs("PathID") = lIdPath
rs("RefID") = lIdRef
rs("ResID") = lIdRes
rs("WsID") = lIdWs
rs("Date") = Date
rs("Time") = Time
'rs("Time") = FormatDateTime(Now(), vbLongTime)
rs("IP") = sIP
rs("Country") = sCountry
rs("UserID") = sUserID
rs.Update

'Terminate database connection
CloseDB
*******************************************************

So this means that whenever a visitor visits my site, all this functions will be called and the Tables involved will be updated appropriately.
However after a few days, when the data bulits up

I get varying "Total"s from the Tables

Table A "Total" = 567
Table B "Total" = 566
Table C "Total" = 567
Table D "Total" = 566
Table E "Total" = 567
Table F "Total" = 565
Table G "Total" = 567

 

[Mile-O]

In what way are the six tables different from each other?

 

[pikachew]

Table A updates Variable A's Total
while Table B updates Variable B's Total.
All 6 Tables have a field name Total however they are the "Total" of different parameters.
"Total" is of the datatype Long Integer
Very simple.

 

[Mile-O]

Very simple.

Seems convoluted.

Any chance you can be more specific than A and B?

 

[The_Doc_Man]

You are collecting counts of visits by specific users but this is the wrong way to do it in Access, particularly because of problems with simultaneous updates. You should NOT increment a counter for this.

Instead, you should do something like store a record for each user-ID for each visit. Capture anything you need to capture in the visitation record. Then, as a separate action, run back through the day's accumulation of visits and update your counts.

Never expose your business statistics to the vagaries of real-time destructive interference. (Which is what I think is going on, based on the real-time nature of web hits.) In specific, I think your problem is that your Totals field can be read by two threads at once and can be written back by each thread to have the same exact value. This could occur easily if your locks are set up to NoLocks. The number of count discrepancies you report suggests that this happens on a frequent basis. At least often enough that you probably don't want to keep on doing what you are doing now if these counts are that important to you.

If I were to do this, I would append a new visitor record to a visitation table. In it, I would capture anything that had to be updated in any other table. (For instance, your stuff that you get from cookies like JaID, JsID, CookieID, ColorID, etc.) But I wouldn't visit those other tables until later.

When "later" arrives, I would have a second visitation table that holds the stuff I am about to process. I would erase the second visitation table, then archive my "live" visitation table to the second table, then purge the archived records out of the "live" table. Rather than outright copy everything, I would select an arbitrary time to be the archiving "time of interest." Then I would append every "live" record with a time earlier or equal to the selected time. Then my erase query would erase every "live" record using the same criteria.

Then I would visit the statistical tables for each item that needed to be updated and run my VBA code based on the archived visitation data as input and the real tables as output.

When I was done, if nothing else in the second visitation table mattered, I would erase it, leaving behind only the summary info. After that, any reports I needed, I would run.

The approach I described here implies the need to do a frequent repair and compress operation as part of regular DB maintenance. So factor that into your schedule.

Now, this means that you don't have real-time reports - but it means that you are less likely to lose counts. And it means that during the update process, you can see if something goes wrong and decide what to do to fix the problem.

Now, another issue...

Each time a visitor visits my site, all 6 functions will be called, updating their respective tables ( Coulmn "Total" in each table is incremented by 1)

Far be it from me to tell someone how to run their business, but unless some language difference has gotten in the way, this statement tells me your design is improperly normalized. If every visitor updates every table at every visit, why are the tables plural and not just a single table with multiple counters for the six categories you track? Why count something six times when you know you will count all six counters with every visit? Why not just count once and use it six times?

 

[LaBelette]

Since this is ASP...

In ASP, you can use the Session_OnStart event in the Global.asa file to make a hit counter.
If you don't know how, here's a good article:
http://www.w3schools.com/asp/asp_globalasa.asp

Example of global.asa file :

<script language="vbscript" runat="server">

sub Application_OnStart
''''some code
end sub

sub Application_OnEnd
''''some code
end sub

sub Session_OnStart
application.lock 'lock the application to prevent having two request to be send at the DB at the same time

' You'll want to update you database *here*

application.unlock ' ...and unlock it so it can continue working!
end sub

sub Session_OnEnd
''''some code
end sub

</script>

 

[pikachew]

Thanks for all the advise people.
By the way, i have a single Table that stores all all necessary IDs of the other 6 tables. However, the reason for the other 6 tables is to store other inforamtion like browser names(very long), Operating Systems names ( very long as well)...and other stuff..
I do not want all the information to be burdened on one table, therefore having several other tables which jus updates a counter, if that particular user uses that particular operating system thus the cloumn "Total"

My inserting all the data into one table, means whenever i query the table "Stats" of some particular info, i will have to query through the whole table.
And if my table grows to say 100,000, the amount of time it needs to query will be disaterous.
Instead, if i want to display info on the Operating System, all i need to do is to go to the relatively small n cute OS table and retreive the information i need.
My last resort is to lump everything into one table which whould mean my counts will never go missing and data will always be correct..
however thinking of performance and good programming habits...i have decided that will be my last resort.
I know its very hard for the updates to be almost real time, and that most programs written so far would all face similar problems although most people wont notice the missing counters.. when the hits reach example 120136 instead of 120245, i dont think people notice...
however i am trying to find a way to solve this problem.

Many thanks to the advise although they do not really solve my problem
however thanks for all the time you guys have put into for writing me your suggestions.
Greatly appreciate it..

 

[pikachew]

Can someone kindly give me an idea of how to implement locks?
I think there is a way to synchronize the updating of the
" rs.Open sSQL,,,adCmdTable "
using ADOs like these
'ADO Constants
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200


Last but not least...i open my database connection like this

sub OpenDB(sConn)
'Opens the given connection and initializes the recordset
conn.open sConn
set rs.ActiveConnection = conn
rs.CursorType = adOpenStatic
end sub

Do i need to change anything?
Please Advise
Many Many Many Thanks

 

[pikachew]

I have finally managed to syncronized the updating of the tables such that they are all the same.
I defined

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

I used adOpenDynamic instead of adOpenStatic.
I used rs.adLockPessistic on the adCmdTable and wala!
The tables are updating correctly,
tested it a few times and it seems ok.

However can anyone tell me the performance comparison between
adOpenDynamic & adOpenStatic
or adLockPessistic & Const adLockOptimistic.
Whats the difference between the 2 of them in terms of performance.
Was reading through the w3school website, hoever the description of the ADO is not as clear as i would like them to be.

Please Advise