as400 odbc connection problem

nak1ugs

New Member
Joined
Sep 28, 2006
Messages
3
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
the first thing that strikes me upon reading your code is this line:

Code:
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....
 
Upvote 0
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
 
Upvote 0
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 :biggrin:
 
Upvote 0

Forum statistics

Threads
1,212,096
Messages
6,105,942
Members
447,985
Latest member
gigi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top