Here’s some code I have to generate a SQL statement
Dim sReturn As String
With clsRepLine
Select Case eType
Case aidbstatusdelete
sReturn = “DELETE * FROM tblRepLine WHERE ReplineID = “ & .RepLineID & “;”
Case aidbstatusadd
sReturn = “INSERT INTO tblRepLine (TxnLineID, ItemNumber, Quantity, SalesDollars) “
sReturn = sReturn & “VALUES (‘” & .TxnLineID & “‘,” & Nz(.ItemNumber) & “,” & .Quantity & “,” & .SalesDollars & “);”
Case aidbstatusupdate
sReturn = “UPDATE tblRepLine SET ItemNumber = “ & Nz(.ItemNumber) & “, “
sReturn = sReturn & “Quantity = “ & .Quantity & “, “
sReturn = sReturn & “SalesDollars = “ & .SalesDollars
sReturn = sReturn & ” WHERE RepLineID = “ & .RepLineID & “;”
End Select
End With
BuildRepSQL = sReturn
End Function
Here’s what a piece of that code looked like yesterday
sReturn = “UPDATE tblRepLine SET ItemNumber = “ & Nz(.ItemNumber) & “, “
sReturn = sReturn & “Quantity = “ & .Quantity & “, “
sReturn = sReturn & “SalesDollars = “ & .SalesDollars & “;”
The difference between those is that with the second one, 164 records in tblRepLine are made identical. I forgot to put the WHERE clause in there so it effected every record instead of just one. Whoops, thank goodness for backups. Luckily I have seven days of backups. Unfortunately, it seems I made this error more than seven days ago.
It only took me an hour or so to fix all the records. I’m glad it wasn’t 1,164 records. It was a development database, not production, so it’s going to get deleted someday anyway. But it’s nice to have a development database that actually has correct data in it.
I’ve been working between Excel and Access a lot recently. I feel like I’m recoding the same stuff over and over – setting up classes, filling the class, writing back to the database, etc. I really need a framework generating application that sets up the classes and the basic structure of the code for me. It’s one my ‘to code’ list.
Anyone reading your posting and who is interested in getting to grips with SQL, Excel and Access, might find the following link of interest.
“Using SQL in VBA” at
http://bygsoftware.com/Excel/SQL/UsingSql.html
The code creates an Access database, creates a table, inserts records into the table, amends the records, deletes the records, deletes the table and, finally, deletes the database.
In this one demonstration, you have the complete database lifecycle.
There are two downloads associated with it: one for DAO and the other for ADO.
I would make one comment about your code. SQL loves spaces and if you don’t have spaces in the right places the code won’t work. This caught me out a number of times until I started putting a space at the beginning of each line so the code looks like this and you can eyeball it for sense:
vtSql = “”
vtSql = vtSql & ” UPDATE LatestPrices”
vtSql = vtSql & ” SET ” & aa & “=” &
vtSql = vtSql & ” WHERE ” & bb & “=””” & xx & “”””
Cheers
Andy Wiggins
I prefer to code param queries in Access or stored procs in SQLServer and not to build in-line SQL in code.
Several years ago I had a class generator that I could point at a database table and it would build a class and collection that modelled the table. It wasn’t perfect as it didn’t understand autonumbers and sometimes got data types wrong, but it saved a lot of time and grunt-work. I lost it after I reformatted one time. The licensing no longer worked, and the developer had disappeared. Very frustrating.
Rob
Shouldn’t this be ‘Lessons in Access’? Your use of Nz() in VBA code is the giveaway :)
Dick, I don’t know how your code is being used but, on the face of it, it has SQL injection issues (http://en.wikipedia.org/wiki/SQL_injection). Better IMO to use stored procs or prepared statements e.g. use ADODB.Paramter objects to strongly-type the (String) values passed and return a ADODB.Command object, rather than returning dynamic SQL text.
Andy,
Thanks very much for the link, I’m playing with the ADO flavor. It has been most timely. I can’t find the secret to getting all my column names in as field names without a “Syntax Eror in Make Table” I get plenty of test tables made. The string is less than 256 characters. Any tips would be apreciated. It has been lots of fun to work with.
Brett
Totally FUN! Removing a hyphen solved the problem. However, some of the data has a double quote as part of the value, as well as some having a single quote. Anyone have a suggestion for a bullet-proof text indicator so I can wrap that around the cell value as it is read into the Access table?
Brett
Jamie: Good point. None of the parameters in my app are exposed to the user, but that’s not say they won’t be in some future application. It’s good practice to use Paramter objects, so I think I’ll start doing that.