I've seen many say exceeding, or approaching, 255 columns is bad form, but no reference to good form. I split my inspection app into multiple tables, but still, when uploading inspections to SQL Server, need to join all tables with all data together. I have 264 inspection points and all must be reported as checked.
6 1231 zmbd 5,501
Recognized Expert Moderator Expert
An SQL server with 200+ columns/fields in a table?
I know that 1000+ is possible; however, I've never seen one and I have a feeling that the maximum bytes/record limit would be hit very quickly - even if using a wide-table and a 64bit install ( Maximum Capacity Specifications for SQL Server).
Do you mean records (rows) and not fields (columns)?
If this design is true( :EEK: ) , then there are some deeper issues with both designs that may well be beyond what we normally work with!
I think we need to have a better understanding of how your tables on both sides are designed before we can really be of any help.
Fields. I inherited this project: develop portable database for inspecting the state's dams based on an existing web based database app. Problem is it doesn't work at dams with no internet access, so this app must run on a Surface Pro. There are 260+ inspection points that the government requires. Once inspector is in range of wireless or 4G, they upload/merge inspection data to existing SQL server. There are now an additional dozen fields/columns that need to be added/tracked, so it's now at 270 fields/columns plus photos.
I'm thinking separate tables, then merge each to main in batches. Still need to figure out how to merge 10 tables into one record on one SQL database table. Also wondering if SQL's localDB may be a better backend than accdb
NeoPa 32,568
Recognized Expert Moderator MVP dlwyer:
I'm thinking separate tables, then merge each to main in batches.
I'm thinking design a table where one field represents the ID of the Inspection Point. That way the data isn't in a {Insert preferred expletive here} mess.
You may want to look at Database Normalisation and Table Structures to see why we're steering you so strongly away from the current approach. I appreciate that you've inherited this, but you won't do yourself any favours trying to proceed on the original lines.
jforbes 1,107
Recognized Expert Top Contributor
I've worked on some occasionally connected inspection software in past and we found it easiest to define a couple tables.
A Header Table something like this: InspectionID - AutoNumber Field SerialNumberOfT estItem - String to search and report on InspectionDate - the Date
A Detail Table to store the Results into: InspectionID - Linking back to the Header Table InspectionOrder - The Order in which to perform the Inspection ReadingType - The Type of Reading/Test to perform Reading - The Actual Results found
There was a lot more to it, like a Form to define a specific type of Reading/Inspection as well as a Form to group these into a Template for a Type of Inspection, like for a specific Model. But the basic principal is to have multiple records in the Detail Table linked to the Header table. Then you wouldn't need to worry about how many test/inspection points there are because each one would just be another Row in the Table and not a Column.
As to SQL Express or Access. Either could work. Access as the Client UI will probably be the quickest way to develop the application, but it would also require a license per user. If you have the time, you can get a copy of Visual Studio for free. I think they are currently calling it Community Edition. With it, you can use both Access or SQL Express as a database. I think in that scenario, you will need to install a client driver for SQL Express, but you wouldn't for an Access Database. I'm probably missing something on this, but that is the scoop to the best of my memory.
Thank-you. The linked information proved to be a helpful starting point.
NeoPa 32,568
Recognized Expert Moderator MVP
It never hurts to cross-post JForbes. Especially when both are essentially giving the same message. The important thing is that the OP got the message.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: K Gibbs |
last post by:
Lets say I have two threads which "communicate" via a shared object (passed
to them as a reference when they are created). The shared object is primarily
composed of getters and setters. In quite a few instances the two threads need
access to both types of methods. However, in some only one of the threads
should access the setter method. Eg, the control thread should be the only one
able to call the method set_LimitSwitchStautus(true);...
|
by: Victoria Holowchak |
last post by:
I was hoping that Microsoft Access 2002 would allow users to see all
the columns of an Oracle database table that contained more than 255
columns. I noticed that my Oracle8 ODBC driver is only at 8.1.6.0.0
for win95, win98 and winNT. If I upgraded my ODBC driver would Access
be able to recognize the columns beyond the 255th column? or is this
an Access limitation?
Please don't lecture me about good table design and recommend
splitting...
|
by: Bas Hamer |
last post by:
So someone mentioned that there is a column limit in the .Net framework when
you are working w/ datasets. Now I'm pretty sure that my data sets will have
less than 400 columns, but it will proabably be more than 256 columns. So I'm
crossing one of the magical computer boundaries.
Can someone tell me if this is really an issue, and if this is on thier
radar to fix ?
|
by: curious_Lee |
last post by:
Hi, all. We're running Windows 2003 Enterprise Edn. with IIS 6. We'd like to
limit the access to two of our ASP apps to those who are on the domain --
without using the integrated domain authentication popup. In other words, we
don't want users on the domain to have to log in every time (or even once);
those not on the domain would not see the site at all. Is this possible?
How? Thanks.
|
by: John Mark Howell |
last post by:
I'm trying to generate a typed dataset from an XSD, yet if I add more than
90 columns in the XSD I don't see any of the public methods on the type
datatable object. I can replicate this easily and consistently in several
projects. Is this a known limit?
--
John Mark Howell
Blog: jmarkhowell.blogspot.com
| |
by: Nosferatum |
last post by:
I am in need of a solution on how to solve this problem:
I need to limit access to six different folders. My users are
validated in a system which check their prescence with a couple of
variables in a db and then forwards them if they exist. Based upoen
their status they are redirected to one of six folders.
Users belonging to group A shall get access to folder A, but not B, C
etc. It must be possible to limit access in this order by...
|
by: meLady |
last post by:
Hello,
I have a form called feedback which has three columns and each cell in a column has a combo box that holds (Yes/No/Empty), and I want to count each column's value for example let's supposed that:
feeback number: Column 1 (Are You happy) - Column 2 (Are you sad) - Column 3 (Are you hungry)
1 : Yes - No - Yes
2 ...
|
by: Nosferatum |
last post by:
Hi, on my Apache server I want to limit access to a certain file ouput
(from php/MySQL) to just one IP. The idea is that users from another
site should click a link whic redirects them to my special page on my
server. Only those who access my page from one particulary URL are
allowed to see my file. All others are denied.
Is it possible to solve this with a .htaccess file, or do I need a php
solution?
|
by: Drew |
last post by:
I am trying to limit access to certain pages on our intranet, and have been
using the following code to do so,
dim Login, L, LL, StringLen, NTUser
Set Login = Request.ServerVariables("LOGON_USER")
L=Len(Login)
LL=InStr(Login, "\")
StringLen=L-LL
NTUser = (Right(Login, StringLen))
|
by: rhonda2010 |
last post by:
Hi, I hope you can help me. I have a program I am developing and I want to be able to limit the time or the amount of times a user can access it. Someone I know wants to try the program out; however, I do not want them to have full access to it until we have come to an agreement. Is there a way to limit the amount of times or the timeframe the program can be run? For example, allow the program to be run 50 times or until a certain date.
I...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |