as400 odbc connection problem

[nak1ugs]

could someone please help me with the following code, as always, its probably something really simple.
Ive been looking on the net to try and find a solution, but no luck so far.

my code is :

Range("D1").Select
With Selection.QueryTable
.Connection = "ODBC;DSN=GENERAL LEDGER;"
.CommandText = Array( _
"SELECT FLP003.ACCN03, FLP008.LINDES, FLP008.PSTAMT, FLP008.PSTPER, FLP008.DOCREF, FLP008.TT " _
& "FROM S44K6624.OSLGLF3.FLP003 FLP003, S44K6624.OSLGLF3.FLP008 FLP008 " _
& "WHERE FLP003.CONO = FLP008.CONO AND FLP008.ACCN08 = FLP003.ACCN03 AND ((FLP008.PSTPER=10608) AND (FLP008.CONO='IL') AND (FLP003.LVCD01='05' And FLP003.LVCD01<>'000') AND (FLP003.LVCD02='310')) ")
.Refresh BackgroundQuery:=False

Call Filter

End With

I think the problem is coming from the commandtext line, but again, im not sure.

What the code is supposed to do is dial into our as400 and update a spreadsheet with all the relevant info.

Hope Ive given enough information, and that someone can help.

Regards

Neil

 

[patrickmuldoon99]

the first thing that strikes me upon reading your code is this line:

FROM S44K6624.OSLGLF3.FLP003 FLP003, S44K6624.OSLGLF3.FLP008 FLP008 " _

It may be the database system you are using, but i've never experienced using dot notation, or spaces for that matter when describing the tables i'm querying....

 

[nak1ugs]

the tables are correct, it could just be our very outdated system.
thanks anyway.

 

[Richard Schollar]

Hi Neil

What error are you getting? Is the VBE telling you where it is erroring out? (ie what's highlighted).

Patrick, I believe in the above example where Neil has:

S44K6624.OSLGLF3.FLP003 FLP003

S44K6624 is the data base owner, OSLGLF3 is the database, FLP003 is the table. The space between this and the final FLP003 is because an alias is being created for the S44K6624.OSLGLF3.FLP003 which is FLP003 (hence the fields in the table are preceded with FLP003 and not the whole dbo.db.tbl shebang). it's pretty standard stuff.

Best regards

Richard

 

[patrickmuldoon99]

You learn something new every day eh!

My SQL experience is limited to Oracle, Access so that's probably why I never came across anything like that, or perhaps that I never got into enough depth for that sort of referencing of tables.

i'm going to gracefully bow out now