Microsoft Access: Update SQL using VBA

In a Microsoft Access Database, I have a table where I added 2 new fields.  I now have over 20 sql files that I need to add the 2 new fields.  These are various SELECT sql queries.  I would like to create a VBA script to add the 2 new fields.  A script would be a great way to add fields w/out having to go into design-view.  Any ideas on how to do this?
Who is Participating?
Rey Obrero (Capricorn1)Commented:
how is your VBA coding ?

you have to alter the sql statement of the query using querydef


dim qd as dao.querydef, db as dao.database
set db=currentdb

set qd=db.querydefs("nameofquery")

'here you can extract the sql statement of the query

debug.print qd.sql

'to get the part before the "FROM Tablename"

dim strSql as string

strSql=left(qd.sql, instr(qd.sql, "FROM")-1)

'to get the part beginning with "FROM"

dim strFrom as string


' place your two fields in a string

dim strField as string

strField=", [Field1], [Field2]"

'now join the string

dim newSQL as string

newSQL=strSql &  strField & " " & strFrom

'apply the newSQL as the sql of the queyr


You can do a simple function and call it as many times as necessary...
This code assumes that if the query begins with 'Select' it is valid.
It adds the new field as the first field in the output.

Function AddFieldtoQuery(queryname, fieldname)
Dim strsql
strsql = currentdb.querydefs(queryname).sql
if left(strsql, 6) = "Select" then    'change the query
    strsql = "Select " & fieldname & ", " & mid(strsql, 7)
    'do nothing
end if
End Function


addfieldtoquery "query1", "fieldx"
vfinatoAuthor Commented:
this worked great. I forgot to state that I use the ADO access method. I was wondering if you knew how to access the database using ADO, instead of DAO. The rest of the project uses ADO to access the data and I would really like to stay in that format instead of using both access methods.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.