• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

QueryDef - spaces in DB name

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
0
Halia
Asked:
Halia
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
I believe you use [db name]
0
 
HainKurtSr. System AnalystCommented:
I dont think DB is the issue, it is surrounded by "
I removed extra space after FullDBPath

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)));"

you can also use [..]

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)));"



0
 
Dave BaldwinFixer of ProblemsCommented:
@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.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Thanks dave. I did this once myself in access and had to port to mssql / adp later on. I never used spaces again.
0
 
HaliaAuthor Commented:
Thank YOU!!!!  Worked great!

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

Thank again to all!
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: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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