PaulS_III
asked on
Invalid Bracketing of name in Insert Into qeury
Hi all,
My question is similar to the one listed in:
https://www.experts-exchange.com/questions/20946351/INSERT-INTO-syntax-again.html
However, the solution there does is not accetpable to my specific situation, so I'm hoping you might be able to point me in the right direction.
I have a VB application that connects to a remote server to do various queries against the data on that server.
One of those queries goes against street names. If the user types in just a letter, say "m", then a listing of all the streets that begin with "m" should be displayed. One of the queries that is used to generate this list copies data from one database to another and is listed below:
insert into temp_streets in 'C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb' select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (((instr(namestrt,'M') = 1) or (instr(streetid,'M') = 1)))
Both databases reside the the current user's My Documents Folder. The problem is that when this query is fired off, I get the Invalid Bracketing of Name error. I have copied this query into Access and tried running it there, with the same error and it highlights insert into temp_streets in 'C:\Documents and Settings\P. L. Stoner\My
I believe the problem is caused by the dots (.) in the user's Name, P. L. Stoner. If I set up a different user, P L Stoner and run the application all works well.
My question is this, is there a way to properly format a user's name so that this query will work? I can't not go back to all of our users and have them change their system user name, that is simply unacceptable. I need a way to properly format so the query works as expected.
Thanks
Paul
My question is similar to the one listed in:
https://www.experts-exchange.com/questions/20946351/INSERT-INTO-syntax-again.html
However, the solution there does is not accetpable to my specific situation, so I'm hoping you might be able to point me in the right direction.
I have a VB application that connects to a remote server to do various queries against the data on that server.
One of those queries goes against street names. If the user types in just a letter, say "m", then a listing of all the streets that begin with "m" should be displayed. One of the queries that is used to generate this list copies data from one database to another and is listed below:
insert into temp_streets in 'C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
Both databases reside the the current user's My Documents Folder. The problem is that when this query is fired off, I get the Invalid Bracketing of Name error. I have copied this query into Access and tried running it there, with the same error and it highlights insert into temp_streets in 'C:\Documents and Settings\P. L. Stoner\My
I believe the problem is caused by the dots (.) in the user's Name, P. L. Stoner. If I set up a different user, P L Stoner and run the application all works well.
My question is this, is there a way to properly format a user's name so that this query will work? I can't not go back to all of our users and have them change their system user name, that is simply unacceptable. I need a way to properly format so the query works as expected.
Thanks
Paul
Try when you build the SQL string to insert doublequotes around the filename:
strSQL = "insert into temp_streets in '" & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb" & Chr(34) & "' select ..."
/gustav
strSQL = "insert into temp_streets in '" & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
/gustav
ASKER
Hello waltercook,
To answer your question, yes. My exact syntax is:
gcGlobals.Col.Temp_DB.exec ute("inser t into temp_streets in '" & gdaoTemp.name & "' select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (" & strStreet & ")")
the gcGlobales.Col.Temp_DB = C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\temp_col. mdb
the gdaoTemp.name = C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb
the strStreet = ((instr(namestrt,'M') = 1) or (instr(streetid,'M') = 1))
So in otherword, I want to insert records into the temp_streets table which is located in the database C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb from a table named temp_tblstret located in the database C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\temp_col. mdb which has the current connection at the time the query is executed
If I didn't say this before, then I apologize, but this works for a user directory that does not contain the dots.
Hello cactus_data,
I tried your suggestion of "insert into temp_streets in '" & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb" & Chr(34) & "' select ..." but it generated the same error.
I do appreciate all the responses thus far. Thank you very much
Paul
To answer your question, yes. My exact syntax is:
gcGlobals.Col.Temp_DB.exec
the gcGlobales.Col.Temp_DB = C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\temp_col.
the gdaoTemp.name = C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
the strStreet = ((instr(namestrt,'M') = 1) or (instr(streetid,'M') = 1))
So in otherword, I want to insert records into the temp_streets table which is located in the database C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
If I didn't say this before, then I apologize, but this works for a user directory that does not contain the dots.
Hello cactus_data,
I tried your suggestion of "insert into temp_streets in '" & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
I do appreciate all the responses thus far. Thank you very much
Paul
OK. Then remove the single quotes:
strSQL = "insert into temp_streets in " & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb" & Chr(34) & " select ..."
/gustav
strSQL = "insert into temp_streets in " & Chr(34) & "C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
/gustav
ASKER
Still no good there gustav.
I've tried hard coding the path and database name, putting square brackets, [ ] around the full path and database name, just around the path, even just around the P. L. Stoner portion. It would seem that nothing I do to that path works.
I'm still open to suggestions
Paul
I've tried hard coding the path and database name, putting square brackets, [ ] around the full path and database name, just around the path, even just around the P. L. Stoner portion. It would seem that nothing I do to that path works.
I'm still open to suggestions
Paul
It seems to be the dots that confuse the SQL parser.
To circumvent that, use the connect string syntax with surrounding brackets:
INSERT INTO
temp_streets
IN
"" [DATABASE=C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp. mdb;]
SELECT
... etc.
/gustav
To circumvent that, use the connect string syntax with surrounding brackets:
INSERT INTO
temp_streets
IN
"" [DATABASE=C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
SELECT
... etc.
/gustav
ASKER
Unless I am doing something completely wrong, your suggestion, gustav, still isn't correcting the problem.
Here is what I did:
gcGlobals.Col.Temp_DB.exec ute("inser t into temp_streets in [DATABASE=" & gdaoTemp.name & "] select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (" & strStreet & ")")
That, line still generated the same error. Then I tried:
gcGlobals.Col.Temp_DB.exec ute("inser t into temp_streets in " & chr(34) & "[DATABASE=" & gdaoTemp.name & "]" & chr(34) & " select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (" & strStreet & ")")
Still no good, so then I tried:
gcGlobals.Col.Temp_DB.exec ute("inser t into temp_streets in [DATABASE=" & chr(34) & gdaoTemp.name & chr(34) & "] select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (" & strStreet & ")")
Still no good.
Looks like I may have to increase the points :).
I am still open for suggestions.
Thanks
Here is what I did:
gcGlobals.Col.Temp_DB.exec
That, line still generated the same error. Then I tried:
gcGlobals.Col.Temp_DB.exec
Still no good, so then I tried:
gcGlobals.Col.Temp_DB.exec
Still no good.
Looks like I may have to increase the points :).
I am still open for suggestions.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gustav
Thank you. with a minor modification, this worked. Here is what I ended up with:
gcGlobals.Col.Temp_DB.exec ute("inser t into temp_streets in """"[DATABASE=" & gdaoTemp.name & "] select temp_tblstret.NAMESTRT as NAMESTRT, temp_tblstret.city as city, temp_tblstret.LORANGE as LORANGE, temp_tblstret.HIRANGE as HIRANGE, temp_tblstret.key as key, temp_tblstret.zip as zip, temp_tblstret.streetid as streetid from temp_tblstret WHERE (" & strStreet & ")")
This works splendidly. Now this is the first time I have ever seen this and I have been working with Access and VB for about 7 years. Could you please explain what we have done and why it works?
Thanks So much for your help and patience.
Paul
Thank you. with a minor modification, this worked. Here is what I ended up with:
gcGlobals.Col.Temp_DB.exec
This works splendidly. Now this is the first time I have ever seen this and I have been working with Access and VB for about 7 years. Could you please explain what we have done and why it works?
Thanks So much for your help and patience.
Paul
I have only used it once and that was most for fun. Like you I have never seen a reference to this syntax.
The purpose, I guess, is to allow for specifying a full connect string including any to Access SQL special chararacters which is done by wrapping the string into the "" [..;] construction.
I'll certainly make a note on it now; this is the first example I've seen - with the dot in the path - where it turned out as the only solution.
/gustav
The purpose, I guess, is to allow for specifying a full connect string including any to Access SQL special chararacters which is done by wrapping the string into the "" [..;] construction.
I'll certainly make a note on it now; this is the first example I've seen - with the dot in the path - where it turned out as the only solution.
/gustav
ASKER
Well thanks again for all your kind help and input.
I truly appreciate it.
Paul
I truly appreciate it.
Paul
Fine! You are welcome.
/gustav
/gustav
With this:
nsert into temp_streets in 'C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
are you trying to say that the table is in the database at 'C:\Documents and Settings\P. L. Stoner\My Documents\CCPR3I\ccprtemp.
Walt