Access all fields in a Microsoft SQL Server table

There is a Microsoft SQL Server table that has over 315 fields.  When I link to this table within MS Access 2013, I can only see up to 200+ fields.  I need to access the 300th field in this sql table.  How can I access that field when MS Access only shows up to a certain point.

There is also no possibility to split the SQL table.
SFWolverineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
I had a similar issue with Salesforce.com objects being 700+ columns ( many columns were nvarchar(4000)'s ) and importing into SQL Server, where the row width was well over what SQL Server could handle.

The resolution was to import the table as two SQL Server tables, both with the two primary key fields.  Then create a SQL Server view that joins both tables by the keys, returning a single set with all columns.

In your case, perhaps you can create two SQL Server views with 150+ columns each, both with the primary key of the table.  Then link both of those views into your Access.  Not ideal, but at least it will give you all columns.

Whether you can then create a query that joins both linked tables ... no idea, as I'm guessing the query column limitations is the same as the table column limitations.  Either way, good luck.

Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperCommented:
Access maximum fields in a table or query = 255.

Therefore using Access to return this information ... will require you to split the table into views on SQL Server.

ET
Eric ShermanAccountant/DeveloperCommented:
Also, it just depends on how you plan to use the data with 300+ fields in Access???

1.) You could build a Form object in Access (Max of 755 fields) to hold your 300+ fields from SQL Server.

2.) Create an ADO recordset that connects to the SQL Server's table ... this recordset will return fields  beyond Jet's 255 field limit.

3.) Bind the fields on your form to this recordset.

You would use the Form's OpenEvent to set the recordset to the ADO connection as shown below.
Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open 
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Set rs = Nothing
   Set cn = Nothing
End Sub

Open in new window


ET
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

pcelbaCommented:
To create a view containing just necessary columns is nothing unusual at SQL Server and it even improves performance because the view passes just the necessary info from the server over the network.

The view creation does not mean "to split the table" in physical meaning.

To create a view issue following command on SQLServer:

CREATE VIEW SomeName AS  SELECT column1, column2, column3, ... , columnN FROM SomeTable

You may also add WHERE clause.

Alternative way is to define a query containing just the necessary columns in the Access directly.

Of course, if you would need more than 255 columns at once then you have to create 2 views or 2 queries or ADO (interesting approach) as described above.
John TsioumprisSoftware & Systems EngineerCommented:
A table with so many fields is a good candidate for normalization....while trying to solve your connection issue probably its time to consider a redesign...
psteffCommented:
I can't test it out right now, but perhaps this might work.  Set up the fields that you want in your query in design view in Access as your normally would, then for the 300th column, switch to SQL view of the query within Access and follow the syntax of the other fields that you already included and enter your 300th field there.  Perhaps even though you cannot see the 300th field in the query designer, you might still be able to access it.

Having said that however, I would agree with others, that the best solution would be for you to get the SQL DB person just to write a view within SQL for your purposes.  Much more efficient.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.