Apparently, all of the SQL sins covered by the ODBC driver with the '*' wildcards are laid bare when you start trying to expand your SQL command with explicit field statements.

Before I got Ted's message, I tried to delimit fieldnames with brackets like '[abc]' and I got a new ODBC error indicating I had created a circular reference. After I got Ted's email I tried again with the backtick '`abc`' and again got the circular reference error.

On a whim, I decided to add an alias to the table (ie: sheet) name and then update the field names to the full bracketed column name notation '[xs].[abc]' along with the table/sheet alias in the query and incredibly it worked! Of course I was still in the development environment at the time, so I quickly compiled the new code to an exe and it worked there too! The relevant section of the query with the troublesome field now looks like this: "..., [xs].[3rdPartySickPay] AS xrdPartySickPay,..."

Upon running the code with another Excel spreadsheet, I got a new error that was related to the fact that the column name aliases were the same as the original fieldnames unless the first character was a digit. Upon review, I just changed the program so that an underscore is added to the original column name which eliminates the need to check for first digits or keywords or uniqueness. Additional checks can be added going forward, but this is the fastest solution that covers multiple potential issues. Since these column headers are only used during the import process and are discarded after control is returned to the original program, the column name is irrelevant.

So I've now have the code working to make Craig Boyd's AppendXLSX.prg work with column headers that start with numeric values instead of alpha characters. I'm sure my code could use some refining, but my modifications are now working in both development and compiled versions.

If anyone is interested in getting a copy of the modified version of APPENDXLSX.prg, you can get it at https://pastebin.com/wxDnwFw2. Changes start on line 110 and go through line 170. Or you can just email me at paul AT tpcqpc DOT com.

Paul

-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Thursday, April 19, 2018 3:32 PM To: profoxtech@leafe.com Subject: Re: INSERT INTO...SELECT Issue

On Thu, Apr 19, 2018 at 4:26 PM, Paul H. Tarver paul@tpcqpc.com wrote:

Unfortunately, now the ODBC Driver is throwing an error:

    "[Microsoft][ODBC Excel Driver] Syntax error (missing operator) in

query expression '3rdPartySickPay'."

Unsurprisingly, ODBC thinks it's a dumb name for a field, too.

You'll probably need to "escape" it. I'm decades out of ODBC, so I don't recall if ODBC wants you to enclose the field name in single quotes, square brackets or back ticks (that `` funny key in the upper left corner no one ever uses). A web search might yield a suggestion, or you can just try the Usual Suspects...