Problem with ODBC Microsoft.Jet.OLEDB.4.0 and the Hyphen in Filename.

General Macro Scheduler discussion

Moderators: Dorian (MJT support), JRL

Post Reply
cusherwood
Newbie
Posts: 3
Joined: Wed Oct 12, 2016 10:47 am
Location: Tampa, FL

Problem with ODBC Microsoft.Jet.OLEDB.4.0 and the Hyphen in Filename.

Post by cusherwood » Tue May 26, 2020 9:14 pm

Recently I have been creating more automation with Macro Scheduler and discovered a bit of a strange problem with “Microsoft.Jet.OLEDB.4.0” and the “Microsoft Text Driver (*.txt; *.csv)”. This issue reveals itself when utilizing variables and special characters appear in the file name. In my case specifically, I instructed users to only use the underscore or hyphen. This has been my normal go-to fix for years to resolve special character issues. Although the underscore is not a problem, the hyphen will not process and generates the error, “Syntax error in FROM clause”.

This is where my overly long troubleshooting of this conundrum began. In researching this problem, the Macro Scheduler forum and throughout Google searching. I was unable to find any real documentation that explicitly helped to resolve this issue. Like many coding problems, the answer is very simple once it is understood.
My solution needs to read a CSV dataset utilizing the built in Windows ODBC drivers then process into a Microsoft SQL database as an update to a table. All is accomplished with my compiled Macro Scheduler script and lunched via Microsoft Task Scheduler.

The Issue, I cannot completely control the file names being created, or the version of ODBC on all Windows computers running the script. The hyphen / dash was the culprit of my excessively long journey to resolution.
Solution was to add square brackets around the FROM variables. All current documentation omits the square brackets, what is understandable unless you have a file name variable that allowed special characters.

PROBLEM: DBQuery>dbH,select * from %UpdateFile%,fields,num_recs,num_fields
SOLUTION: DBQuery>dbH,select * from [%UpdateFile%],fields,num_recs,num_fields

My hope is this problem could make it into the user manual. This might save some other Macro Scheduler enthusiast some long hours in troubleshooting what appears to be a bug in the ODBC driver. I have installed a VM of Windows Server 2008 and the issue exists. Additionally, I have tested on Windows Server 2012R2, Windows Server 2016, Windows Server 2019, and Windows 10 and all exhibit the same issue.

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Problem with ODBC Microsoft.Jet.OLEDB.4.0 and the Hyphen in Filename.

Post by Marcus Tettmar » Thu May 28, 2020 1:02 pm

Hi,

This square bracket thing is NOT a Macro Scheduler thing, which is why it is not in the manual. The square brackets thing is peculiar to Microsoft SQL. They are identifier delimiters:

https://stackoverflow.com/questions/991 ... e-designer

https://social.msdn.microsoft.com/Forum ... ransactsql

DBQuery is agnostic, independent of any database technology and doesn't even understand that database technology. It is like a conduit. Like the mail system. You simply pop a message in one end, to be interpreted the other and a response comes back. The format of the message is irrelevant to DBQuery/Macro Scheduler and is only interpreted by the database technology at the other end.

We cannot re-document every syntax feature of every database type out there. That would be impossible and DBQuery would then run to thousands of pages.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

cusherwood
Newbie
Posts: 3
Joined: Wed Oct 12, 2016 10:47 am
Location: Tampa, FL

Re: Problem with ODBC Microsoft.Jet.OLEDB.4.0 and the Hyphen in Filename.

Post by cusherwood » Thu May 28, 2020 6:05 pm

Hello Marcus,
Thank you for your consideration on my request. I completely understand and agree with your point of view on this subject. Hopefully this post will serve as help for anyone experiencing this specific problem.
Sincerely,
Charlie

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts