CREATE TABLE [dbo].[tblCompany](
    [RefID] [INT] IDENTITY(1,1) NOT NULL,
    [CompanyName] [VARCHAR](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Amount] [DECIMAL](18, 2) NOT NULL CONSTRAINT [DF_tblCompany_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblCompany_1] PRIMARY KEY CLUSTERED
(
    [RefID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
CREATE TABLE [dbo].[tblDetail](
    [DetailID] [INT] IDENTITY(1,1) NOT NULL,
    [RefID] [INT] NULL,
    [Amount] [DECIMAL](18, 2) NOT NULL CONSTRAINT [DF_tblDetail_Amount]  DEFAULT ((0)),
 CONSTRAINT [PK_tblDetail] PRIMARY KEY CLUSTERED
(
    [DetailID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[tblDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblDetail_tblCompany] FOREIGN KEY([RefID])
REFERENCES [dbo].[tblCompany] ([RefID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblDetail] CHECK CONSTRAINT [FK_tblDetail_tblCompany]

Dim intID as Integer
 
adoconn.BeginTrans
with adoRsMaster
    .Open "Select * from tblCompany Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    addnew
    !CompanyName="Sample"
    !Amount =2500
    .update
intID = !RefID
end with
with adorsDetail
    .Open "Select * from tblDetail Where 1=2",adoconn,adOpenDynamic,adLockOptimistic   
    .addnew
    !RefID = intID
    !Amount = 2500
    .update
end with
adoconn.commitTrans

INSERT INTO tblCompany (CompanyName, Amount) VALUES ('Sample', 2500)
DECLARE @id INT
SET @id = Cast(SCOPE_IDENTITY() as INT)
 
INSERT INTO tblDetail (RefId, Amount) VALUES (@id, 2500)

adoID.Open "Select Max(RefID) from tblCompany", adoConn, adOpenForwardOnly, adLockReadOnly
iRefID = adoID(0).Value