Execute batch update

[Guest]

I want to update product prices. There are 200 odd products in the product
table, so I need to run 200 odd UPDATE statements - UPDATE products SET price
= 12.35 WHERE item = 'A103'. That sort of thing.
If this were any other relational database, I could simply create a text
file with all the individual SQL statements, and then RUN or READ this file.
The UPDATE statements would execute and I would be done.
But, this is Access. I really don't want to get involved in writing VB
code, and I really don't want to have to input 200 odd individual SQL
statements into a macro.
Is there some elegant way to do a batch update in Access?

 

[Dirk Goldgar]

In

I want to update product prices. There are 200 odd products in the
product table, so I need to run 200 odd UPDATE statements - UPDATE
products SET price = 12.35 WHERE item = 'A103'. That sort of thing.
If this were any other relational database, I could simply create a
text file with all the individual SQL statements, and then RUN or
READ this file. The UPDATE statements would execute and I would be
done.
But, this is Access. I really don't want to get involved in writing
VB code, and I really don't want to have to input 200 odd individual
SQL statements into a macro.
Is there some elegant way to do a batch update in Access?

There's no built-in batch SQL facility in Access.

If your updates are all of the form, "this item should have this price",
then you can make a simple table (either an access table or a linked
text file in CSV format) and execute a single update query along the
lines of:

UPDATE Products INNER JOIN NewPrices
ON Products.ItemID = NewPrices.ItemID
SET Products.Price = NewPrices.Price

There, you're done.

Or you could easily build a routine to read SQL statements from a file
(or table) and execute them one by one. It would probably take about 10
minutes. But if you don't want to get involved in writing VB code,
there's nothing built into Access.

 

[Douglas J. Steele]

If this is something you're going to need to do often, then really it
behooves you to write the VBA code to read in the SQL statements and run
them sequentially.

If you ensure that each SQL statement ends with a semi-colon and that there
are no other semi-colons in the file, the following should work:

Dim dbCurr As DAO.Database
Dim intFile As Integer
Dim lngLoop As Long
Dim strBuffer As Integer
Dim strFile As Integer
Dim varStatements As Variant

strFile = "C:\Folder\File.txt"
intFile = FreeFile()
Open strFile For Input As #intFile
strBuffer = Input(FileLen(strFile) - 1, #intFile)
Close #intFile
varStatements = Split(strBuffer, ";")
Set dbCurr = CurrentDB()
For lngLoop = LBound(varStatements) To UBound(varStatements)
dbCurr.Execute varStatements(lngLoop), dbFailOnError
Next lngLoop
Set dbCurr = Nothing

If there are semi-colons in the SQL statements themselves, chose a different
character as the delimiter between the individual statements, and use that
in the Split statement instead of the semi-colon

 

[Guest]

Thanks Dirk,
Could you give me a quick code example of how I could build a routine to
read SQL statements from a file, as you suggested.
Bill

 

[Dirk Goldgar]

In

Thanks Dirk,
Could you give me a quick code example of how I could build a routine
to read SQL statements from a file, as you suggested.

Doug Steele posted a simple example based on reading the file's complete
text and parsing out the SQL based on the semicolons (though I think he
made an error in his definitions of strBuffer and strFile). Here's an
alternate version of the same procedure that reads and executes a line
at a time:

'----- start of modified (and untested) code -----
Sub ExecuteBatchSQL()

On Error GoTo Err_Handler

Dim dbCurr As DAO.Database
Dim intFile As Integer
Dim strFile As String
Dim strStatement As String

strFile = "C:\Folder\File.txt" '<-- modify this to your file path
intFile = FreeFile()
Open strFile For Input As #intFile

Set dbCurr = CurrentDb

Do Until EOF(intFile)

Line Input #intFile, strStatement
strStatement = Trim(strStatement)

If Len(strStatement) > 0 Then
dbCurr.Execute strStatement, dbFailOnError
End If

Loop

Exit_Point:
On Error Resume Next
Close #intFile
Set dbCurr = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of modified (and untested) code -----

 

[Guest]

Thank you, Dirk
I just tried your INNER JOIN idea. Created a .CSV file, linked to Access
database, created Query with UPDATE statement (using my specific col names,
of course) and everything works up until the point that it has to do the
actual update. I'm getting error message 3616 "Udating data in a linked
table is not supported by this ISAM" Looks like Access doesn't want to allow
updates from a linked table.
Any way around this??
Bill

 

[Albert D. Kallal]

Thank you, Dirk
I just tried your INNER JOIN idea. Created a .CSV file, linked to Access
database, created Query with UPDATE statement (using my specific col
names,
of course) and everything works up until the point that it has to do the
actual update. I'm getting error message 3616 "Udating data in a linked
table is not supported by this ISAM" Looks like Access doesn't want to
allow
updates from a linked table.
Any way around this??

A csv file is just a text file, I not aware that you can actually update a
text file...especially via sql? Have you ever updated a simple text file via
sql?

While you might be able to come up with some type of updating system, for
practical purposes, I would import the file into a standard mdb file.
Further, since no indexing occurs on those text files, you not going to get
decent performance, and worse, nothing is available to enforce the data
types in a csv file anyway. The list of downfalls of trying to update and
work with a csv file via sql is really going to be difficult in practical
use.

 

[Guest]

Albert,
My goal is to update the Access.mdb from the text file, not the other way
around. But, yes, the info I have from MS Online is that Access will not
allow updates from a linked text file, for the reason you mentioned, no index.
I loaded the NewPrices into an .mdb file and the now UPDATE works fine.
Bill

 

[Guest]

Dirk,
Seems as if Access will not allow updates from a linked file. I loaded my
NewPrices into an .mdb file and your INNER JOIN statement now works fine.
I am working with products that are sold internationally, so we have to deal
with multiple currencies and multiple exchange rates per currency, as the
dollar continues to drip like a rock.
I don't want to clog the database with dozens of price tables for each
combination of currency/exchange, I would rather keep that data in separate
files and just update our current prices as necessary. Looks like this
should work fine.
Thanks,
Bill

 

[Dirk Goldgar]

In

Dirk,
Seems as if Access will not allow updates from a linked file.

I guess not; not a linked text file anyway. It must conclude that since
the text file is not updatable, nothing in the query results will be --
an incorrect conclusion, but what can you do?

I
loaded my NewPrices into an .mdb file and your INNER JOIN statement
now works fine.

I guess that's the best solution. I just tried an alternative to avoid
the join:

UPDATE Products
SET Products.Price =
(SELECT Price FROM NewPrices
WHERE NewPrices.ItemID = Products.ItemID)
WHERE Products.ItemID In (SELECT ItemID FROM NewPrices);

.... but it didn't like that, either, saying "Operation must use an
updateable query". I was hoping to avoid your having to import the text
file, but no such luck.

 

[Dirk Goldgar]

In

A csv file is just a text file, I not aware that you can actually
update a text file...especially via sql? Have you ever updated a
simple text file via sql?

No one is trying to update the text file. It is (IMO) a flaw in Jet
that it considers the entire query nonupdatable, even though the query
is trying to update a local Jet table.