Reusing DAO variables within one sub procedure.

[REMark516]

Hi, I'm writing a procedure which has to make multiple queries to the same table multiple times. I'm opening, closing and using set <var>=nothing with each use.

I get an error <Compile error: Duplicate declaration in current scope.) the second time I try and open a connection to to perform my second query. (Code at bottom of posting)

any help would be appreciated? Should I be ReDimensioning the variables?

Thanks.. Mark


'Define DAO sql strings
Dim sql As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

'Define SQL statement to query tblMatches for correct match
'Using tblMatches.MatchId vs. combo5.value
sql = "Select * from tblMatches " & _
" WHERE MatchId=" & Combo5.Value & ";"

'Execute SQL Statement
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

'Assign recordset fields to variables
'Home/Away Team
mHomeTeam = rs("HomeTeam")
mAwayTeam = rs("AwayTeam")
...... more code

'close record set connection and set to null
rs.Close
db.Close
Set db = Nothing

------Second Query----------
'Begin logic to update player statistics
'Set 1 Game 1
'Define DAO sql strings
Dim sql As String ---------error occurs here--------
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

'Define SQL statement to query tblPlayer Stats for Away Player
'Using Set 1 Game 1 variable vs. tblPlayerStats (Away Player)
sql = "Select * from tblPlayerStats WHERE PlayerAway='" & mS401APlayerAway & _
"' AND PlayerTeam='" & mAwayTeam & "';"

'Execute SQL Statement
Set rs = db.OpenRecordset(sql, dbOpenDynaset)


'close record set connection and set to null
rs.Close
db.Close
Set db = Nothing
Set sql = Nothing

 

[willir]

Mark

A few things may help...
- Open the DB database once at the top, and then close it once at the bottom. No need to open / close it.

- Although you can open and close record sets, it adds to the readability to give each recordset a more meaningful name. For example rsMatch, rsStats...

- But your real problem is the SQL variable. SQL is a "string" - it is a variable. It is defined (DImmed) ONCE, and it is not SET. Instead, for a string, you can use SQL = "" .

(And while we are on the topic, avoid using names that may be reserved or have a specific meaning. A common naming issue is to use a field name called DATE. In your case, consider using strSQL or stSQL to imply you are working with a string variable)

How does a variable differ from the database object and record set object -- well, I guess I gave it away with "object". You are setting a pointer to a database object - you are reading from the database, and you may be updating the database. Whereas a variable is something in memory that can be manipulated.

Another consideration is to modularize your code - each module has a specific puerpose. It simplifies the understanding, debugging and often allows you to re-use your code. (Although I do not see an obvious case in your situation where the code could be re-used.)

Richard

 

[REMark516]

Richard, thank you for the prompt reply. I noticed my inappropriate use of the set sql=nothing after i posted. (My code never compiled that far down to throw an error.)

I agree, I sure don't see anywhere i'd ever use logic like this again. (This is for a dart league statistics database) The typical, oh, your a network engineer, you must know how to program databases! I don't envy you guys who live in VB code. Thanks for the advice and pointers.

I think alot of this twisted pseudo code could have been prevented with a database which was designed a little better. (Next time I'll know better!)

What I'm doing is reading one record from tblMatch, pulling win/loss info for 15 different games, dumping the whole record to variables.

then i proceed through each games variables, do a lookup in playerstats table, record existing values to temp variables, then increment existing values using the match variables, then perform an update back into the playerstats table.

It looks like i'll be dealing with 1 recordset for the match, and 30 different record sets for the playerstats.

So i think it will look like this:

define db connection

define rsMatch
open rsMatch
query rsMatch
define match variables
close rsMatch

define rsGame1Away
open rsGame1Away
select rsGame1Away
store rsGame1Away variables
close rsGame1Away

Increment rsGame1Away variables

DoCmd.SQL (update rsGame1Away variables)

define rsGame1Home
open rsGame1Home
select rsGame1Home
store rsGame1Home variables
close rsGame1Home

Increment rsGame1Home variables

DoCmd.SQL (update rsGame1Home variables)

.....Repeat for games 2-15 home and away

close db connection

 

[willir]

Hmmm

Your coding appears logical, and will most likely work. Don't open too many record sets at once - each set uses memory. Opening and closing them as you go through your code will be "kinder" to resources.

However...
Can you have an over arching module call this module 15 times rather than duplicating the code 15 times?

There are several ways to do this, and I do not have enough info from your post...

Event tied to a command button...

Dim strVariable as String, strHome as String, strAway as String
Dim lngPlayer as Long, lngAwayPlayer as Long, intScore as Interger

strVariable = Me.Combo5

FindMathInfo strVariable, strHome, strAway
'passes value in Me.Combo5 and returns your Home and Away

lngPlayer = ...
lngAwayPlayer = ...

FindStatInfo lngPlayer, lngAwayPlayer, intScore
'passes player vs away player and returns a score
...

Then you would have your sub modules defined elsewhere in the form module coding. (Or you can become brave and define modules in the module section)

[b]
Private Sub FindMathInfo (strVariable as String, strHome as String, strAway as String) [/b]

Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()
   
'Define SQL statement to query tblMatches for correct match
'Using tblMatches.MatchId vs. combo5.value
strSQL = "Select * from tblMatches " & _
        " WHERE MatchId= " & strVariable    
'Execute SQL Statement
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
...
rs.Close
db.Close
Set db = Nothing

End Sub

[b]
Private Sub FindStatInfo lngPlayer as Long, lngAwayPlayer as Long, intScore as Interger) [/b]

Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb()

strSQL = "Select * from tblPlayerStats WHERE PlayerAway ='" & lngAwayPlayer
...

You can call the same code, exchange the values in the passed variables.

Code can be improved by using a loop (Do While / For Next / Do Until)

You may wish to read up on coding. I can only go so far because I am not sitting next to you. Hopefully, I have helped a bit...

I have to sign off now. Groceries to buy for breakfast, finish laundry and get a bit of shut-eye.

Richard

 

[REMark516]

Richard thanks for the sample code, I was reading more on coding techniques and found an example using action queries that I can use which will simplify a solution for me.

The primary driver being that action queries do not have to be constructed using DAO, only Select queries need DAO contruction.

Action Queries can be constructed as follows:

Dim strSQL as String
strSQL="UPDATE Table1 SET Table1.Field1 = Field1 + 1 WHERE x=y and a=b;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Using this technique will reduce if not eliminate redundant select queries as well as keep my code simple.

I may even be able to dump tblMatch field name data into an array and do UPDATE queries in a loop, even further simplifying code and maybe making it reusable somewhere else.

Is there an area I can give you credit for helping me along my learning curve? I'll re-post if the solution works, if it doesn't I'm looking at manual solution in a spreadsheet (kidding).

Thanks..

Mark

 

[willir]

Absolutely.

As you become more familiar with coding techniques, and how they work, you will a) develope a coding style, b) determine which tools to use and when

Richard