 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / April 2008Too many fields
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  Signaturedeb 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. [quoted text clipped - 27 lines] > > Thanks 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 On Apr 11, 1:25 pm, Golfinray <Golfin...@discussions.microsoft.com> wrote: > 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 [quoted text clipped - 36 lines] > > - Show quoted text - "a a r o n . k e m p f @ g m a i l . c o m" <aaron.kempf@gmail.com> wrote: >b*****t w***e Your use of foul language and name calling is exceedingly obnoxious. Tony SignatureTony 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/ > ... > [quoted text clipped - 7 lines] > 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 MPAPoster@FortuneJames.com 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 >> ... >> [quoted text clipped - 35 lines] > James A. Fortune > MPAPoster@FortuneJames.com 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 > 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 [quoted text clipped - 43 lines] > > - Show quoted text - 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. 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 On Apr 13, 3:42 pm, "Pete D." <pduffy21...@coxDOT.net> wrote: > 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 [quoted text clipped - 53 lines] > > - Show quoted text - 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) On Apr 12, 8:44 pm, "James A. Fortune" <MPAPos...@FortuneJames.com> wrote: > > ... > [quoted text clipped - 36 lines] > James A. Fortune > MPAPos...@FortuneJames.com > 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 [quoted text clipped - 16 lines] > 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 MPAPoster@FortuneJames.com 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." 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 On Apr 14, 12:12 am, "James A. Fortune" <MPAPos...@FortuneJames.com> wrote: > > I just think that it's _CUTE_ that you list an article for SQL 7.0 ;) > [quoted text clipped - 43 lines] > > - Show quoted text - > I just don't think that there is a single benefit to JET anywhere. > [quoted text clipped - 12 lines] > > -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 MPAPoster@FortuneJames.com 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. 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 On Apr 14, 10:06 pm, "James A. Fortune" <MPAPos...@FortuneJames.com> wrote: > > I just don't think that there is a single benefit to JET anywhere. > [quoted text clipped - 38 lines] > > - Show quoted text - > 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-- [quoted text clipped - 5 lines] > 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 MPAPoster@FortuneJames.com Often statistics are used as a drunken man uses lampposts - for support rather than illumination. -- Trevor Best I love Bill Murray. Stripes is one of those classic movies. Thanks for ligthening the situation ;) This newsgroup needs more humor. -Aaron On Apr 14, 12:12 am, "James A. Fortune" <MPAPos...@FortuneJames.com> wrote: > > I just think that it's _CUTE_ that you list an article for SQL 7.0 ;) > [quoted text clipped - 43 lines] > > - Show quoted text - > 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 MPAPoster@FortuneJames.com 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 >I found this article from Douglas Steele... > Query that will update or insert as required. [quoted text clipped - 30 lines] > > Thanks 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. [quoted text clipped - 54 lines] > > - Show quoted text - |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.