Discussion Locked

Access 97 JetSQL Update Query

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a need to process about 500+ updates to an Access97 table. Simple query, but it is record by record. (I can write sql, but have not mastered VBA..and my problem is that I need to do this today..)

In other SQL's I have worked with you could "stack" multiple queries. Like
updateqry#1....;
updateqry#2....:
and so on.

I created a file that has all the update statements built, all terminated with a ";"

But near as I can tell, either MS JetSQL doesn't support multiple update statements in the same query, I am using the wrong end of statement char or I am doing something else wrong.

Checked the web, checked my books, checked the on-line Access 97 help, but can not find anything that says yes I can or cannot do it.

Any suggestions would be greatly appreciated.

Thanks inadvance.

Doug Cranston

3 replies

by roc180

Jet does not support multiple updates in the same SQL statement, what you want to do is loop through the updates. Depending on where you hope to kick off the process the below code will work within the ON_Click event of a command button.

For intI=1 to 500
value1=intI
docmd.runsql "UPDATE TBL Set Field=" & value1
Next intI

Good Luck
Neal Martinelli

This discussion is closed to new replies.