Using Querydefs


With the code below i want to change a certain field in a query, but only the query's in the Object query and not in the e.g. rowsource of a Combobox.
How do i have to change the code?


Public Sub changeSQL()
    Dim db As DAO.Database
    Dim qdfs As DAO.QueryDefs
    Dim qdf As DAO.QueryDef
    Dim strsql As String, I As Integer
    Dim arrT
    Set db = CurrentDb
    Set qdfs = db.QueryDefs
    arrT = Array("N", "F", "D", "E", "S", "I")
    For Each qdf In qdfs
       strsql = db.QueryDefs(qdf.Name).SQL
       For I = 0 To 5
           strsql = Replace(strsql, "Omschrijving" & arrT(I), "Omschrijving" & taal)
       Next I
       db.QueryDefs(qdf.Name).SQL = strsql
    Next qdf
    Set qdf = Nothing
    Set qdfs = Nothing
    Set db = Nothing
End Sub

Open in new window

Who is Participating?
Helen FeddemaCommented:
That example was for different versions of the values in the fields, though.  To get different field names, perhaps you could make six different aliases for each field that needs a language-specific name, like FieldName As GermanFieldName, FieldName As FrenchFieldName, etc., and display the appropriate one depending on the country (or some other identifier).
Helen FeddemaCommented:
It is not entirely clear what you are trying to do here.  Are you trying to change the names of fields in a table or query?  It might be better to work with the DAO Fields collection of a TableDef.  Here is some code for that purpose:
Public Sub FieldsTest()

   Dim dbs As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Set dbs = CurrentDb
   Set tdf = dbs.TableDefs("tblStores")
   Set fld = tdf.Fields("StoreName")
   fld.Name = "NewStoreName"
End Sub

Open in new window

Helen FeddemaCommented:
It doesn't work with QueryDefs, though.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sonmicAuthor Commented:

In my tables are descriptions of products in 6 different languages. Each user, connected to the same Backend-DB, need to use each produkt in his own language.
When i have a report with a underlying query i use for the discription Descrip:DescriptionF (french). When the user select another languag, the whole DB (form, reports, different tables) are changed into his selected language. Ii want to change the query into Descrip:DescriptionG (german). Perhaps there's another and easier way.
Helen FeddemaCommented:
In that case, you don't need to change the field names at all -- just alias them with the different language versions of the field name, in a query, using the Switch function.  You will need some field that tells you which language to use for each record.
Helen FeddemaCommented:
Here is a rather silly example:

SELECT tblContacts.StateOrProvince, Switch([StateOrProvince]="NY","Blue",[StateOrProvince]="OH","Red",[StateOrProvince]="IN","Purple",[StateOrProvince]="VT","Green",[StateOrProvince]="MA","Orange",[StateOrProvince]="RI","Black",[StateOrProvince]="WV","Pink",[StateOrProvince]="CA","Yellow") AS StateColor
FROM tblContacts;
Helen FeddemaCommented:
Or make six versions of each query, one for each language, using the appropriate field name aliases.
Helen FeddemaCommented:
If you are getting the country from a report field, perhaps when opening the report from a menu, you could check that field, and assign the appropriate record source to the report before opening it.
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.