MS Acess 2003 - VBA for Update SQL query?

Question

hey guys, could someone show me the simple update query through vb? I need to add new fields to the table (just 3) and add a couple text boxes on a form so that users can add some additional data relative to the record (which is already what this form is based on).

So the first form I have is a form that populates a list, when the user double clicks on a selection from that list, it opens a new form, so that the ID of the the table that is tied to this form that I need to add the these text boxes on (all the combo boxes and text boxes relative to one record are tied to the active form at this point, however there are all unbound. On a button click there is already vb that saves the information to the table). I did not create this however, it was built by someone who is not there anymore, and apparently is better than I at this stuff. My problem is that there is soooo much vb that checks for records, and various sql statements based on case, that I cannot decipher it to its simplest form.

So I was looking for a simple example of an update sql statement in vb so I can try to break this apart.

I need it to update the record based on the ID: sql WHERE RecordID = me.RecordID

I actually thought I knew how to do this based on examples, however every time I try, then try to run on button click, I get a run-time error of SYNTAX error, and the debug just highlights the db.execute(sql) part. So I tried to get the resulting immediate window of the sql statement, and it looks fine to me:

UPDATE tblMain 
   SET [Name] = "John Doe", 
       [DATE] = #9/30/2009#, 
       [TYPE] = "TypeA", 
 WHERE RecordID = 958;

Can I update a table without accounting for every field in the table (because this one has about 15 plus the new 3, so I am ignoring about 14 fields here, but I do not want to alter those anyway???

So as always, I appreciate the help yall!! Thanks!

EDIT:

Sorry I always forget this....I was actaully trying it DAO....

  Dim db as DAO.Database
  Dim sql as String
  set db = CurrentDb

etc

Answer 1

You were thaaat close! You have a simple extra comma after your last column. Get rid of it and it works fine.

UPDATE tblMain SET 
[Name] = "John Doe", 
[DATE] = #9/30/2009#, 
[TYPE] = "TypeA" 
WHERE RecordID = 958;

Yes, you can absolutely update only a few columns rather than all of them. That is a best practice, BTW.

Finally, It's considered bad practice to name your columns after reserved words like "Name" and "Date", but I know you inherited this.

Answer 2

You were wise to include Debug.Print sql in your code. bpayne already pointed out the extra comma in your SQL statement.

I want to point out another trouble shooting technique you may find useful to debug SQL statement problems.

Copy the statement from the Immediate Window, and paste it into the SQL View of a new query. Modify the query in the query designer until you can get it working, then revise your VBA code to generate a matching SQL statement.

In this case you might not have noticed the extra comma. However, you could create another new query and build the UPDATE statement from scratch in the query designer. After getting that one working, you could compare its SQL View to the failing query.