Too big for Access?

[TomPl]

I am using an ODBC to import data from another database. When I link or
import the data table it truncates any fields in excess of 255. I need field
260.

In Excel I can set up a MS Query to pull only the fields that I want. That
works great.

How can I get this data directly into Access? Is Access limited to 255
fields? I don't know that MS Query works with Access.

Tom

 

[Tom Wickerath]

Yes, the limit for the number of fields in any given table is 255. You can
find the various specifications by entering "Specifications" (without the
quotes) as the search term in Help.

In most cases, a properly designed table should not exceed about 25 fields.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[TomPl]

Any suggestions on how I might get field 260 into Access?

Tom

 

[John Spencer]

What is the source database?

For instance in MS SQL Server, you could construct VIEWs of the data that
returned chunks of the data (primary key and 150 fields and then Primary key
and the remaining fields).

You are still only going to be able to work with a maximum of 255 fields at a
time. Also, a record cannot have more than 2000 characters - excluding memo
and OLE object fields (Access 2003 and earlier - there may be some differences
in Access 2007).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[TomPl]

I thought there might be a solution similar to the process used for Excel,
but apparently not.

Thanks for the help.

Tom

 

[Tom Wickerath]

Excel 2003, or earlier, supports 65,535 rows. Excel 2007 includes support for
over a million rows. You can import all of your data into Excel, add a field
to uniquely identify each record (if it is not already present), and then
decide how to group these fields by subject into appropriate tables. Each
grouping would need to include the field(s) that can be used to uniquely
identify each record. These would then get imported into separate tables into
Access, keeping in mind that a record cannot exceed 2000 characters total.

Here is a link to several papers on database design. Make sure to read the
first two, written by database design expert and author Michael Hernandez:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[david]

Yes, you can set Access to pull only the fields you want. A linked
table doesn't do that, so a linked table won't work. You need to
set up a query that does that, just like you do in MS Query.

There are several ways to do that. It will probably work if you
specify the ODBC DSN in the Source Connect String property
of a query.

If not you can use a 'pass through query' directly connected to
the ODBC database by selecting "SQL Specific" from the
Query Menu.

I don't know where to find that on the Ribbon.

(david)