db.execute strSql

[Siew-Ming]

Hi,

Try to create a module that will update a field for different sites and years.

ASTU7001
SCAS7001

7 means year 2007; 001 means site 001

A regular query will look like this.

UPDATE ASTU7001 INNER JOIN SCAS7001 ON
[ASTU7001].[STULINK]=[SCAS7001].[STULINK] SET SCAS7001.PRIMDISABI = ""
WHERE ((([SCAS7001].[PRIMDISABI]) Is Not Null) And (([ASTU7001].[STATUS]) Is
Null));

However, if I can be more Access efficient, there will be better. I would
like to run the query when file are linked and move to the next sites. This
how I link my file currently.


Set dbs = CurrentDb()
Set rstFile = dbs.OpenRecordset("filename")
Set rstSchl = dbs.OpenRecordset("school")
Set rstyear = dbs.OpenRecordset("schoolYear")

rstSchl.MoveFirst
rstFile.MoveFirst
rstyear.MoveFirst

stryear = rstyear!schoolYear

For i = 0 To (rstSchl.RecordCount - 1)

strschl = rstSchl!schoolnum
strpath = rstSchl!schpath

For j = 0 To (rstFile.RecordCount - 1)
strfile = rstFile!filename



linkfile = strfile & Right(stryear, 1) & strschl
outfile = Left(linkfile, 8)
DoCmd.TransferDatabase acLink, "dBase IV", "c:\" & strpath &
"\datafile\data" & Right(stryear, 1) & strschl, acTable, _
linkfile, outfile, -1, True
rstFile.MoveNext



Debug.Print outfile
Next j
rstFile.MoveFirst
rstSchl.MoveNext
Set db = DBEngine(0)(0)
strSQL = "UPDATE ASTU7001 INNER JOIN SCAS7001 ON
[ASTU7001].[STULINK]=[SCAS7001].[STULINK] SET SCAS7001.PRIMDISABI = """"
WHERE ((([SCAS7001].[PRIMDISABI]) Is Not Null) And (([ASTU7001].[STATUS]) Is
Null));"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were updated."

Next i


End Sub


Try to use variable for the tables eg. ASTU & Right(stryear,1) & strschl,
but can't figure out all the syntax.


Any helps will be greatly appreaciated

Sming