Linked SQL Server Table / Max Columns

[daveWP]

Hi guys,
I've setup up a nice MS 2007 Access workstation where I use connect to the main SQL server through ODBC.

All was going good until I tried to map a field, and as it turns out, Access only links to the first 256 columns. The field I want is the SQL Server Table's 260th column. as a result, I can't map the damned field to my MS Access Form!!!@!#@$!.

I know its not a data access error, because I can use the same ODBC string in MS Excel to query the field, but I need something in the Access environment so I can run Update Statements.

Is there any way to defeat the 256 max column or atleast Select the column through a different table mapping?

Please Help.

 

[WayneRyan]

Dave,

I think a stored procedure would help you.

http://www.eggheadcafe.com/software/aspnet/35665604/accessing-stored-procedure-via-vba.aspx

Wayne

 

[daveWP]

Hi WR,
I'm not sure if the stored procedure is created in the Access environment or the SQL Server environment. I'll need to create something in the Access environment, because I don't have any admin rights for the SQL Server.

That means I'm looking for a way to pass this procedure through the ODBC and change the way Access links to the table (something other than "grab the first 256 columns you see")

A very easy solution is to create a view in the SQL Server, but that would take away from the elegance of my current solution, which is rooted in not bothering anyone.

Any other comments?

 

[Galaxiom]

Use a recordset to retreive just the fields you want.

 

[LPurvis]

A recordset would have to be opened directly against the server of course - otherwise you'll again fall foul of Jet's 255 column limit. (It's not an Access limitation per se.)

Since you mention updatability then a passthrough isn't much help to you.
Your mention of creating a View on the server which returns the subset of columns which you are interested in (including the Primary Key field(s) of course - that's absolutely vital) will allow you to link that (as you would a SQL table) and update against it.
It's not inelegant - just perhaps a niggle to ask your DBA for it huh? ;-)

If you wanted to go the recordset method then you won't need to bother the DBA - but you'll be limited to updating a single row at a time of course (though don't be deceived into thinking that updates on linked tables are intrinsically set based either, smoke and mirrors. ;-)

Cheers.