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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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