How to run update batch query

[sg]

Hi,

I have at least over 3 update table queries. I like to run them at one time
by using macro if possible. Please help!

Thanks,
Sarah

 

[tina]

use an OpenQuery action to run each query.

hth

 

[sg]

Hi Tina,

Thank you for your response. The issue that I have is that I wrote up update
statements, I like to run them at one time.
Update table set id=1 where sn=1
update table set id=2 where sn=2....
.......
It is fair long statements. I don't know how I do this with openquery.
Can you give me further help please?
Thanks,
Sarah

 

[tina]

your initial post said you have "over 3 update table queries". are they
Update queries - that is, are they query objects in the Query tab of the
database window? or are they SQL statements, written in a VBA code module?
if neither, where and what are these "update table queries"?

 

[sg]

Hi Tina,

They are actually SQL statements saved in a text document. I like to find a
way to update those records in my table by using those statements. But I
don't know how to do this effienciently. Can you give me help on this?

Thanks,
Sarah

 

[tina]

hmm, perhaps i'm in over my head here, Sarah. why do you have the SQL saved
in a txt file, instead of saved - as either queryobjects, or in a VBA
module - in the database?

 

[sg]

Hi Tina,

I got this file from other resource which is generated in excel. But I need
to use this file to update my database which is big challenge for me.
If you know someone who can help me that would be great. Or you can give me
some other way around like VBA or database object which I don't know much.
Thanks,
Sarah

 

[Ken Snell [MVP]]

You just want to read the SQL statements from a text file and then run them?

Open one text file via VBA, and run the SQL statement that you read from
each line of that text file):

Dim intFile As Integer
Dim strLine As String
intFile = FreeFile()
Open "PathToFileName" For Input As #intFile
Do While EOF(intFile) = False
' reads entire line
Line Input #1, strLine
CurrentDb.Execute strLine, dbFailOnError
Loop
Close #intFile

--

Ken Snell
<MS ACCESS MVP>

 

[tina]

whew, thanks Ken! <grabs flotation ring, paddles back to shallow water>

 

[Ken Snell [MVP]]

whew, thanks Ken! <grabs flotation ring, paddles back to shallow water>

< chuckling > been there more than once.....

 

[sg]

Thanks Ken and Tina for your help. This is really helpful and I appreciate
the patience of both of you.
You might see me some time again in this group.

Merry Christmas and Happy New Year,
Sarah

 

[sg]

Hello Ken,

It works for me. Thanks so much.
Sarah

 

[Ken Snell [MVP]]

You're welcome. Good luck.