MS Acess 2003 - VBA to update SQL query?
Hey guys can anyone show me a simple update request via vb? I need to add new fields to the table (3 in total) and add a couple of text fields in the form so that users can add additional data regarding the record (which this form is already based on).
So the first form I have is a form that fills in a list, when the user double-clicks on the selected list from that list, it opens a new form, so the id of the table that is bound to this form, I need to add these text boxes (all combo boxes and text fields in relation to one record are anchored to the active form at this point, but they are not all connected. Pressing the button is already a vb, which saves information to the table). I didn't create this, however, it was built by someone that doesn't exist anymore and seems to be better than me at this. My problem is that there are so many vb that validate records and various SQL statements based on case that I cannot decode it in its simplest form.
So, I was looking for a simple example of sql update statement in vb, so I can try to break this down into parts.
I need to update a record based on ID: sql WHERE RecordID = me.RecordID
I actually thought I knew how to do this based on the examples, but every time I try and then try to trigger a button click, I get a SYNTAX runtime error and debug just highlights db.execute (sql). So I tried to get the sql statement window that appeared and it looks good:
UPDATE tblMain
SET [Name] = "John Doe",
[DATE] = #9/30/2009#,
[TYPE] = "TypeA",
WHERE RecordID = 958;
Can I update the table without considering every field in the table (because this has about 15 plus the new 3, so I'm ignoring about 14 fields here, but I don't want to change them at all?
So, as always, I appreciate the help yall !! Thank!
EDIT:
Sorry I always forget about it .... I was actaully tried this DAO ....
Dim db as DAO.Database
Dim sql as String
set db = CurrentDb
etc.
source to share
You were soooo close! You have a simple extra comma after the last column. Get rid of it and it works great.
UPDATE tblMain SET
[Name] = "John Doe",
[DATE] = #9/30/2009#,
[TYPE] = "TypeA"
WHERE RecordID = 958;
Yes, you can absolutely update only a few columns, not all of them. This is best practice BTW.
Finally, he thought it was a bad practice to name your columns after reserved words like "Name" and "Date", but I know you inherited that.
source to share
It would be smart to include Debug.Print sql in your code. bpayne has already specified an extra comma in your SQL statement.
I want to point out one more troubleshooting technique that you may find useful for debugging SQL query tasks.
Copy the statement from the Immediate window and paste it into the SQL View of the new query. Modify the query in the Query Designer until you get it working, and then modify the VBA code to generate the corresponding SQL query.
In this case, you may not have noticed the extra comma. However, you can create another new query and create an UPDATE statement from scratch in the query designer. Once you get it working, you can compare its SQL View to a failover query.
source to share