MSAccess limitations when linking to ODBC database

[daveatthewell]

My Office PC has Access 2002 installed (with Office Standard 2003). I'm connecting to a remote Mainframe containing hundreds of DBase2 tables, the structure of which I have no control. Whenever I link to the table I'm interested in, Access pulls through around 256 fields of which I need about 10 max. What I do want is for access to pull through,say, field 300, field 310, field 311 etc (they have proper names but I hope you get my drift. It doesn't, it stops at field 256 (or maybe that should be 255!!). If I link to the same table via an Excel ODBC connection, I can select just the fields that I want, including those beyond the apparent 256 boundary seemingly set in Access. Now this seems a bit stupid; here I am using a database application to grab data from another database application and can't, but I can from a spreadsheet application. Is there a work around?
Regards
Dave

 

[Norie]

Dave

How are you pulling the data?

Can't you query the source database to only return the fields of interest?

If you could it would only require a simple SQL SELECT query.

 

[revans]

Access tables can only have 255 fields. You can query for just the fields you want using the IN SQL clause. Excel allows for more columns because it's not a database, it's a "flat file".

hth,

Rich

 

[daveatthewell]

Norie, revans thanks for your responses. I've tried using the IN clause and am effectively coming up against the same problem. Her's the SQL (changed the table/field names to keep it short.

SELECT A, B, C, D, E, F, G, H, I, J, K,L, M, N, O, P, Q, R, S, XX, YY
FROM ZZZZZ
IN '' [ODBC;DSN=TTTT_DB2_SOURCE;MODE=SHARE;DBALIAS=TTTT_SRC;PROGRAMNAME=DGENERIC;IGNOREWARNINGS=1;DB2DEGREE=ANY];

If I remove XX and YY from the SQL, the query runs and brings the correct data through; if I add them back in (they reside beyond field 255 - think they are fields 300,309), Access prompts me for a parameter for XX and YY. Simply selecting OK at that point lets the SQL run but the two field colummns are blank (and I know from Excel that there is data there)(also the field heading has changed to Expr1 and Expr2)!!. Any more thoughts.

Dave

 

[Norie]

Dave

As far as I can work out from what's in the link Rich posted you need a SELECT after the IN clause.

SELECT *
FROM ZZZZZ
IN '' [ODBC;DSN=TTTT_DB2_SOURCE;MODE=SHARE;DBALIAS=TTTT_SRC;PROGRAMNAME=DGENERIC;IGNOREWARNINGS=1;DB2DEGREE=ANY];
SELECT A, B, C, D, E, F, G, H, I, J, K,L, M, N, O, P, Q, R, S, XX, YY

 

[xenou]

How exactly do you "link" to the mainframe? Aside from the above DSN-less attempts, are you putting the connection info in access, or have you created a machine dsn, or is it being done in a vba code module?

Note: off topic but what in the world are these two single quotes (I guess):
IN '' [ODBC;
I've never seen anything like that in a query or connection string ...