massive update queries in MS ACCESS

RES AMBIENTE SRL 0 Reputation points
2024-01-13T03:25:32.17+00:00

Now, here is my steps: 1- open query, 2- SQL view 3- update SQL statment (trought WordPad) 4- update SQL 5- save query updated I would like to avoid the above sequence because it takes a big time and this activity is very boring. In other words i wattould like to reach to the following: 6- export, in a massive way, a set of queries (i.e. format .txt) 7- to edit the code (i.e. substitute the name of the coordinates whom is the same over all the set) 8- import the code updated substituting the original queries)

Access
Access
A family of Microsoft relational database management systems designed for ease of use.
333 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Peter Doering 170 Reputation points MVP
    2024-02-09T00:22:58.24+00:00

    You can write a procedure in VBA. Important objects for you to use are DAO.Database, DAO.QueryDef, the SQL method and maybe Replace(). Example:

    Dim Db As DAO.Database Dim Qdf As DAO.QueryDef Dim strSQL As String Set Db = CurrentDb For Each Qdf In Db.QueryDefs Qdf.SQL = Replace(Qdf.SQL, "Old Text", "New Text") Next Qdf Set Qdf = Nothing Set Db = Nothing Be careful with tools like that, as the syntax of your SQL statement fully depends on your replace statement. Access will throw errors in case the syntax isn't correct.

  2. RES AMBIENTE SRL 0 Reputation points
    2024-02-16T14:32:59.59+00:00

    Problem: the variable ExistinfQr.Name, strangerly vales: ~sq_rCordignano_ELENCO_ASTE in which the initial substring ~sq_r it's impossible to understand where it comes. The correct value should be (only) Cordignano_ELENCO_ASTE. Have you got some idea about? Thanks in advance Achille

    0 comments