Problems with NewQueryDef (Passthrough) using IBM iSeries ODBC Driver

[Derevon]

Hi everyone,

I have some problems with NewQueryDef adding undesired (illegal) characters to my PassThrough Query that the iSeries Access ODBC Driver can't handle.

For instance, I have an SQL string like this:

sql_str = "SELECT GLCO FROM JD_CSEFP7.F0911"
Set qdf = db.CreateQueryDef("Q01_PT", sql_str)

If Access would simply leave the SQL code exactly as it is, all would be fine, but for some reason it insist on adding [] characters and a semicolon at the end which are not allowed by the driver.

The end-result of the above code is a query that looks like:

SELECT GLCO
FROM [JD_CSEFP7].F0911;

The above would be fine if it was inside Access, but it simply won't work in my Pass-through Query.

Anybody knows any workaround for this?

Basically what I'm trying to do is to create dynamic pass through queries which take settings stored in an Access table as parameters.

Thanks for any help

 

[Cronk]

That's because JD_CSEFP7.F0911 is not a valid table name in Access. The period upsets the apple cart.

If you can't change the name on the backend, try creating a query/view based on the table and using that as your source.

 

[pbaldy]

I would create a pass through query and save it. Then just change the SQL. It won't touch the syntax when it knows it's a pass through query.

 

[Derevon]

I would create a pass through query and save it. Then just change the SQL. It won't touch the syntax when it knows it's a pass through query.

This solution seems to work just as you said.

Thanks!

 

[pbaldy]

Happy to help!