How to overcome column limit on Access dB uploading to SQL db

[dlwyer]

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.

[zmbd]

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.

[dlwyer]

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]

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]

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
SerialNumberOfTestItem - 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.

[dlwyer]

Thank-you. The linked information proved to be a helpful starting point.

[NeoPa]

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.