Changing the variable names, programmatically

[Mr. Malloc]

I need to know how to change the Access variable names programmatically.

I have a large Access database, with hundreds of variables that need to be shortened to 8 characters since they are to be imported into a SIR database, which does not allow variable names longer than 8 characters.

Because the variables have to be unique and yet still identifiable, I have to write code that will do abreviations. For instance:

Neglect Aphasia Conversion Baseline
Neglect Aphasia Conversion Drug
Neglect Aphasia Conversion Fluency
Neglect Aphasia Conversion Composite

will become:

N A CV BA
N A CV DR
N A CV FL
N A CV COM

Would something like this be done in a macro?

And once the variable names are abreviated, how would they be put back in the database table?

 

[Mile-O]

By variables, do you actually mean fields?

 

[Pat Hartman]

The SIR database will probably not allow spaces or special characters in the column names so your example names will be invalid also.

You can't do this with a macro, you will need to write VBA code and lots of it. I am going to post a function that I built that breaks company names apart and then compares each "word" against a list of abbreviations so the abbreviation can be expanded. The code could have worked the opposite way. It could have looked for the full word and created an abbreviation. But it should give you an idea of what you are in for. The code sample was used to help me with a conversion. I needed to convert a dozen company files into a single company file and there were lots of inconsistancies with company names in the various tables. So, the object of the code was to standardize the company names so I could find the common company records among the 12 files and merge them together for the final file. Obviously, you will not be able to use the code as it exists but it will show you how to separate the names into individual words, how to convert a word to something else, and then how to concatenate the individual words back together again. This was my first reall attempt at VBA and it was written over 10 years ago so no snickers please.

You will need to add code that opens the tabledefs collection and run this parsing routine for each field of each table. You can update the collection as you go along. I don't know whether updating the collection via code causes Name AutoCorrect to activate. If it doesn't, you'll need to manually change all your queries, forms, reports, etc.

Sub ParseName(strInName As String, strTemp() As String)
    Dim intSpacePos     As Integer
    Dim I               As Integer
    ReDim LastWord(1 To 3) As String
    ReDim strTemp(0) As String
    ReDim ExpandedName(1 To 3) As String
    Dim CheckWord       As String
    Dim FoundCount      As Integer

    intSpacePos = InStr(strInName, " ")

    Do Until intSpacePos = 0
                                                    ' add empty element to end of array
        ReDim Preserve strTemp(UBound(strTemp) + 1)
                                                    ' fill element with found word+space
        strTemp(UBound(strTemp)) = Trim$(Left$(strInName, intSpacePos - 1)) & " "
                                                    ' remove found word from input string
        strInName = LTrim$(Right$(strInName, Len(strInName) - intSpacePos))
                                                    ' look for next space
        intSpacePos = InStr(strInName, " ")
                                                    ' if company name is null filled this gets last word
        If intSpacePos = 0 Then
                                                    ' this stops extra word when company name is not null filled
            If Len(strInName) > 0 Then

                ReDim Preserve strTemp(UBound(strTemp) + 1)
                strTemp(UBound(strTemp)) = Trim$(Left$(strInName, Len(strInName))) & " "
                strInName = LTrim$(Right$(strInName, Len(strInName) - intSpacePos))
            End If
        End If

    Loop

                                                    ' look at each word in array and expand if in list
    For I = 1 To (UBound(strTemp))
        If strTemp(I) = "" Then
            Exit For
        End If
        Select Case strTemp(I)
                Case "ACC "
                    strTemp(I) = "ACCIDENT "
                Case "AGRIC "
                    strTemp(I) = "AGRICULTURAL "
                Case "AMER ", "AM "
                    strTemp(I) = "AMERICAN "
                Case "AND "
                    strTemp(I) = "& "
                Case "ASSN ", "ASN"
                    strTemp(I) = "ASSOCIATION "
                Case "AUTOMOBILE "
                    strTemp(I) = "AUTO "
                Case "BUR "
                    strTemp(I) = "BUREAU "
                Case "CALIF "
                    strTemp(I) = "CALIFORNIA "
                Case "CAS "
                    strTemp(I) = "CASUALTY "
                Case "CO ", "CO. "
                    strTemp(I) = "COMPANY "
                Case "COS "
                    strTemp(I) = "COMPANIES "
                Case "COMP "
                    strTemp(I) = "COMPENSATION "
                Case "CONSTIT "
                    strTemp(I) = "CONSTITUTION "
                Case "CONTRIB "
                    strTemp(I) = "CONTRIBUTIONSHIP "
                Case "CORP ", "CORP. "
                    strTemp(I) = "CORPORATION "
                Case "FID "
                    strTemp(I) = "FIDELITY "
                Case "FIN "
                    strTemp(I) = "FINANCIAL "
                Case "GEN ", "GENL "
                    strTemp(I) = "GENERAL "
                Case "GR ", "GRP "
                    strTemp(I) = "GROUP "
                Case "GUAR "
                    strTemp(I) = "GUARANTY "
                Case "PROP "
                    strTemp(I) = "PROPERTY "
                Case "INC "
                    strTemp(I) = "INCORPORATED "
                Case "INDEM "
                    strTemp(I) = "INDEMNITY "
                Case "INS ", "INS. ", "IN "
                    strTemp(I) = "INSURANCE "
                Case "INTL ", "INTERNATL "
                    strTemp(I) = "INTERNATIONAL "
                Case "LIAB "
                    strTemp(I) = "LIABILITY "
                Case "L&A "
                    strTemp(I) = "LIFE & ACCIDENT "
                Case "MAR "
                    strTemp(I) = "MARINE "
                Case "MICH "
                    strTemp(I) = "MICHIGAN "
                Case "MUT "
                    strTemp(I) = "MUTUAL "
                Case "NAT ", "NATL "
                    strTemp(I) = "NATIONAL "
                Case "PAC "
                    strTemp(I) = "PACIFIC "
                Case "PENN "
                    strTemp(I) = "PENNSYLVANIA "
                Case "PROP "
                    strTemp(I) = "PROPERTY "
                Case "PHILA "
                    strTemp(I) = "PHILADELPHIA "
                Case "P&C "
                    strTemp(I) = "PROPERTY & CASUALTY "
                Case "RE ", "REINS ", "REIN "
                    strTemp(I) = "REINSURANCE "
                Case "ST. ", "SAINT"
                    strTemp(I) = "ST "
                Case "TRANSAM "
                    strTemp(I) = "TRANSAMERICA "
                Case "UNIV "
                    strTemp(I) = "UNIVERSAL "
                Case "US "
                    strTemp(I) = "U.S. "
        End Select
    Next I
        

    '''''build company name
                                                    ' handle case where input string was empty
    If UBound(strTemp) <> 0 Then
        strInName = strTemp(1)
    End If
                                                    ' put words back into a string
    For I = 2 To (UBound(strTemp))
        strInName = strInName & strTemp(I)
    Next I

    '''''save max index
    I = (UBound(strTemp))


'Check to see if last word in any name is "Company",
'If it is, then all names must end with "Company"
        CheckWord = "Company "
        FoundCount = 0
        For I = 1 To 3
            GoSub CheckLastWord
        Next I

        If FoundCount = 1 Or FoundCount = 2 Then
            For I = 1 To 3
                GoSub AppendLastWord
            Next I
        End If

Exit Sub

CheckLastWord:
    If LastWord(I) = CheckWord Then
        FoundCount = FoundCount + 1
    End If
Return

AppendLastWord:
    If LastWord(I) <> CheckWord Then
        ExpandedName(I) = Trim(ExpandedName(I)) & " " & CheckWord
    End If
Return

End Sub

 

[The_Doc_Man]

It is possible to change tables, fields, queries, forms, reports, and even code references via VBA code. 'tain't for the faint of heart. Further, if you have any references in a Macro, that is a real "no joy" situation 'cause the storage format for a Macro is nigh unto intractable.

Before you go writing your own code, I believe there exists some solutions that you could search for on the web. Topic might be "Access" + "global name changes." The cost of this solution as a "roll your own" would not be pretty. This is a definite case where buy/make is a significant and highly important decision.

I've done the first half of this myself by writing a cross-referencer that can find anything in a tabledef, fielddef, querydef, form, report, or module and make a list of where it is. The result can be quite overwhelming for a reasonable amount of code for a twenty-thirty table DB with fifteen to forty fields per table.

But I can neither sell this code nor post it, so don't ask. Contractual issues, I'm afraid.

The point I wanted to make is, you can see everything you need to see in a VBA operation by enumerating your way through the collections of objects. The down side of this process is that you must be VERY comfortable with text parsing 'cause that's all you can do for the code segments. The other stuff - tabledefs, fielddefs, querydefs - are easy 'cause the whole item name is "monolithic" in that when you ask for the object.name property, you get the whole thing. In forms and reports, you get a mixed bag of object names and strings. But in class modules and general modules, you get text strings and that's all you get.