Question Problem adding Columns to a Table (1 Viewer)

[MSAccessRookie]

I am having issues with a database that uses MS Access 2003 as its Front End, and MS SQL Server 2005 as its Back End, and because of this fact, as well as the issues below, I am not sure which of the Forums that this post best fits in:

• I have an issue where Compact and Repair causes errors in an unpredictable basis (so far at least).
• I have a couple of Forms that get an error when I modify them and then try to save them.

These two events are caused (I believe) by the Modification of an existing table (tblProducts) to add additional Columns. An interesting thing that I wanted to state is that these Forms do not use any of the new Fields in the modified Table.

• I have another Form that uses all of the new Fields in the modified Table, and has no problems with any functionality at all.

As a result of my problems, I have (for now) divided the Table into two Tables as a workaround. The first Table (tblProducts), contains all of the original Columns, and The second Table (tblProducts2), contains all of the newly added Columns. While this resolves all of the issues that were encountered, it does not address the following:

• Having two tables with the same Primary Key and a 1:1 relationship, violates design and normalization requirements for a database.
• Management wants the tables combined to be a single Table.

Attached is a SpreadSheet that contains the Table designs for tblProducts tblProducts2, as well as the design of the combined table.

 

[Fifty2One]

I had strange and seeming random error problems when the 'server guy' changed some of the security settings on the server and depending on which computer was being used the server would automatically archive a backup copy which would scatter the back end targets.

 

[MSAccessRookie]

I had strange and seeming random error problems when the 'server guy' changed some of the security settings on the server and depending on which computer was being used the server would automatically archive a backup copy which would scatter the back end targets.

Even though I thought your experience could not be related to mine, I checked with my "Server guy" and we have made no changes of any kind for several months. Thanks for that idea, and I await any other observations or suggestions.

 

[MSAccessRookie]

Update:

An SQL Server Programmer that I know suggested that I consider whether the Schemabinding of the Views was contributing to the problem. I removed all Schemabinding from the development database, relinked all of the tables, and I still have the same problems with Compact and Repair, as well as with modification of forms when I use the combined table.

Any further suggestions are welcome.

 

[LPurvis]

Problems such as
"Compact and Repair causes errors in an unpredictable basis... Forms that get an error when I modify them.."
Smack of corruption.
Gawd knows I hate to say it - I'd happily dig all day before writing off an issue to this, but it's worth considering early on when there are intermittant or application based issues.

I assume you've done the usual?
Export all to a new MDB, decompiled etc.
Have you considered a full export to text and then reimport? It's the most complete house cleaning exercise that you can implement.

Looking at the tables themselves, I assume that you've ensured that your Bit columns have a default value (0?) and are enforced to be Not Null? (I'd imagine that were they there at conversion time by the SSMA they would have been so created by default, but perhaps not added later?)

And you must obviously be refreshing the table links after you've made changes to them (as you can see those new fields ;-).

See how you go anyway.
For an example of text import/export see http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1405649

Cheers.