We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

QueryDef - spaces in DB name

Halia
Halia asked
on
Medium Priority
487 Views
Last Modified: 2012-06-27
Hello,

I am trying to build  set of query def using this..

strSql = "SELECT MSysObjects.Name,  """ & DB & """ AS DB, MSysObjects.DateUpdate, MSysObjects.Type " & vbCrLf & _
"FROM MSysObjects IN '" & FullDBPath & " ' " & vbCrLf & _
"WHERE (((MSysObjects.Name) Not Like ""MSys*"" And (MSysObjects.Name) Not Like ""~*"") AND ((MSysObjects.Type) In (5,-32766)));"

Works fine unless I come across a DB that has spaces in the name.  How can I get around this?  Removing the spaces is, unfortunately, is not an option.

FullDPath is the path & DB Name.

Thanks,

Trish
Comment
Watch Question

Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
I believe you use [db name]
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
@aarontomosky is right and it's on this page:  http://office.microsoft.com/en-us/access-help/create-an-expression-HA001181384.aspx

For future reference, it appears that Access is the only database that allows space in database and table names.  If you have to export it to an SQL database, you will have to fix those names and the code that uses them.
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Thanks dave. I did this once myself in access and had to port to mssql / adp later on. I never used spaces again.

Author

Commented:
Thank YOU!!!!  Worked great!

As for the spaces, they where all there before me!  Drives me batty!

Thank again to all!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.