linking access and sql server

[rkapfer]

Greetings,

I've got an sql server that I can see and link/import data from but a few of the files are large meaning greater that 255 fields. So I create a link to that file and do see the first 255 fields in my linked table but! How do I get to see fields 256 thru 275?

 

[boblarson]

Can you get 2 views created on SQL that you can connect to?

 

[rkapfer]

I'm linking via ODBC I have no control over the DSN. When I link I'm seeing only 255 fields. I'm currently using Excel via MS Query to get fields 256-275 then linking an Access table to the spreadsheet. I'd like to eliminate Excel.

 

[boblarson]

Don't see how you're going to get around that. So there is no way you can request from the DBA to get 2 views so you can have all fields?

 

[MSAccessRookie]

I'm linking via ODBC I have no control over the DSN. When I link I'm seeing only 255 fields. I'm currently using Excel via MS Query to get fields 256-275 then linking an Access table to the spreadsheet. I'd like to eliminate Excel.

Do not look at the table. Create an SQL Server View that looks at most of the data, and another (using the same primary key) that looks at the rest.

An example might be:

Column 1 is the PK

View1 selects Columns 1 - 200
View2 selects Columns 1, and 201 to the end

You can then have a query that joins the two views (Access will treat them as tables), and you can see all of the data as if it were a single table.

My real question is whether or not the table needs so many columns, or if a mild redesign would eliminate this problem entirely

 

[rkapfer]

Old data base we're banging at it probably like no one else, the vendors tough to deal with. Probably an old Cobol data base converted huge records throughout.

Access tables don't link to MS Query do they?

I'll have to create a button to refresh Excel or move to Business Objects, this won't be the last adventure.

 

[MSAccessRookie]

Old data base we're banging at it probably like no one else, the vendors tough to deal with. Probably an old Cobol data base converted huge records throughout.

Access tables don't link to MS Query do they?

I'll have to create a button to refresh Excel or move to Business Objects, this won't be the last adventure.

A Cobol Database would probably NOT BE relational, as Cobol used Record Oriented structures. This increases the probability that this database could and should be redesigned for the purpose of normalization, and then this problem would go away. You said it had been converted for use on an SQL Server, so you should be able to work with it.

In the mean time, if you make two or more views from the table, you should be able to make it work the same way that it does now with an Access query that puts them back together.

Not sure about MS Query, as I have never tried it.

 

[rkapfer]

The Access ODBC wizard doesn't work like the MS Query ODBC wizkid, wish it did. Access only lets you pick an exposed view of the data, MS Query ODBC wizard lets you pick not only the file but also the fields. The vendors' already said "we've never been asked that..." amongst other "we'll get to that one day".

 

[MSAccessRookie]

The Access ODBC wizard doesn't work like the MS Query ODBC wizkid, wish it did. Access only lets you pick an exposed view of the data, MS Query ODBC wizard lets you pick not only the file but also the fields. The vendors' already said "we've never been asked that..." amongst other "we'll get to that one day".

Is something or someone (other than lack of experience) preventing you from creating views? I do not have any problems with that here as I have admin privs for the database.

The views are hands down your best available option.