Legal characters for table names

[furtivevole]

I am creating tables in an existing db whose names are derived dynamically, and include a string picked by the user from an external list whose contents I can't control. I've already discovered that the stop and hyphen characters cause the CREATE to fail (it's done with vbs in an .asp page, BTW), although hash seems to be OK. There are no spaces involved.

Can anyone say if other special characters are legal?

Many thanks

 

[willir]

linnet

Well, first peruse...
//

http://www.tek-tips.com/faqs.cfm?fid=2190

Next, although Access accepts various characters as valid, there are certain things that become a royal pain later on.

Graham's (LittleSmudge) article talks about not using spaces. The pain-in-the-rear is that if you have spaces because you then have to encapsulate all your field and table names within [square brackets].

Additionally, although the octophorp / number sign, "#" is legal, it is also used for dates.

My preference is to...
- use only alpha (a-z) and numeric (0-9) characters
- mix case for readability

MyTable
MyField
cmbMyComboBox

...or for less generic
tblContact
ContactFN (first name)

etc.
Richard

 

[furtivevole]

Hi Richard
Thanks, but not really addressing the issue (and I'd already checked the FAQ mentioned). What the original message was intended to convey was that (a) other than going for a complete re-design, I'm tied into using a string from a third-party source to generate the table names, and this occasionally contains the stop and hyphen characters (b) there are - thankfully - no spaces anyway.

So 'best practice' naming convention is currently a tad academic here. However if anyone has had experience of which special (non-alphanumeric or underscore) characters are legal in this situation, that would be really appreciated in determining how to approach the problem long-term.

Linnet

 

[GHolden]

Hi,

From Microsoft...

object-naming rules
A set of specific rules for naming Microsoft Access objects. In Microsoft Access, names can be up to 64 characters long and can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]). Note that you also can't use leading spaces or control characters (ASCII values 0 to 31). For information on Visual Basic naming conventions, search the Help index for "naming conventions."

Tips

Avoid including spaces in object names if you'll frequently refer to the objects in expressions or Visual Basic code.
Avoid using extremely long names because they are difficult to remember and refer to.

Hope this helps.


There are two ways to write error-free programs; only the third one works.

 

[furtivevole]

Hi
On further investigation, the problem lies with the vbscript CREATE TABLE statement which is throwing out (error 80040e14) attempts to dynamically create a table whose name includes any special chars other than (as far as I can ascertain) _ and #. So that, for instance, programmatically creating a table A^B (with carat) fails, whereas manually naming it seems OK.

Thanks for feedback however.

Linnet

 

[willir]

Would you be interested in stripping off special characters?

Although most of the time, I suspect things may be just fine, you may occasionally run into a name that creates problems. For example a "slash" "\" or "/" may creates problems. Ditto for a "decimal" "." .

Your comment

... occasionally contains the stop ...

Do you mean "period" or "decimal", ".", ASC 046 ?

A simple strip routine could be used to remove / or limit to the list of accepted characters.

Something very simple along the lines of...

Function StripString(strOriginal As String) As String
Dim strFinal As String, intX As Integer, strChar As String

strFinal = ""

For intX = 1 To Len(strOriginal)
    strChar = Mid(strOriginal, intX, 1)
    
    Select Case Asc(strChar)
    
        Case 48 To 57
            '0 to 9
            strFinal = strFinal & strChar
        
        Case 65 To 90
            'A to Z
            strFinal = strFinal & strChar
        
        Case 97 To 122
            'a to z
            strFinal = strFinal & strChar
    
    End Select
    
Next intX

If Len(strFinal) = 0 Then
    MsgBox "Zero length string", vbOKOnly
    StripString = strOriginal
Else
    StripString = strFinal
End If

End Function

Richard

 

[furtivevole]

Hi
Unfortunately these can't be stripped as they have to be reconstituted later. I've already substituted the period/stop with a hash. But it looks like another approach altogether is called for.

Thanks for your advice anyway.
Linnet

 

[willir]

You never mentioned that...

have to be reconstituted later

You can modify the StripString function to accomplish a substitution instead using the case.

One approach would be to add a number to the ASCII character code. Then add an indicator. Your use of the number sign / octophorp is a good one. Something along the lines of
[tt]
Character Ascii Code Ascii Code + 32 + Indicator
+ 043 -> 75 -> K #K
, 044 -> 76 -> L #L
- 045 -> 77 -> M #M
. 046 -> 78 -> N #N
/ 047 -> 79 -> O #O
[/tt]
What if you have a "#"? Just use "##"

You don't have to use +32, and regardless of the approach, you have to anticipate failure areas. In my example, ASCII code only goes up to 255, so anything above 223 will generate an error. Some ASCII code in not "visible".

Hint: You can use more than one translation rule - either use a different identifier, or look at the ASCII code make the neccessary adjustment.

To reconstuct, just reverse the process. Process the string looking for the indicator. If an indicator is found, translate if back to the original ascii character.

There are other approaches.
Richard