Run script with Access?

[franandanna]

Hi

I have 4310 update statements to run on an MS Access database. If I was
using Oracle, I'd just run sqlplus and do @updates.sql to run a text
file of the update statements.

Is there some equivalent with MS Access?

I have tried pasting 500 update statements at a time into the MS Access
SQL Query window, but they seem to get considered all together as a
single statement. (Maybe there's a statement delimiter, something like
";" but not ";") that would separate the statements so I could just do
chunks of 500 updates at a time.

Thanks for any help

 

[Douglas J Steele]

Unfortunately, there is no equivalent in Access.

You could run each script independently through VBA, but you can't string
them together in a query.

 

[Albert D.Kallal]

I have 4310 update statements to run on an MS Access database.


That sounds like a lot of updates. Are these in a text file?

You can simply open the text file..and execute the sql. is the sql one line
in the text file?

you certainly can open the text file..and execute each line....

However every single vendor on the planet have a slightly different format
for their sql......

However, the code could be



Sub ReadTextFile

Dim strFile As String
Dim intF As Integer
Dim strLineBuf As String

strFile = "c:\my data\MySql.txt"

intF = FreeFile()
Open strFile For Input As #intF

Do While EOF(intF) = False
Line Input #intF, strLineBuf

currentdb.Execute strLineBuf,dbfailonError
Loop
Close intF

End If

End Function

Certanly less then 10 lines of code....

I have tried pasting 500 update statements at a time into the MS Access
SQL Query window

yes..you have to paste one at a time....

However, again, you could write a wee bit of code to execute all of those
queries. However, if this is just a one time deal..then it don't make sense
to build 4000+ queries just to execute them...I would use the first
approach...

So, you do have a full coding system at your disposal.