Join Selects using OleDb

[gregmonay]

hi,
I'm a newbie on Iseries Access and As400. I was trying to extract data from Iseries using OleDB IBMDA400. Selecting from one table, i can able to get data, but when i select from multiple tables using join it is not working. Is it possible to use join selects in OleDb?
Any ideas will be appreciated. Thanks in advance

[Andrew K]

Re: Join Selects using OleDb

Can't think of why it shouldn't work, so must be something in your SQL statement.
Can you post the SQL that is not working please? What are you getting back as error(s)?

[gregmonay]

Re: Join Selects using OleDb

Can't think of why it shouldn't work, so must be something in your SQL statement.
Can you post the SQL that is not working please? What are you getting back as error(s)?

I use an excel macro to connect and extract data from Iseries using IBMDA400 OleDB.
When i execute a select statement on a single table, it is working.
But when i execute a select statement on two tables with join, I received this error message:

Run-time error '-2147467259 (80004005)'
Automation error
Unspecified error

this is my code:

rs.Open "Select * from mylib.invtbl a left join mylib.tbldet b on a.tblid=b.tblid where a.tblnum=93002", cn_SystemA, adOpenForwardOnly, adLockReadOnly

I can able to use this code using ODBC connection but i dont want to be creating ODBC name on every workstation that's why i want to use OleDB.

Thanks for your ideas.

[Andrew K]

Re: Join Selects using OleDb

Never really tried IBMDA400, so my knowledge is pretty limited here I'm afraid.

Are you seeing anything at all in the joblog on the iSeries (look at QZDASOINIT jobs) in terms of messages?

Can't see anything wrong with your SQL, but it seems strange if a select over a single table works OK and this doesn't.

In terms of getting round having to create a DSN for ODBC on every machine, you can create a 'DSN-less connection'. I use the following code (in Excel to call a stored procedure)

Dim objConn As New ADODB.Connection
Dim ObjCommand As New ADODB.Command

objConn.Properties("Prompt") = adPromptComplete
objConn.ConnectionString = "DRIVER=Client Access ODBC Driver (32-bit); " & _
                            "SYSTEM = My400; SIGNON=1"

[GLS400]

Re: Join Selects using OleDb

Hi Gregmonay:

This works for me:

SELECT * FROM mylib.cendptmf, mylib.censusmf    
where cdacct='MON01' and cddepn=PMDEPN    
order by cdname

I suggest that you down load Littlepd's worksheet here:

http://code400.com/forum/showthread.php?t=4925 (a very nice example)

That will allow you to key your SQL in interactively.

Best of Luck
GLS

[gregmonay]

Re: Join Selects using OleDb

Thanks a lot for your inputs and ideas Andrew and GLS.

I have already used the DSN-Less connection and it worked.

Thank you guys.