db.execute strSql

[Siew-Ming]

Hi,

What's the best way to execute a query 26 times for different file names?

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."

* ASTU7001 and SCAS7002 can be replaced with ASTU7002 and SCAS7002 or
ASTU6001 and SCAS6001.


Thanks for you input in advance,
Sming

 

[Allen Browne]

Concatenate the names into the string, e.g.:
For i = 1 To 26
strSQL = "UPDATE ASTU7" & Format(i, "000") & " & " INNER JOIN ...
db.Execute ...
Next

 

[John Spencer]

I would use an SQL statement like the following and loop through the table
names assinging them to strTableA and strTableB.

I would build a table with two fields and 26 records containing the
tableNames.

Then you could execute code something like the following UNTESTED AIR CODE

Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim strTableA as String, strTableB as String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset ("SELECT * FROM tableListTables")

While rstAny.EOF = False
strTableA = rstAny!ASTUTable
strTableB = rstAny!SCASTable

strSql = "UPDATE [" & strTableA & "] as A INNER JOIN [" & strTableB "] as
S" & _
" ON A..[STULINK]=S.STULINK] " & _
" SET A.PRIMDISABI = """" " & _
" WHERE .[PRIMDISABI] Is Not Null And [A].[STATUS]) Is Null"
dbany.execute strSQL, dbFailOnError

rstAny.MoveNext

Wend


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..