What are invalid MDB table characters?

Thank you. Should I do a replace on each character or is there an easier way?

Hello Takamine334,

>>What are some (or all of) the invalid characters when creating a table in an MS Access
>>Database?

*None* of those characters are invalid for creating Access database objects.  You can create
tables and fields with *all* those characters in the name, if you wanted to.  That said, best
practice dictates that you use only letters and numbers in table and field names.  Even
underscores have, on occasion, proven problematic, according to Experts here who far
outrank me.

If you want a simple way to remove all the characters that conflict with best practice, add
this UDF:



Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
   
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
   
    ' If you use this function from Excel, you may substitute range references for all the arguments
   
    Dim RegX As Object
   
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
   
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
   
    Set RegX = Nothing
   
End Function




Then, refer to it in your code:

NewUserDB = InputBox("Please input a database name (ex. Portland Fans, Colleges, Seattle Musicians)", "New User Database")
NewUserDB = RegExpReplace(NewUserDB, "[^A-za-z0-9]")

Regards,

Patrick

Hi Patrick - For some reason, the code still left an open bracket "[" and caused an error when creating the DB.

It also allows me to enter in numbers which cause an error

Takamine334,

Please post back the code in its entirety.  Also, are you applying this test against the name
of the MDB file, against a table name, or both.  Your question title and text indicates table,
but the input prompt in your code indicates database.

Regards,

Patrick

Forced accept.

Computer101
EE Admin