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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
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....
 

nak1ugs

New Member
Joined
Sep 28, 2006
Messages
3
the tables are correct, it could just be our very outdated system.
thanks anyway.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Jun 27, 2006
Messages
345
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:
 

Forum statistics

Threads
1,136,655
Messages
5,677,017
Members
419,668
Latest member
DharmaK

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
Top