Get column beyond 255th - Acc2K

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

Hi All,

I have to pull data for display from a database that has a table with more
than 255 columns. The problem is that the data I need is beyond the 255th
column. Anyone know how to do this?

Thanks
 
Tony

Access tables hold, at a theoretical maximum, 255 columns (i.e., fields).

However, on a practical note, you will rarely find a well-normalized,
relational database table (in Access or any other relational database) with
as many as 30 fields.

Consider reviewing "normalization" and "relational database design" before
doing anything more with this.

If you are, as you say, pulling this from a database that CAN store more
than 255 fields in a table, why are you using Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Tony said:
I have to pull data for display from a database that has a table with more
than 255 columns. The problem is that the data I need is beyond the 255th
column. Anyone know how to do this?
Click to expand...

According to the Access 2000 Help file, a table can have no more than
255 fields. Are you importing/linking data from a different database?
 
Hi All,

I have to pull data for display from a database that has a table with more
than 255 columns. The problem is that the data I need is beyond the 255th
column. Anyone know how to do this?

Thanks
Click to expand...

You can't. A Table is limited to 255 fields... period.

You'll need to create Views on the table to extract the extra fields,
and link to those views. What database software is this monster in?

John W. Vinson[MVP]
 
Jeff,

Thanks for the quick reply. I'm getting the data from a Progress SQL-92
database via ODBC. What I'm trying to achieve is getting some base data
that is stored in the Progress dB, showing it in a view, then storing it in
the Access dB. I only need to grab a few columns, which happen to be beyond
the 255th column of the Progress dB. The reason why we're using Access
instead of the Progress dB is because what we're doing has very little
bearing on what's going on the Progress dB and the schema is not set up to
track/manipulate the data that we want to keep in Access; we just want to
tap into the Progress dB so that the end users have validated lookups.

Make sense?

Thanks again.

Tony
 
Hey Neil,

Thanks for the response. As to where the data's coming from & what we'd
like to do with it, please see the explanation in my response to Jeff
Boyce's post.

Thanks,

Tony
 
John,

Thanks for the reply. The table from which I need the data is in a Progress
SQL-92 dB. Details of what I want to do are in my reply to Jeff Boyce's
post. I'll do some searching on creating views, but any chance you can
point me to a primer? I've heard the term before but do not know to what it
refers.

Thanks,

Tony
 
Tony said:
The table from which I need the data is in a Progress
SQL-92 dB. Details of what I want to do are in my reply to Jeff Boyce's
post. I'll do some searching on creating views, but any chance you can
point me to a primer? I've heard the term before but do not know to what it
refers.
Click to expand...

A View is essentially what Access calls a Query.

I'd assume from the SQL-92 in the name of your other database that
something like this will work (with the correct field and table names,
of course!):

CREATE VIEW vw_extrafields
AS
SELECT
Field256, Field 257
FROM
MyEnormousTable

(If the table has one or more fields as a primary key, you should
include those in your view as well.)

When you import/link the data into Access, you would then use the name
of the view rather than the table.
 
Thanks for the reply. The table from which I need the data is in a Progress
SQL-92 dB. Details of what I want to do are in my reply to Jeff Boyce's
post. I'll do some searching on creating views, but any chance you can
point me to a primer? I've heard the term before but do not know to what it
refers.
Click to expand...

I've never used Progress software, and I really have no idea how (or
even whether) it lets you create views. Sorry, but you'll need to get
that information from a Progress expert!

John W. Vinson[MVP]
 
I would create a Pass-Through query to the Progress db. The P-T can be used
to select only the desired records and fields.
 
Hey Neil,

Thanks for the information. I found similar code on the web yesterday
evening and wasn't successful with it. I get a syntax error that reads:

//////////////////////////////////////////////////////////////////
Syntax error in CREATE TABLE statement.
//////////////////////////////////////////////////////////////////

when I try to save the view. When I click OK, VIEW is highlighted in the
following code:

////////////////////////////////////////
CREATE VIEW getFields
AS
SELECT
PUB_jc_job.co
FROM
PUB_jc_job
////////////////////////////////////////

I am, by no stretch of the imagination, a SQL expert, and imagine that I'm
addressing the table incorrectly. In any event, I'll play with the syntax
and see what happens.

I appreciate the help.

Tony
 
John,

Thanks, anyway. If nothing else, I'll have the opportunity to learn a
little more SQL. And trust me, I've found other posts you've made here and
elsewhere helpful.

Thanks again,

Tony
 
Hey Duane,

Thanks for the suggestion. I work pretty exclusively with select queries
but will try my hand at a Pass Through.

I think I might learn a little in dealing with this issue...

Thanks,

Tony
 
Tony said:
Thanks for the information. I found similar code on the web yesterday
evening and wasn't successful with it. I get a syntax error that reads:

//////////////////////////////////////////////////////////////////
Syntax error in CREATE TABLE statement.
//////////////////////////////////////////////////////////////////
Click to expand...

You need to create the view in your Progress Database - that looks
suspiciously like the error you get when you try to execute a CREATE
VIEW within an Access Query!

The syntax for CREATE VIEW in Progress is on page 65 of this pdf:
<http://www.psdn.com/library/servlet/KbServlet/download/1103-102-907/e92.pdf>
 
Hey Neil,

Yes, I was trying to create the view in Access. Looks like I've got some
reading to do. Thanks for your help and passing along the document.

Tony
 
Jeff, Neil, John, and Duane,

Well, looks like I'm getting closer. I queried the data in Excel (for some
reason when I select the Progress table in Excel when I get external data, I
can see all the columns), added the columns I wanted, and returned the data
I was looking for. I took the SQL from Excel and pasted it into an Access
Pass Through and got what I needed. Now, I just have to hammer on it and
make sure that I'm really getting the all the correct data. To me, the SQL
looks odd, as though it's adding extra information. The SQL generated by
Excel which appears to work in Access looks like this:

////////////////////////////////////////////////////////////////////////////
SELECT jc_job_0.co, jc_job_0.div, jc_job_0.ctr
FROM PUB.jc_job jc_job_0

////////////////////////////////////////////////////////////////////////////

What I would have expected is:

///////////////////////////////////////////////////////////////
SELECT jc_job.co, jc_job.div, jc_job.ctr
FROM PUB.jc_job
///////////////////////////////////////////////////////////////

Anyway, I'll test and hopefully this is what I need. Thanks again for all
the help; I really appreciate you guys taking time out of your days to help
me.

Tony
 
Back
Top