As a followup, let me clarify that I am using the Access form operations to
do my data modificaion. (i.e. there is no VBA code behind)
Here is the schema fo9r the affected tables.
CREATE TABLE [dbo].[question] (
[QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
[Status] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Answer_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hint_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C_Image] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C_Image2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceID] [int] NULL ,
[PageNo] [int] NULL ,
[SourceQuestionNo] [int] NULL ,
[LevelID] [int] NULL ,
[Issue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Compound] [bit] NULL ,
[CreateDate] [datetime] NULL ,
[UpdateDate] [datetime] NULL ,
[Descripton] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ToDo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Text] [bit] NULL ,
[HP1] [bit] NULL ,
[HP2] [bit] NULL ,
[Excel] [bit] NULL ,
[Hint] [bit] NULL ,
[Question] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AnswerDetail] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HP12C] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LevelNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[solution] (
[solutionid] [int] IDENTITY (1, 1) NOT NULL ,
[questionid] [int] NULL ,
[solution] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[question] ADD
CONSTRAINT [DF__question__Status__33D4B598] DEFAULT (1) FOR [Status],
CONSTRAINT [DF__question__Source__34C8D9D1] DEFAULT (5) FOR [SourceID],
CONSTRAINT [DF__question__LevelI__35BCFE0A] DEFAULT (1) FOR [LevelID],
CONSTRAINT [DF__question__Compou__36B12243] DEFAULT (0) FOR [Compound],
CONSTRAINT [DF__question__Create__37A5467C] DEFAULT (getdate()) FOR
[CreateDate],
CONSTRAINT [DF__question__Text__38996AB5] DEFAULT (0) FOR [Text],
CONSTRAINT [DF__question__HP1__398D8EEE] DEFAULT (0) FOR [HP1],
CONSTRAINT [DF__question__HP2__3A81B327] DEFAULT (0) FOR [HP2],
CONSTRAINT [DF__question__Excel__3B75D760] DEFAULT (0) FOR [Excel],
CONSTRAINT [DF__question__Hint__3C69FB99] DEFAULT (0) FOR [Hint],
CONSTRAINT [aaaaaquestion_PK] PRIMARY KEY NONCLUSTERED
(
[QuestionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[solution] ADD
CONSTRAINT [DF__solution__questi__46E78A0C] DEFAULT (0) FOR [questionid],
CONSTRAINT [aaaaasolution_PK] PRIMARY KEY NONCLUSTERED
(
[solutionid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[question] ADD
CONSTRAINT [FK_question_level] FOREIGN KEY
(
[LevelID]
) REFERENCES [dbo].[level] (
[LevelID]
),
CONSTRAINT [FK_question_source] FOREIGN KEY
(
[SourceID]
) REFERENCES [dbo].[source] (
[SourceID]
)
GO
ALTER TABLE [dbo].[solution] ADD
CONSTRAINT [solution_FK00] FOREIGN KEY
(
[questionid]
) REFERENCES [dbo].[question] (
[QuestionID]
)
GO
---------------------------
And here is the record source for the form::
SELECT question.QuestionID, question.Answer, question.Answer_Image,
question.Hint_Image, question.HP12C_Image, question.HP12C_Image2,
question.SourceID, question.PageNo,
question.SourceQuestionNo, question.UpdateDate, question.Question,
question.AnswerDetail, solution.solution,
question.HP12C, question.Compound, question.LevelID,
question.LevelNote, question.Issue
FROM question INNER JOIN
solution ON question.QuestionID = solution.questionid;