DSXchange

--- Moderator add on ---
Originally split from this post
(release, job type and Os were arbitrarily selected)
--- End moderator add on ---
I will like to add up my question here itself. Correct me I am wrong ,So accroding to the reply I can use MDB as a source if I create a ODBC to it?
I have to use it but problem with the MDB file is that the MDB file has column names with spaces something like "a b c". so when I try to use it as a ODBC stage the query which gets generated is select 'xyz'.'a''b''c' from 'xyz' , which is obviously wrong and doesnot work. I tried doing select 'xyz'.[a b c] from 'xyz' but stil it does not work. I dont have much knowledge of Access so dont know how to the best I can use it as a source in datastage.
Anyone who can help me in this?

That's very close to hijacking the thread!

Set up the ODBC stage, with the DSN filled in. Then click GetSQLInfo. This goes out to the DSN and retrieves the SQL quote character (which is "`" for MS Access) and delimiter character.

So access needs queries like

Yeah thats true but I dint want to create a seperate thread for the same topic.

Thats true ray but the thing which is bothering is that the column names have a space in between which I am not able to decipher in the ODBC stage as it puts quotes everywhere it finds space. I tried User define query but that also give sme the same error. Is it like Column Name should not have spaces in there??

The ODBC standard forbids spaces so the ODBC driver enforces the rule. That MS Access does not is just Microsoft not following its own standards!

About your only choice is to use user-defined SQL. Let DataStage generate the SQL and then modify it.

Thats the problem. That was th efirst thing that came in to my mind but it doesnot work.

Use User Defined SQL as Ray says, then remove all quotes from the SQL, enclose in square brackets ("[" and "]" ) the column names that contain spaces.

The DataStage column names must be renamed to contain no spaces.

Example User Defined SQL :

SELECT Table1.FirstColumn, [Table1.Second Column] FROM Table1;

Another option is to create a view in ACCESS and rename the columns in the view so that they do not contain spaces.

Reference the view not the table using ODBC.

Dates can also be strange in Access; depending on the circumstances you may need to surround with "#" characters. For example The inner "#" characters identify a reference to a job parameter, the outer "#" characters identify an MS Access date value.

DeepakCorning wrote:Thats the problem. That was th efirst thing that came in to my mind but it doesnot work.

I ran into the same problem as Deepak today, and I simply did what ray and crouse suggested. It worked without any problems. Are you sure you didn't leave any extra "`" inside a column name? Those things are a bit hard to spot sometimes

While connecting to SQL server while using the ODBC connector, it worked when I used the brackets "[" , "]" when the field names had spaces and '#'