MSAccess limitations when linking to ODBC database

daveatthewell

New Member
Joined
Jul 28, 2006
Messages
43
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,471
Office Version
365
Platform
Windows
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.
 

daveatthewell

New Member
Joined
Jul 28, 2006
Messages
43
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,471
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,612
Office Version
2013
Platform
Windows
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 ...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,503
Messages
5,414,921
Members
403,552
Latest member
Daniel Kuenstler

This Week's Hot Topics

Top