Invalid bracketing

[sumdumgai]

Hello again. I'm trying to use VBA to run SQL that will run a 'Make Table' query and create a table in another database. Unfortunately, both the database path and database name if the target database contain spaces. This is giving me a headache trying to come up with a syntax that will not give the 'Invalid bracketing' error. I've tried brackets, apostrophes, double quotes, etc. and cannot find the answer.

If the path to the target database is this "c:\target database path" and the name of the target database is "target database.accdb" and the target table name is "new tab", then what would be the proper syntax?

e.g., Select F1, F2, F3 into [new tab] In 'c:\target database path.target database.accdb'

Thanks.

 

[theDBguy]

Hi. Just a guess, but maybe something like?

SELECT F1, F2, F3 INTO [new tab] FROM [c:\target database path].[target database.accdb]

 

[sumdumgai]

Thanks. Found the problem. The database name has an embedded period (.) as in dbname Rel. 10-1-10.accdb

Any workaround other than renaming all the databases?

 

[theDBguy]

Thanks. Found the problem. The database name has an embedded period (.) as in dbname Rel. 10-1-10.accdb

Any workaround other than renaming all the databases?

Hi. Are you saying you're still looking for a solution to the original problem? None of the usual ways or the suggested approach worked?

 

[Deleted member 147267]

I tried this with Test.It.accdb and it worked. Must be something wrong with your sql? Did you post the exact sql you're trying or was that pseudo sql?

 

[vba_php]

The database name has an embedded period (.) as in dbname Rel. 10-1-10.accdb

Any workaround other than renaming all the databases?

why would that be a problem, sumdumgai? Micron basically said it isn't. Are you getting an error when you try to run it? I guess it's possible that VBA would recognize that first period as the qualifier for the file extension.

 

[June7]

Period is not a reserved character and is normally not issue, nor is a space. However, I found that a period immediately followed by space does error. Only recourse I can see is to rename files.

There is no FROM clause in OP example.

Select F1, F2, F3 INTO [new tab] IN 'c:\target database path.target database.accdb' FROM tablename

 

[sumdumgai]

This works: 'S:\Clients\Test\Production Database\Reports Database (Rel 11-08-20).accdb'
This does not: 'S:\Clients\Test\Production Database\Reports Database (Rel. 11-08-20).accdb'

Removing the period after 'Rel' fixes the Invalid Bracketing error. We could change the names of all of the databases to remove the embedded periods but I was just hoping there was some VBA/SQL syntax to avoid that.

Thanks.

 

[theDBguy]

This works: 'S:\Clients\Test\Production Database\Reports Database (Rel 11-08-20).accdb'
This does not: 'S:\Clients\Test\Production Database\Reports Database (Rel. 11-08-20).accdb'

Removing the period after 'Rel' fixes the Invalid Bracketing error. We could change the names of all of the databases to remove the embedded periods but I was just hoping there was some VBA/SQL syntax to avoid that.

Thanks.

Hi. Just to confirm it, did you try my "guess" and found it didn't work either? I would just like to know for my own benefit. Thanks!

 

[vba_php]

Hi. Just to confirm it, did you try my "guess" and found it didn't work either? I would just like to know for my own benefit. Thanks!

I would tend to think it would work, guy. because that's the same syntax that is used in this situation inside of a single access file anyway.

 

[June7]

Right, remove either period or space and should work. I suggest removing period. I would avoid spaces as well but that's just personal preference.

 

[theDBguy]

I would tend to think it would work, guy. because that's the same syntax that is used in this situation inside of a single access file anyway.

Hi. Thanks. That would be my guess too, but I just wanted some confirmation from the OP, because I haven't tried it with a dot in the file's name. Cheers!

 

[vba_php]

Hi. Thanks. That would be my guess too, but I just wanted some confirmation from the OP, because I haven't tried it with a dot in the file's name. Cheers!

well, you know, being as secretive as you are, I'm sure you could be clever and run a couple of tests or something to find out for yourself.

 

[GinaWhipp]

Hmm, never had a problem using a period but I do not ever have any spaces in the file name. I may have to play around with this myself.

 

[vba_php]

I do not ever have any spaces in the file name.

that's always bad practice, Gina. I know that the pros have been saying that for years, regardless of language and platform. That's probably why all the SEO addresses on the internet look like:

https://www.domain.com/this-is-a-news-story-from-cnn

 

[GinaWhipp]

that's always bad practice, Gina. I know that the pros have been saying that for years, regardless of language and platform. That's probably why all the SEO addresses on the internet look like:

https://www.domain.com/this-is-a-news-story-from-cnn

Moved my reply out of this thread so as not to confuse the issue at hand.

 

[June7]

Period itself is not an issue. Period followed by space is an issue.

 

[GinaWhipp]

Period itself is not an issue. Period followed by space is an issue.

Yep, just got thru confirming that. And I have learned my new thing for the day!

 

[Deleted member 147267]

I've tried everything I can think of to get around it - square brackets, concatenating, ASCII characters, encapsulating path in double quotes and a mix of those. Have to say that after all these years I still think that those who put spaces, special characters and such in file paths need 40 lashes with a wet noodle, and the only fixes are fix the file name or do it all manually.

 

[vba_php]

I still think that those who put spaces, special characters and such in file paths

I'm sure many people could learn from the windows operating system (and perhaps linux too?). windows doesn't allow special chars in file names. or at least, most special chars.