Updating a table based on data in a second table (1 Viewer)

reglarh

Registered User.
Local time
Yesterday, 23:39
Joined
Feb 10, 2014
Messages
118
There seem to be many ways of tackling this and I have tried a number without success!

I have run a membership system and will be allowing subscription payments to be made via online banking. I can download bank transactions into Excel and then upload these into an Access table. However, the membership numbers from the Excel spreadsheet are not validated and may be wrong. Not all payments will be made at the same time, so there will be many interactions of the code over a few weeks.

So, I need to write some code that:

Loops through the Payment table;
Searches the membership table to check the existence of a valid member record;
If it finds a matching record, update the members record with payment date and update the payment record with an 'Success' status else update the payment record with a 'Failed' status.

Its a bit more complicated than that but it the use of the appropriate techniques for this logic that are causing me a problem.

Now if this was the 1960s using Cobol and Indexed Sequential files..........!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:39
Joined
Aug 30, 2003
Messages
36,118
Where are you stuck? I'd use a DAO recordset to loop the payment table. A DCount() to check the member number against the member table, unless you need to update it, in which case another recordset. I would assume it wouldn't be updated though. You'd use the Edit method of the recordset to update the payment table as appropriate.
 

reglarh

Registered User.
Local time
Yesterday, 23:39
Joined
Feb 10, 2014
Messages
118
Thanks Paul

That gives me confidence to continue since it is the approach I am taking. I do need to potentially update both tables, the members to record payment (if made) and the bank transaction table to record success or failure.

Initially I wrote a simple update query to action the updates to the member table and that worked well. However, it did not update the bank table and I had a problem of an incorrect membership number entered by the member through his banking system could update the member table incorrectly. So I now require the membership number and the first 4 characters of his surname to carry out additional checks. So I need to retrieve the member record by a member number search and then further check the surname.

I read through the bank transaction table and seek the matching record from the members table. How to I check whether a record has been found? Their seems to be no return code from the seek command. Unless I was up too late last night to find it!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:39
Joined
Aug 30, 2003
Messages
36,118
Not sure how you used Seek, but I wouldn't anyway. I'd open a recordset on an SQL that retrieved the desired record. Testing for EOF will tell you if it didn't find one:

Code:
If rs.EOF Then
  'no record found, do what you need to
Else
  'record was found, proceed as desired
End If
 

reglarh

Registered User.
Local time
Yesterday, 23:39
Joined
Feb 10, 2014
Messages
118
Thanks. Being a few hours ahead of you I managed to finally crack my problem and it all works well. Being a small database (600 members) I needn't worry about efficiency, but I find with Access that the casual user gets confused by their being many ways to solve a problem and not having any ideas on the relative merits of the difference approaches.

I don't want to take any more of your valuable time, but why would you not use seek? And do you imply that if I wrote an SQL statement to retrieve a record, not finding it would set an EOF condition? It smacks of having read through the whole table and not finding the record, whereas seek implies an indexed read.

Maybe I am too old for this game!
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:39
Joined
Oct 17, 2012
Messages
3,276
Pulling a record via SQL statement is far, far faster than opening a recordset and searching the records individually. As in several orders of magnitude faster. I've taken code that used record navigation, changed it to use of a SQL query, and seen processing times drop from twenty minutes to just over a second.

Every recordset comes with a BOF marker, an EOF marker, and any records it contains. If there are records, then when the recordset is first opened, the pointer begins on the first record, and BOF and EOF are both false. If there are no records, then both BOF and EOF are true.

The entire recordset does not need to be loaded in order to determine if EOF is true. That determination is available as soon as the first page of records is loaded, and that can be a really good thing.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:39
Joined
Oct 17, 2012
Messages
3,276
I don't want to take any more of your valuable time, but why would you not use seek? And do you imply that if I wrote an SQL statement to retrieve a record, not finding it would set an EOF condition? It smacks of having read through the whole table and not finding the record, whereas seek implies an indexed read.

Oh, one other thing. While SQL statements can search by criteria on non-indexed fields, their performance is generally significantly enhanced by appropriate use if indexes. Since Seek requires an index anyway, SQL retains its massive speed advantage, as well as being able to include criteria on non-indexed fields Seek isn't able to touch.
 

reglarh

Registered User.
Local time
Yesterday, 23:39
Joined
Feb 10, 2014
Messages
118
Thanks for the insight.

I'll give it a go, although my database is not large enough to have any significan processing times on an i7 with 16gb memory.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:39
Joined
Oct 17, 2012
Messages
3,276
Here's a good visual example for the difference between Seek and using SQL.

Let's say you have a bunch of file cabinets full of paperwork. This paperwork is in alphabetical order (the index), one letter per drawer.

You decide you need to find the folder for "Zulu, Shaka".

SQL means you scan the drawer labels until you find the Z's, skip to the back, skim the folder tabs until you find Zulu, and have the file on your desk inside a minute.

Seek means you start at 'Aardvark, Aaron the', pull his folder, read the name tab, then replace it and pull the next folder. Repeat until you've reached Zulu. (Bring some caffeine.)
 

reglarh

Registered User.
Local time
Yesterday, 23:39
Joined
Feb 10, 2014
Messages
118
I understand the point you are making, but my problem is that I cannot find anywhere where the underlying mechanisms for mechanisms such as seek can be found. The references produced by Microsoft only tell of the what a command does, not how it does it!
 

Users who are viewing this thread

Top Bottom