Invalid Bracketing error (1 Viewer)

Hi All,

I am trying to move a table to another database by modify the below query

SELECT DISTINCT TPR_BOM_HEADERS_CONFIG.*, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_NUMBER AS BMI_Number, TPR_ITEM_ATTRIBUTES_CONFIG.LONG_DESCRIPTION AS BMILongDesc, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_DESC AS BMIShortDesc INTO tbl_SMART_BOM_Header_BMI
FROM TPR_BOM_HEADERS_CONFIG INNER JOIN TPR_ITEM_ATTRIBUTES_CONFIG ON TPR_BOM_HEADERS_CONFIG.ASSEMBLY_ITEM_ID = TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_ID
WHERE (((TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_TYPE)="BMI") AND ((TPR_BOM_HEADERS_CONFIG.SMI_BOM_TYPE)="SMI") AND ((TPR_BOM_HEADERS_CONFIG.BOM_PARTITION_KEY)=0));

to below mentioned query by including the URL of new database

SELECT DISTINCT TPR_BOM_HEADERS_CONFIG.*, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_NUMBER AS BMI_Number, TPR_ITEM_ATTRIBUTES_CONFIG.LONG_DESCRIPTION AS BMILongDesc, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_DESC AS BMIShortDesc INTO tbl_SMART_BOM_Header_BMI IN '\\perseco.com
\files\groups\SCDM\PROD\SMART\DATABASES\SMART_MIR_Extended.accdb' FROM TPR_BOM_HEADERS_CONFIG INNER JOIN TPR_ITEM_ATTRIBUTES_CONFIG ON TPR_BOM_HEADERS_CONFIG.ASSEMBLY_ITEM_ID = TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_ID
WHERE (((TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_TYPE)="BMI") AND ((TPR_BOM_HEADERS_CONFIG.SMI_BOM_TYPE)="SMI") AND ((TPR_BOM_HEADERS_CONFIG.BOM_PARTITION_KEY)=0));

But I am getting the Invalid Bracketing error and I am not sure why as all my parenthesis, brackets and single quotes are properly closed. Please advise ASAP.
Historically I never got such error whenever I tried to move tables created using Make Table query.

Replace ALL the double quotes with single quotes e.g. "BMI" to 'BMI'

if you use queries instead of sql code, you wont have this error.

ridders said:

Replace ALL the double quotes with single quotes e.g. "BMI" to 'BMI'

Click to expand...

But there are other queries which are working with double quotes. Could you elaborate.

Below query is working perfectly with double quotes. Words within double quotes are text criteria to pull specific records. Not able to get the reason. Could you elaborate.

SELECT DISTINCT TPR_BOM_COMPONENTS.*, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_NUMBER AS BMI_Number, TPR_ITEM_ATTRIBUTES_CONFIG.LONG_DESCRIPTION AS BMILongDesc, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_DESC AS BMIShortDesc INTO tbl_SMART_BOM_Component_BMI
FROM TPR_BOM_COMPONENTS INNER JOIN TPR_ITEM_ATTRIBUTES_CONFIG ON TPR_BOM_COMPONENTS.COMPONENT_ITEM_ID = TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_ID
WHERE (((TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_TYPE)="BMI") AND ((TPR_BOM_COMPONENTS.BOM_PARTITION_KEY)=0));


SELECT DISTINCT TPR_BOM_COMPONENTS.*, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_NUMBER AS BMI_Number, TPR_ITEM_ATTRIBUTES_CONFIG.LONG_DESCRIPTION AS BMILongDesc, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_DESC AS BMIShortDesc INTO tbl_SMART_BOM_Component_BMI IN '\\perseco.com
\files\groups\SCDM\PROD\SMART\DATABASES\SMART_MIR_Extended.accdb' FROM TPR_BOM_COMPONENTS INNER JOIN TPR_ITEM_ATTRIBUTES_CONFIG ON TPR_BOM_COMPONENTS.COMPONENT_ITEM_ID = TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_ID
WHERE (((TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_TYPE)="BMI") AND ((TPR_BOM_COMPONENTS.BOM_PARTITION_KEY)=0));

Access uses " as an escape character so will in certain circumstances interpret a double quote as the end of the SQL statement so in this case there were an unequal number of left/right brackets beforehand.

Ranman is correct in saying double quotes work in a query .... and so do single quotes. However if you are happy with SQL stick with it as you can do several things in SQL that can't be done in the query designer.

Replaced " double quotes with ' single quotes but no go. Same error. Once you click "ok" on error message. It highlights below text of query with black background.

SELECT DISTINCT TPR_BOM_HEADERS_CONFIG.*, TPR_ITEM_ATTRIBUTES_CONFIG.ITEM_NUMB

Check your table & field names in the highlighted section.
Then check the rest of the statement
Do you have a space after the first comma in your SQL statement?

Single quotes are the T-SQL standard delimiter but double quotes are the Access SQL delimiter. However, Access SQL is more flexible in that you can use single quotes to enclose a string that contains double quotes or double quotes to enclose a string that includes single quotes.

So, is the issue that you can't use UNC naming to reference the foreign database?

Is there some reason that you don't want to link to the other database?

You can link one FE to multiple BE's.