Field name migration conventions

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Field name migration conventions

Post by ChrisGreaves »

Using Excel Query Tables, I grabbed a set of labels from financial statements in Google Finance and used This Code to create a table with over 100 fields, each field name derived from my harvested label (attached).

By allowing Access 2000 to do its thing I see that I appear to have duplicate field names in the table, and worse, I don't know the algorithm for mapping from a string label to a usable Field name.

If anyone can point me to the rules that Access follows in deriving a field name via the CREATE TABLE I'd be grateful.

In the end, I want to locate the labels in any downloaded sheet and be able to pop the associated numeric values into the appropriate fields.
I would prefer to derive the field name by algorithm rather than by a table mapping, so that the VBA code remains nicely generic.

If all else fails I could write my own label-to-identifier mapping function.

Code: Select all

Sub BuildDbase()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Create an Access database on the fly
'Don't forget to set a reference to the Microsoft ActiveX Data Objects Library
' http://www.excelguru.ca/node/60
' Cloned Chris Greaves feb 4th 2010
    Dim dbConnectStr As String
    Dim Catalog As Object
    Dim cnt As ADODB.Connection
    Dim dbPath As String
    Dim strMDBFullname As String
    strMDBFullname = ThisWorkbook.Path & "\" & "Financial.mdb"
        dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBFullname & ";"
    If UX.blnFileExists(strMDBFullname) Then
    Else
        Call amlog(ThisWorkbook, "I cannot find your database " & strMDBFullname, True, True)
        'Create new database
        Set Catalog = CreateObject("ADOX.Catalog")
        Catalog.Create dbConnectStr
        Set Catalog = Nothing
    End If
        'Connect to database and insert a new table
        Set cnt = New ADODB.Connection
        With cnt
            .Open dbConnectStr
                     Dim strExecute As String
                     strExecute = "CREATE TABLE tblsample (" & strFieldnames & ")"
            .Execute strExecute
        End With
        Set cnt = Nothing
End Sub
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Field name migration conventions

Post by ChrisGreaves »

ChrisGreaves wrote:If all else fails I could write my own label-to-identifier mapping function.
(attached)
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Field name migration conventions

Post by HansV »

  1. The function strReplaceAll is not defined here, but I assume it's equivalent to the Replace function.
  2. Why use Trim if you're removing all spaces in the next instruction anyway?
  3. The function strRegulate concatenates the input string twice with a numeric code. Why twice?
Best wishes,
Hans

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Field name migration conventions

Post by ChrisGreaves »

Thanks for the feedback on my cheap'n'nasty.
Why use Trim if you're removing all spaces in the next instruction anyway?
Because I was coding from the rough specs given in the web page. I generally try to transcribe the logic as given.
Since the web page said "trim spaces from the ends", that's what I did.
The web page didn't tell me to replace spaces with underscores. I added that myself. I generally don't like spaces within identifiers.
Hence the two-pronged attack on spaces.
The function strRegulate concatenates the input string twice with a numeric code. Why twice?
Because I'm not as good a programmer as I think I am!
I wrote the initial code with a Long-to-CHR loop, then realised that it would suffice to dump the checksum tally as a formatted string.
That's what comes of trying to write a program on a computer instead of writing it with pencil and paper.
An expensive day out: Wallet and Grimace