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: 458
  • 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 TomoskyTechnology ConsultantCommented:
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 TomoskyTechnology ConsultantCommented:
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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