Updating fields and all references on a global scale 3

[bhoran]

I am working for a company that has change a large chunck of teminology eg from profit centre to cost centre etc etc.

If possible I would like to change all fields in a database and then all related references in queries, forms and reports as well code.

Does anyone have some sort of program or function that can do this well? If not I will leave it alone as it is not imperative but would be useful for the future.

 

[Steve101]

Check out the following thread: thread705-738341


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[bboffin]

As an alternative to the solutions in the other thread I use the built-in but hidden function SaveAsText to extract everything in the database to searchable text files.

I then use my favourite editor, EditPlus (insert your own preference here), to search for all occurrences of the field names I am changing and then simply make the changes it identifies.

I have written my own function to dump all the files out called SaveAllAsText which just requires the path to the folder where the files will be saved. I include this in all new projects:

Public Function SaveAllAsText(sPath)
Dim vObject, i, dbs
Set dbs = CurrentDb
For Each vObject In dbs.Containers("Forms").Documents
    SaveAsText acForm, vObject.Name, sPath & "\" & vObject.Name & ".xcf"
Next
For Each vObject In dbs.Containers("Reports").Documents
    SaveAsText acReport, vObject.Name, sPath & "\" & vObject.Name & ".xcr"
Next
For Each vObject In dbs.Containers("Modules").Documents
    SaveAsText acModule, vObject.Name, sPath & "\" & vObject.Name & ".xcm"
Next
For Each vObject In dbs.Containers("Scripts").Documents
    SaveAsText acMacro, vObject.Name, sPath & "\" & vObject.Name & ".xcs"
Next
For i = 0 To dbs.QueryDefs.Count - 1
    If Left(dbs.QueryDefs(i).Name, 4) <> &quot;~sq_&quot; Then
        Application.SaveAsText acQuery, dbs.QueryDefs(i).Name, sPath & &quot;\&quot; & dbs.QueryDefs(i).Name & &quot;.xcq&quot;
    End If
Next i
Debug.Print &quot;All done&quot;
End Function

I just call this function from the Immediate window supplying the path name.

You can also use this technique to identify differences between versions of the same database by extracting the text from each to a different folder and then using WinDiff or similar to detect the differences.

 

[Steve101]

bboffin,

Interesting approach. How do you then re-load the changed objects back into the database?


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[bboffin]

There's a corresponding hidden LoadFromText function that does the job but I tend to just use the results of the search or comparison to make the changes manually within Access itself especially if the changes are small.

These hidden functions were provided to allow Visual SourceSafe to do its job and if you use VSS, as I do, I haven't figured out how to use LoadFromText successfully in this situation without messing up your VSS project which is another reason why I tend not to use it.

 

[Steve101]

Useful to know. Have a star,
Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[tkecowboy]

So there is no real way to use sourcesafe seamlessly with Access like you do in VB or Interdev?

 

[bboffin]

So there is no real way to use sourcesafe seamlessly with Access like you do in VB or Interdev?

In my experience the Access interface to VSS is practically useless. If as I do you store your VSS databases on a server on the network is becomes almost impossible to use as it takes ages to check the status of the files whenever you open a database.

I now use VSS with Access in a much cruder way. I simply use VSS to manage different versions of the entire mdb file. VSS is quite happy handling binary files.

It's quick, simple and just as secure.

 

[bblekfeld]

bboffin,

I am typing SaveAllAsText(J:\Shared) in the immediate window and getting this error:

Compile Error
Expected List Seperator or )

Please help, this is exactly what I am lookin 4

 

[RoyVidar]

Try using quotes on the path:

[tt]SaveAllAsText("J:\Shared")[/tt]

Roy-Vidar

 

[bblekfeld]

I get a Compile Error: Expected variable or procedure, not module error if I type it with quotes as follows:


SaveAllAsText("J:\Shared")

 

[RoyVidar]

With reference to your other thread thread705-863129, I'm also playing with these methods (and the reverse, thread705-834817). I'm getting the above syntax, with quotes to work. Some questions, suggestions:

Have you placed the code in a module, not a forms/reports module (in VBE - Insert | Module)?

Are you able to compile your database - Debug | Compile (or Debug | "Compile and save all modules" (a97 - not sure of the actual spelling, cause in that version the menus are in my local language)?

You could also try alternate ways in the immidiate pane:
[tt]SaveAllAsText "J:\Shared"
x=SaveAllAsText("J:\Shared")[/tt]

Also to be sure, try saving at a folder on your local harddrive ("c:\test"), just for testing.

Roy-Vidar