• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • 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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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