Modify an Access table

[MWE]

I am running Access2000. I have a moderately larger table (50 fields, 3000 records) that has "grown" over time. I would like to reorganize this table; in particular, rename some fields, move fields around, etc. My access skills are pretty basic, so I can fumble my way through most of what I want to do, but suspect that there are easier ways to do things. For example:


I have not found a way to rename a field and have all associated macros, forms, etc., "know" what I have done. So, after renameing, say, field "A" in table "B", I have to manually go through macros, forms, etc., and update things. Is there an easier way? I have heard that there are tools available that help one do this type of thing.
I wish to "reorder" the above mentioned table. I have not found a way to "move" a field in the design view of the table. I know that I could build a query to copy everything to a new table in a new order, delete the old table, rename the new table, ... but that seems silly given the simplicity of what I want to do.
Thanks

[Norie]

1 Not too sure about this one. I know you can search through modules and write code to them. I think I had code to do something like that, I'll see if I can find it.

2 You can just drag/drop, cut/paste fields in table design mode.

[MWE]

1 Not too sure about this one. I know you can search through modules and write code to them. I think I had code to do something like that, I'll see if I can find it.Thanks


2 You can just drag/drop, cut/paste fields in table design mode.I have not been able to make drag/drop work. Cut/Paste retains field parameters (field size, formats, etc), but all data associated with that field is lost.

[Norie]

To drag and drop left click in the grey area next to the field name and then drag/drop.

Here's that code I was talking about. It doesn't really help you much as it doesn't actually change any code but it might give you some ideas.

I'm sure I've got something somewhere that changes code, I'll dig deeper.

Public Sub SearchProcs()
' this will search each form's module for a string
' and leave it open if it is found
Dim frm As Form, mdl As Module
Dim dbs As Database, ctr As Container, doc As Document
Dim X As Long
Dim strString As String
strString = InputBox("What text do you wish to search for?", "Search text")
If strString = "" Then Exit Sub
Set dbs = CurrentDb
Set ctr = dbs.Containers!Forms
For Each doc In ctr.Documents
If Not IsLoaded(doc.Name) Then DoCmd.OpenForm doc.Name, acDesign
Set frm = Forms(doc.Name)
Set mdl = frm.Module
Set mdl = frm.Module
If Not mdl.Find(strString, X, X, X, X) Then DoCmd.Close acForm, doc.Name
Next
End Sub

[MWE]

To drag and drop left click in the grey area next to the field name and then drag/drop.

Here's that code I was talking about. It doesn't really help you much as it doesn't actually change any code but it might give you some ideas.

I'm sure I've got something somewhere that changes code, I'll dig deeper.

Public Sub SearchProcs()
' this will search each form's module for a string
' and leave it open if it is found
Dim frm As Form, mdl As Module
Dim dbs As Database, ctr As Container, doc As Document
Dim X As Long
Dim strString As String
strString = InputBox("What text do you wish to search for?", "Search text")
If strString = "" Then Exit Sub
Set dbs = CurrentDb
Set ctr = dbs.Containers!Forms
For Each doc In ctr.Documents
If Not IsLoaded(doc.Name) Then DoCmd.OpenForm doc.Name, acDesign
Set frm = Forms(doc.Name)
Set mdl = frm.Module
Set mdl = frm.Module
If Not mdl.Find(strString, X, X, X, X) Then DoCmd.Close acForm, doc.Name
Next
End Sub


Thanks for you help.

I tried your code (above), but have a few problems:

Why is mdl set to frm.Module twice?
the IsLoaded procedure is not in any library I have referenced. I have not referenced anything special; do I need to?

[Norie]

:oops:

This is old code that I haven't really used for a while. It did work for what I was doing though.

1 I have no idea, shouldn't make a difference I think

2 Here's the code for IsLoaded.



Function IsLoaded(ByVal frmName As String) As Boolean
'returns true if a form is open in datasheet or form view

Dim frm As Form
For Each frm In Forms
IsLoaded = (frm.Name = frmName) And ((frm.CurrentView) <> 0)
If IsLoaded Then Exit Function
Next
End Function