Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

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?
0
vfinato
Asked:
vfinato
1 Solution
 
Rey Obrero (Capricorn1)Commented:
how is your VBA coding ?

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

example..

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

strFrom=mid(qd.sql,instr(qd.sql,"FROM"))


' 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


qd.sql=newSql


0
 
peter57rCommented:
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)
    currentdb.querydefs(queryname).sql=strsql
else
    'do nothing
end if
End Function

example..

addfieldtoquery "query1", "fieldx"
0
 
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now