change into MSysQueries

  • Thread starter Thread starter FTH
  • Start date Start date
F

FTH

Hi to all,

I'v changed the name of some of my fields.
I try to modified all the queries where this table/fields is involved.
I try to change that into MSysQueries but is impossible;
I also import this table into another DB but impossible again.

some good ideas?

best regards

Fréédric
 
Hi to all,

I'v changed the name of some of my fields.
I try to modified all the queries where this table/fields is involved.
I try to change that into MSysQueries but is impossible;
I also import this table into another DB but impossible again.

some good ideas?

best regards

Fréédric
Click to expand...

It is neither necessary nor appropriate nor safe to directly edit any of the
MSys tables. If you change the field name using the user interface (or any
other way) and compact the database, MSysQueries will take care of itself.
 
Instead of trying to edit the system tables, use VBA code to loop through
the queries collection. Retrieve the sql for each query, make your
replacements, and update the query's sql. But be VERY careful that you can't
accidentally replace other words that contain your changed fields in some
substring that should not be changed.

I usually use a loop like that to identify queries containing the old field
names, and then update each of those queries manually so I'm sure I'm not
accidentally destroying the sql.

Here's code to do the search:
Public Function pjsFindFieldUsageInQueries(strFieldname As String) As String
Dim qdf As DAO.QueryDef
Dim strOut As String

strOut = "Searching for the string: " & strFieldname

'Search the querydefs
strOut = strOut & vbCrLf & "Used in Queries: "
For Each qdf In CurrentDb.QueryDefs
If InStr(qdf.SQL, strFieldname) Then
strOut = strOut & vbCrLf & " " & qdf.Name
End If
Next
pjsFindFieldUsageInQueries = strOut
End Function
 
Back
Top