Too many fields

[deb]

I found this article from Douglas Steele...
Query that will update or insert as required.

For the sake of discussion, I'll assume that there are two tables
(ExistingData, and NewData), and that the two tables are identical in
structure.
1. Create a new query
2. Add tables NewData and ExistingData to the query.
3. If a relationship line isn't drawn between the two tables, do so now,
relating them by their primary key.
(Note that if it's a compound primary key, you need to join on each of the
fields in the index.)
4. Double-click on the relationship line joining the two tables, and choose
the option "Include ALL
records from 'NewData' and only those records from 'ExistingData' where the
joined fields are equal.",
then click OK. (If you're dealing with a compound primary key, you need to
do this for each line
joining the tables.)
5. Drag all of the fields from table ExistingData into the query grid.
6. From the Query menu, select Update Query to change the Select query to an
Update query.
7. Now, for every field in the query, go to the Update To cell and type
[NewData].[name of the field].
8. Save the query with an appropriate name.

When I create this. I get the error "Too many fields defined."
Unfortunately I have 160 fields. They are imported via excel.
Is there anything I can do to get around this?

Thanks

 

[Golfinray]

I think 255 fields is the limit and you are not there. I would say you have
query problems or table problems. Right click on your query properties and
set display all data to no. Sometimes that gets your query running.

 

[Jeff Boyce]

Just because Excel uses 160 columns (in a spreadsheet) doesn't mean you are
constrained to use 160 columns/fields in a table.

Access is a relational database, and you won't get easy/good use of the
tools it offers if you feed it 'sheet data.

Before you try anything else, consider spending the time to design a
well-normalized data structure. If "normalization" and "relational" are not
familiar terms, you can beef up on these topics (to get better use of
Access), or you could consider using a spreadsheet!

Is there something about Access that lends itself to solving a business need
you've identified?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[a a r o n . k e m p f]

bullshit whore

if your database won't support 255 fields then move to SQL Server!!!

that's why I moved a decade ago; and I'll sure never look back!!!

SQL Server 'just works'- the kids around here blame it- when things go
wrong- on the network, the users, the design.

Screw excuses-- SQL Server and Access Data Projects-- work _GREAT_
with that many columns.

Access MDB sucks balls as a database; it obviously doesn't meet our
needs.

-Aaron

I think 255 fields is the limit and you are not there. I would say you have
query problems or table problems. Right click on your query properties and
set display all data to no. Sometimes that gets your query running.

I found this article from Douglas Steele...
Query that will update or insert as required.

For the sake of discussion, I'll assume that there are two tables
(ExistingData, and NewData), and that the two tables are identical in
structure.
1. Create a new query
2. Add tables NewData and ExistingData to the query.
3. If a relationship line isn't drawn between the two tables, do so now,
relating them by their primary key.
(Note that if it's a compound primary key, you need to join on each of the
fields in the index.)
4. Double-click on the relationship line joining the two tables, and choose
the option "Include ALL
records from 'NewData' and only those records from 'ExistingData' where the
joined fields are equal.",
then click OK. (If you're dealing with a compound primary key, you need to
do this for each line
joining the tables.)
5. Drag all of the fields from table ExistingData into the query grid.
6. From the Query menu, select Update Query to change the Select query to an
Update query.
7. Now, for every field in the query, go to the Update To cell and type
[NewData].[name of the field].
8. Save the query with an appropriate name.

When I create this. I get the error "Too many fields defined."  
Unfortunately I have 160 fields.  They are imported via excel.
Is there anything I can do to get around this?

- Show quoted text -

 

[a a r o n . k e m p f]

bullshit whore;

SQL Server and Access Data Projects support more than 255 columns.
I'm _DEAD_ serious.

This is one of the main reasons I moved from Access to SQL Server a
decade ago. Because it didn't complain about my requirements.

If these kids blame something on 'the network' or 'poor design' or
'bad users'- you typically should realize that really means
' the mdb kids around here have small wangs and can't compete with a
real friggin database'.

That was my translation, my diagnosis.
Don't let these dipshits pass the buck.

SQL Server 'just works'.

Access doesn't.

-Aaron

Just because Excel uses 160 columns (in a spreadsheet) doesn't mean you are
constrained to use 160 columns/fields in a table.

Access is a relational database, and you won't get easy/good use of the
tools it offers if you feed it 'sheet data.

Before you try anything else, consider spending the time to design a
well-normalized data structure.  If "normalization" and "relational" arenot
familiar terms, you can beef up on these topics (to get better use of
Access), or you could consider using a spreadsheet!

Is there something about Access that lends itself to solving a business need
you've identified?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I found this article from Douglas Steele...
Query that will update or insert as required.

For the sake of discussion, I'll assume that there are two tables
(ExistingData, and NewData), and that the two tables are identical in
structure.
1. Create a new query
2. Add tables NewData and ExistingData to the query.
3. If a relationship line isn't drawn between the two tables, do so now,
relating them by their primary key.
(Note that if it's a compound primary key, you need to join on each of the
fields in the index.)
4. Double-click on the relationship line joining the two tables, and
choose
the option "Include ALL
records from 'NewData' and only those records from 'ExistingData' where
the
joined fields are equal.",
then click OK. (If you're dealing with a compound primary key, you need to
do this for each line
joining the tables.)
5. Drag all of the fields from table ExistingData into the query grid.
6. From the Query menu, select Update Query to change the Select query to
an
Update query.
7. Now, for every field in the query, go to the Update To cell and type
[NewData].[name of the field].
8. Save the query with an appropriate name.

When I create this. I get the error "Too many fields defined."
Unfortunately I have 160 fields.  They are imported via excel.
Is there anything I can do to get around this?

- Show quoted text -

 

[Tony Toews [MVP]]

b*****t w***e

Your use of foul language and name calling is exceedingly obnoxious.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[James A. Fortune]

...

if your database won't support 255 fields then move to SQL Server!!!

that's why I moved a decade ago; and I'll sure never look back!!!

SQL Server 'just works'- the kids around here blame it- when things go
wrong- on the network, the users, the design.
...
SQL Server and Access Data Projects-- work _GREAT_
with that many columns.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/ee830a006b9fb1d3

The following link explains the page split mechanism that SQL Server
uses (great article BTW):

http://www.microsoft.com/technet/prodtechnol/sql/70/books/c0618260.mspx

SQL Server doesn't "just work" when there are lots of fields. IIRC,
both Access and SQL Server basically use just the memory required for
the amount of text used. My point is that because both Access and SQL
Server use a page split mechanism you have to be careful not to go above
a certain size for an entire record/row. Being able to have more than
255 fields would make checking for that condition even more important.
Having a memory limit for a record/row is a price we pay for efficient
indexing. Microsoft could move that record memory limit up, but it
would probably be at the expense of performance. It seems better to
push for more normalization and a smaller number of fields in tables
than to do that. Also, Access allows you to use Memo fields (stored
separately) if you expect the text length of a particular field to
become large. So just as there are tradeoffs in weighing the merits of
Access vs. SQL Server for a particular use, there were tradeoffs made in
how to deal with accessing the data in a table with a large number of
fields efficiently.

James A. Fortune
(e-mail address removed)

 

[Pete D.]

Simplify the query but also editing the tables design over and over can
leave ghost fields in the table that can cause this error. Copy and paste
the table design only and append your data from old table to new one.
Rename new table to old table name and fix indexes. Rerun query and see if
problem goes away.
http://support.microsoft.com/kb/128221
http://support.microsoft.com/kb/248270
http://support.microsoft.com/kb/199076

 

[a a r o n . k e m p f]

I just think that it's _CUTE_ that you list an article for SQL 7.0

SQL Server doesn't 'just work'? ? Can you give some background for
this? From the hundreds of SQL databases that _I_ have worked on in
the past decade.. Yes-- SQL Server 'just works' and Microosft Access
'does not work reliably enough for a single user and a single record'.

Sorry-- those are the facts.

SQL Server 'just works'. Sorry-- I just don't get what you're trying
to say here.

The 2000 byte limit for Access is 8096 bytes for SQL Server. SQL
Server can have tables that are 4 times wider.. Not including special
datatypes.

I don't think that it seems better to 'push for more normalization'
I think that it makes sense to use a database that _JUST_WORKS_ no
matter what the schema looks like.

There are perfectly valid reasons for having databases that are 400
columns wide.

Sorry-- but if your db doesn't fit your needs then move to a real
database (SQL Server or mySql)

 

[James A. Fortune]

I just think that it's _CUTE_ that you list an article for SQL 7.0

It just had better documentation . I can't imagine that they gave up
the page split mechanism.

SQL Server doesn't 'just work'? ? Can you give some background for
this? From the hundreds of SQL databases that _I_ have worked on in
the past decade.. Yes-- SQL Server 'just works' and Microosft Access
'does not work reliably enough for a single user and a single record'.

Sorry-- those are the facts.

SQL Server 'just works'. Sorry-- I just don't get what you're trying
to say here.

The 2000 byte limit for Access is 8096 bytes for SQL Server. SQL
Server can have tables that are 4 times wider.. Not including special
datatypes.

I don't think that it seems better to 'push for more normalization'
I think that it makes sense to use a database that _JUST_WORKS_ no
matter what the schema looks like.

There are perfectly valid reasons for having databases that are 400
columns wide.

I don't disagree with the points you are making except that a table with
400 columns doesn't "just work," especially in SQL Server and that
Access can work reliable for quite a few users. I have over 100
concurrent users in Access typically.

Sorry-- but if your db doesn't fit your needs then move to a real
database (SQL Server or mySql)

I use SQL Server when Access is inadequate, which for my purposes is seldom.

James A. Fortune
(e-mail address removed)

I'm sorry to inform you that they insulted you equinely without your
knowledge:

http://en.wikipedia.org/wiki/Francis_the_Talking_Mule

is the most likely obscure reference, IMO, from "Stripes."

 

[a a r o n . k e m p f]

I just don't think that there is a single benefit to JET anywhere.

I use SQL Server or JET where appropriate also.
I just KNOW that JET is a complete waste of time.

It isn't portable-- it isn't reliable.

And you kids make excuses 'oh your design is bad'.

The design isn't bad-- that is not relevent.
Access doesn't support wide tables. SQL Server does.
Access doesn't support automation. SQL Server does.
Access doesn't support indexes. SQL Server does.
Access doesn't support ROI. SQL Server does.

-Aaron

 

[a a r o n . k e m p f]

wow.. sounds like an Access/JET bug... and another kid making excuses.
Is anyone else keeping track of this?

Microsoft isn't commited to fixing bugs in JET. They have a 'real
database' or twelve to concentrate on.

-Aaron

 

[a a r o n . k e m p f]

I love Bill Murray. Stripes is one of those classic movies.

Thanks for ligthening the situation This newsgroup needs more
humor.

-Aaron

 

[James A. Fortune]

I just don't think that there is a single benefit to JET anywhere.

I use SQL Server or JET where appropriate also.
I just KNOW that JET is a complete waste of time.

It isn't portable-- it isn't reliable.

And you kids make excuses 'oh your design is bad'.

The design isn't bad-- that is not relevent.
Access doesn't support wide tables. SQL Server does.
Access doesn't support automation. SQL Server does.
Access doesn't support indexes. SQL Server does.
Access doesn't support ROI. SQL Server does.

-Aaron

Your points are generally valid. But, for now, Access, even used with
JET, has great ROI in terms of development time. As developers get more
familiar with using SQL Server and exploiting its advantages, it might
bring an even greater ROI than JET -- eventually. If you really want to
get more Access developers to use SQL Server you should post some
examples of exceptional code that makes use of SQL Server's advantages
or show examples of how you overcame various problems in upsizing to SQL
Server. That way, everyone that wants to can move quickly to SQL Server
without experiencing the pitfalls you encountered. There is definitely
a leap involved in moving to SQL Server. Perhaps the chasm will grow
narrower with time.

James A. Fortune
(e-mail address removed)

Dr. Wedekind, my faculty advisor, had been pushing me particularly hard
as a teaching/research assistant -- even beyond what I believed myself
capable. So I asked to have a meeting with him in his office. The
conversation was brief. Immediately after the conversation, perhaps out
of mutual respect and not wishing to say something we'd regret, stared
at each other for several hours until the twilight no longer enabled us
to see each other. From that point onward I knew how true burnout felt
and know when it is approaching.

 

[James A. Fortune]

I love Bill Murray. Stripes is one of those classic movies.

Thanks for ligthening the situation This newsgroup needs more
humor.

-Aaron

That's a fact, Jack! Err..., maybe using the name Jack isn't a good
idea given the context .

James A. Fortune
(e-mail address removed)

 

[a a r o n . k e m p f]

just because 'Access has an ROI' that doesn't not mean that ADP is not
a _BETTER_ ROI.

I don't think that there is a leap involved with moving to SQL Server.
You kids are stuck with Robinson Crusoe-- stuck on a deserted island--
but you guys have _PLENTY_ of motorboats that are tied up to the dock.

Why don't you guys hop in a boat? And cruise back to the mainland?

The powerboats are _FREE_; completely safe; and more reliable, secure,
faster-- there is no way than an island without _ANY_ amenities can
compete with a great motorboat and getting back home.

Performance is within your grasp.

ADP is a _GREAT_ tool for you jet kids to graduate from the 1st grade
of the database world.

Sorry--
I don't need to post code examples. I post helpful functions; helpful
routines. 9 times out of 10-- there are 3 or 4 ways to accomplish
something in SQL Server.
Does that make it _HARDER_TO_USE_?

Personally- I think that a database that gives this is harder to use:
- limited scalability
- even Tony - the most delusional of you all- agree that Access can't
handle more than 12 or 15 people editing / entering data.
- performance
- indexing. Access indexing is not 1/10th of the indexing in SQL
Server. For starters-- there are tools in SQL Server to help you
_TUNE_ indexes.
- ETL - DTS, SSIS, Informatica.. _PLUS_ all of the ETL that you
currently have in MS Access?
What do you actually THINK that I gave up DoCmd. when I moved to
ADP? rofl
- Reporting Services - included with the workgroup edition; there is
also a lot of functionality that is included with the express
edition. This is-- by far-- 100 times better reporting than MS
Access.
- Analysis Services - give people a pivotTable; let them get all the
data they want. Subsecond response times with TERABYTES of data.
- Stored Procedures - sprocs are 10000000 times more powerful than
Access queries.

I'll post statistics... 2,000,000,000 webpages that say that SQL
Server is faster than Jet.
In fact-- I've already posted them-- you just need to find them.
http://www.google.com

Thanks

-Aaron

 

[James A. Fortune]

just because 'Access has an ROI' that doesn't not mean that ADP is not
a _BETTER_ ROI.

First, I must warn you that you are in danger of getting involved in an
intelligent discussion, at least from one perspective. That being said,
what you were trying to say is a valid point.

I don't think that there is a leap involved with moving to SQL Server.
You kids are stuck with Robinson Crusoe-- stuck on a deserted island--
but you guys have _PLENTY_ of motorboats that are tied up to the dock.

Why don't you guys hop in a boat? And cruise back to the mainland?

The powerboats are _FREE_; completely safe; and more reliable, secure,
faster-- there is no way than an island without _ANY_ amenities can
compete with a great motorboat and getting back home.

I'll give you an example of what I meant. I have a SQL Server 2000
System Table Map poster. It has the names and schemata of 128 system
tables that are used by SQL Server. That suggests that SQL Server is
"high maintenance" compared to Access, which seems to foster a "cheap
date" mentality for Access. SQL Server also involves learning more than
you need to learn in order to utilize JET so that one may take advantage
of what it can optimize. There are times when "high maintenance" is
really useful and times when it is not.

Performance is within your grasp.

Even if an Access developer switches to SQL Server immediately, there
won't be much of a performance boost until she learns enough to take
advantage of SQL Server's strengths.

ADP is a _GREAT_ tool for you jet kids to graduate from the 1st grade
of the database world.

As I said in another post. JET is Kindergarten. SQL Server is first
grade .

Sorry--
I don't need to post code examples. I post helpful functions; helpful
routines. 9 times out of 10-- there are 3 or 4 ways to accomplish
something in SQL Server.
Does that make it _HARDER_TO_USE_?

Someone who writes functions in T-SQL might not think of them as code.
Routines certainly seem to be code. Without examples of how to utilize
SQL Server's strengths, Access with SQL Server won't be much better than
JET.

Personally- I think that a database that gives this is harder to use:
- limited scalability

That is not an issue that affects ease of use, per se.

- even Tony - the most delusional of you all- agree that Access can't
handle more than 12 or 15 people editing / entering data.

Your conclusion here along with the ad hominem argument is quite weak.

- performance

If you mean speed, I disagree with the proviso that advanced SQL Server
optimization is excluded from the statement temporarily. If you mean
better at avoiding corruption or mean better at dealing with lots of
concurrency then I agree.

- indexing. Access indexing is not 1/10th of the indexing in SQL
Server. For starters-- there are tools in SQL Server to help you
_TUNE_ indexes.

That's a great feature. What is the procedure you follow in tuning
indices? Or does the SQL Server index tuner choose all the indices for you?

- ETL - DTS, SSIS, Informatica.. _PLUS_ all of the ETL that you
currently have in MS Access?

Maybe it would be good for you to enlighten us Access neanderthals about
what situations are most improved by those tools. It might cause some
of us to start using SQL Server much sooner than we had planned.

What do you actually THINK that I gave up DoCmd. when I moved to
ADP? rofl

DoCmd never entered my mind. What feature aren't you giving up by
keeping DoCmd that will ease my transition to SQL Server?

- Reporting Services - included with the workgroup edition; there is
also a lot of functionality that is included with the express
edition. This is-- by far-- 100 times better reporting than MS
Access.

Access reporting works for most of my needs. When I need something
better I simply create pdf's on-the-fly. What functionality of
Reporting Services did you find to be the nicest?

- Analysis Services - give people a pivotTable; let them get all the
data they want. Subsecond response times with TERABYTES of data.

Perhaps present a situation where Analysis Services shines or creates
such speed improvement.

- Stored Procedures - sprocs are 10000000 times more powerful than
Access queries.

I did not find them to be so. To which powerful features are you referring?

I'll post statistics... 2,000,000,000 webpages that say that SQL
Server is faster than Jet.
In fact-- I've already posted them-- you just need to find them.
http://www.google.com

I don't disagree with your "statistics." I just find the way you
present your argument here to be mildly entertaining. I, for one, am
not impressed by your bandwagon argument, at least not positively impressed.

James A. Fortune
(e-mail address removed)

Often statistics are used as a drunken man uses lampposts - for support
rather than illumination. -- Trevor Best

 

[Pete D.]

Kid, that was so sweet of you, finally a complement. I use the tools that
are the easiest to use to meet the requirement in the situation at hand. I
have done databases in Assembly, COBOL, C, Java, Access, SQL, VB, and even
dabbled in Ada oh, did I leave out the punch cards and IBM Word Processor.
I am not a professional but with the exception of the punch cards, totally
self taught using the books/WEBs of others. This is why I can jump in and
talk like an expert in one area of Access and an idiot in other areas. I
learned what was needed at the time. No one here has ever said SQL wasn't
more powerful that Jet but for the workgroup, home user, time, money and
frontend only a fool would ignore the power and simplicity. The only limit
is the imagination and whether you like it or not MS is very forthcoming
with most problems and workarounds for them. What they miss...I jump in
here and find the answer and it is not to S&*t can it all and start over
with SQL. You do have some great ideas so why you insist on not being
helpful other than to say something totally un-needed to the situation is
outside the realm of my understanding. I wait with hope that someday you
will say, Tony is right and has a good idea, try adding this to what he
suggested. Until that day I can only assume that what you're selling is a
wooden nickel which doesn't add much value to the conversation.





message
wow.. sounds like an Access/JET bug... and another kid making excuses.
Is anyone else keeping track of this?

Microsoft isn't commited to fixing bugs in JET. They have a 'real
database' or twelve to concentrate on.

-Aaron