Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

What are invalid MDB table characters?

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

    NewUserDB = InputBox("Please input a database name (ex. Portland Fans, Colleges, Seattle Musicians)", "New User Database")
    NewUserDB = Replace(NewUserDB, "'", "''")
    NewUserDB = Replace(NewUserDB, ",", "")
    NewUserDB = Replace(NewUserDB, " ", "_")
    NewUserDB = Replace(NewUserDB, "!", "")
    NewUserDB = Replace(NewUserDB, "@", "")
    NewUserDB = Replace(NewUserDB, "#", "")
   
    NewUserDB = Trim(NewUserDB)
0
Takamine334
Asked:
Takamine334
1 Solution
 
jpaulinoCommented:
You get that informatin in Microsoft Support Page http://support.microsoft.com/kb/826763
0
 
Takamine334Author Commented:
Thank you. Should I do a replace on each character or is there an easier way?
0
 
Patrick MatthewsCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Takamine334Author Commented:
Hi Patrick - For some reason, the code still left an open bracket "[" and caused an error when creating the DB.
0
 
Takamine334Author Commented:
It also allows me to enter in numbers which cause an error
0
 
Patrick MatthewsCommented:
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
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now