|
From my experience, I can see absolutely no reason to have that many columns. I got to look at BPCS, that the company used, and it was an absolute nightmare of a design, that I replicated some of with well-designed relational tables. I doubt I ever used many more than ten columns per a table, because there was absolutely no reason to do so if you normalize the data. But maybe I'm missing something.
|
|
|
|
|
In a properly designed 3rd normal form database, you are correct. You'd never get to 255 columns.
I can only think of one situation where it would be useful to have >255 columns and that is denormalizing a dataset for ease of reporting. For example a clinical-trials reporting database.
|
|
|
|
|
I used to think the same thing. I would never build a table with that many columns.
But I have run into a few tables along the way that were very wide.
NPPES, National Plan and Provider Enumeration System allows the downloading of the NPI database as a csv file. Once a month I download and import this file into SQL for internal application viewing access.
The structure is 329 columns/fields by 7 million or so rows.
That is a lot of columns!
|
|
|
|
|
Named 'COL1', 'COL2', etc... probably. 
|
|
|
|
|
YUP!
Here is an example. Starts at 1. There are several other numbered fields:
Other Provider Identifier Issuer_50
|
|
|
|
|
. I mean 
|
|
|
|
|
David O'Neil wrote: Is there something I'm just unaware of, not having been involved in DB development work other than experiences like the above?
Perception.
Many think of Access as a toy, so if they're going to spend any serious amount of time putting together some system that needs a DB, they're not going to want to mention in their resume they did it with Access.
My opinion anyway.
[Edit]
Oh...and when I say "my opinion"...I mean, that's my opinion of how it's perceived. Not necessarily my opinion of Access's usefulness.
modified 21-Feb-22 9:06am.
|
|
|
|
|
I'm using MS Access since > 20 years for my private DBs and it does a good job for me, but:
What may happen if you use it in a bigger company and one of the other limitations of MS Access become(s) a bottleneck?
Limits:
- Total size for an Access database (.accdb or .mdb), including all database objects and data: 2 gigabytes, minus the space needed for system objects.
- Total number of objects in a database: 32,768
- Query Recordset size: 1 gigabyte
and many more as shown here:
Access specifications[^]
|
|
|
|
|
This smells like "don't allow access to grow, it will eat into SqlServer" restrictions.
So, max size is limited to a 32 bit value and # of objects is a word. Hmmm. I don't do databases any more but what is an object in these terms? 
Charlie Gilley
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
Has never been more appropriate.
|
|
|
|
|
Objects in Access include: table; query; form; report. There are more, essentially everything inside.
|
|
|
|
|
well, okay, I understand. But having lived in a company that had really smart people and killed off by marketing, it still smells to me like a marketing issue....
with the # of updates of products, Microsoft should have no issue of updating access instead of rounding corners for notepad. yes, I'm tossing the total bullshit flag
Charlie Gilley
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
Has never been more appropriate.
|
|
|
|
|
Jo_vb.net wrote: What may happen if you use it in a bigger company and one of the other limitations of MS Access become(s) a bottleneck?
In situations like this, the wrong tool is being used, like using a 5 oz finishing hammer for framing or roofing. MS Access is perfect for small applications, especially in small companies that do not need the infrastructure necessary to run SQL Server or Oracle.
Situation like that are the opposite, using a 32 oz roofing hammer to drive finishing nails.
Use the right tool for the right job.
|
|
|
|
|
I've been poking around YouTube, trying to find whether SQL has Access's query design interface/tool/whatever. Designing queries in Access seems MUCH easier than SQL's offering, but maybe I'm not using the right search terms.
|
|
|
|
|
David O'Neil wrote: Designing queries in Access seems MUCH easier than SQL's offering
It irritates me every time I use the query designer in SSMS...take the time to arrange tables/objects in the designer, run the query, return to the designer and find that everything is back to being stacked vertical...it can't even remember the last window size ffs! At least the Access designer remembers the layout. Additionally, because I need the same queries to work in both Access and SQL Server, I need to design in Access as table/view joins designed in Access will always work in SQL Server, but not the other way around. Oh yeah, and I also prefer Access < 2007. (.mdb)
I've ranted about this before, but you'd think that the SSMS team would put a little more effort into the query designer. Every update nag I get, I wonder 'have they fixed it?' and every update I'm dissappointed! 
"Go forth into the source" - Neal Morse
"Hope is contagious"
|
|
|
|
|
|
obermd wrote: Access is the hidden gem in Office
And was also the most misused tool. Entire business logic applications written in VBA / VB6 with a self contained .mdb prone to get farked every week.
GCS d--(d-) s-/++ a C++++ U+++ P- L+@ E-- W++ N+ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
|
|
|
|
|
David O'Neil wrote: Everyone seems to sh*t on Office and VBA, but when used correctly it is TREMENDOUSLY POWERFUL! A small business could be ran with it pretty easily
My brother works in a multinational corporation (I think they are the fourth biggest in their field) and most of their internal monthly accounting is made in Excel, assisted by VBA macros for the more complex things (like reading the detailed results of the month and filling out the official accounting software mandated by the different governments). But you haven't heard that from me 
A long time ago (1995, I think), when I was starting to program, I built a DB with GUI in Access for a library. At that time Access allowed to build GUIs, do not know if it still does. On delivery, the guy who hired me commented "This is all in Access? It looks great."
|
|
|
|
|
ElectronProgrammer wrote: At that time Access allowed to build GUIs, do not know if it still does.
2010 does, and I bet the newer versions also do. Haven't played with them.
|
|
|
|
|
Even 2021 does support the GUIs! I have Access 'apps' that I started in the 1990s and they have had minor tweaks (e.g. going from Access 2 to Access 97 was tricky as they changed the default Recordset class, some error nos altered) but apart from that, they work fine. The default 'style' for forms change between releases, but old forms still work without modification.
I have used Access in multi-user mode; admittedly, the multi-users were web users and the web app was the only 'real' user using the database (apart from a maintenance user). That ran from 1997 to well into the 2000s.
|
|
|
|
|
jsc42 wrote: Even 2021 does support the GUIs! I have Access 'apps' that I started in the 1990s and they have had minor tweaks
Same here -- I have several databases started in Access V2 which have migrated through every version up to the current one. VBA has been expanded but most of the original code works.
Beyond Access, my MS Word Normal.dotx contains macros that have run, unchanged, since 1995. This reinforces how bad the constant language churn is for the front line languages.
|
|
|
|
|
Access is a tool. Like any other tool, it has its purpose. Use it for the wrong purpose and one is destined for failure.
The reason most poo-poo on Access is because for most, it is not the correct tool for the job, SQL Server, Oracle, etc. are the correct tools for the job.
and yes, if you have a table that needs more than 255 columns, then you have much bigger problems on your hands than Access.
|
|
|
|
|
I used Access since it first came out. I still do at times. I switched to SQK Server after it came out, mostly because of the 32767 limit.
Access is a tool. Like any other tool, it has its purpose. Use it for the wrong purpose and one is destined for failure.
I agree with you one hundred percent! the same can be said for, and is equally true about VB in ALL of it's forms from VB6 to the latest version of VB.net. The same can be said for other languages, for that matter. I'm looking at you, Griff. Just because you like one language better than others, does not give you the right to rundown the others.
ED
|
|
|
|
|
Slow Eddie wrote: I agree with you one hundred percent! the same can be said for, and is equally true about VB in ALL of it's forms from VB6 to the latest version of VB.net
Visual Basic was a revolutionary language that completely changed the programming landscape. I recall trying to develop GUIs prior to VB ... sheesh!
Look at the "popular languages" lists -- regardless of the ranking criteria, Classic VB is often still in the top 20. Personally, this surprises me, but it's oddly true.
Folks complain about the bad applications written in VB ... I've had to deal with far more crap code in C, C++, C#, and Java. As a mentor said, it's a poor craftsman who blames the tools for his lack of skill.
|
|
|
|
|
I too love Access, for the right sort of jobs.
However the problem that I often see is when a non-programmer builds a simple solution with Access. At first, no big deal. But then over time, new functionality gets patched on, Now fast forward a few years, and you have this behemoth of an app, built with loads of spaghetti-style code, and no one really knows how it works anymore. Now they turn to the professional developers to fix things in it or add yet more functionality, and we are left with this Ball of Mud to sort out.
One company I worked with had one such Access DB, with multiple forms and reports, that had become crucial to their business over the course of almost 20 years of patching. They even had one-off versions with for one or two people with different formulas for estimations of pricing. And full of these little bugs that they wanted fixed. They thought upgrading to a new version of Access (they were still on Office 2003) would fix their problems....
Long story short - like any tool, you need to use it properly. Programming in Access is like programming in any other language, and needs some forethought for anything beyond trivial solutions.
|
|
|
|
|
Andreas Mertens wrote: They even had one-off versions with for one or two people with different formulas for estimations of pricing
Ungh!!! That's Rough, Buddy!!!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
