Access Project - ADP (subform)

[Khalid_Afridi]

Hello Friends,

I am in process to upsize Access 2010 database to SQL Server 2008R2. The This was a back-end database on network and was linked to some front end applications, now as database become HUGE and the users increased the database need to transfer to SQL server. Database is up-sized successfully except two tables. "Overflow Error Message" appear.

Second, I need to change the Front End Applications to ADP project, I changed some forms with straight data i.e with no "sub-forms' and they are working fine, the problem starts with the Main Form which have some sub-forms with calculated fields, i.e the "SOFID" on the main form refer to the sub-form (continuous form) with Master-child key relationship.

The main form become freeze and subform becomes blank.

Any suggestion

Thanks

 

[Bilbo_Baggins_Esq]

Please provide more specifics on the exact error.
There should be an error code with perhaps specific verbiage as well as the steps to repro (are you getting the error in Access or when opening views directly in SQL)?

your subforms should be able to be updated in the same way your other forms were.
If you open the subforms directly in design view they link to tables the same as main forms.
If you have unbound calculated controls, certainly you will need to relink all of your bound controls to the new data locations first.

You may also need to look at your form(s) code to see if any record sets were being pulled into memory from the old backend.
If so, you of course will need to fix that code and that will more than likely require new connection provider and protocols.

 

[Khalid_Afridi]

Thanks Bilbo

are you getting the error in Access or when opening views directly in SQL)?

There is no error in SQL, tables and stored procedures work fine. Problem starts in ADP form, I remove all Access default connection for DAO, and changed them to ADODB recordset etc in codes.

The main form is bound to a huge table 'tblSOF' with more than 250,000 records. There are two subforms attached to main form.

1st subform is based on a Access query (now a SQL stored Procedure) with calculated fields and a combobox.

2nd subform is also based on lot of combos but with a single table.

Both the subforms have a unique master "SOFID" field which are linked to their corresponding child fields in the subforms.

I removed all the subforms datasources and leave the main form unbound to any data.

Another problem is that the Access query 'qryQty' which is now SQL stored procedure 'QryQty_sp' is not update able. I made this a prameterized SQL stored procedure with "SOFID" pram and bound it to one of the subform in the main form but its not updating inserting any new record.

 

[JHB]

Another problem is that the Access query 'qryQty' which is now SQL stored procedure 'QryQty_sp' is not update able. I made this a prameterized SQL stored procedure with "SOFID" pram and bound it to one of the subform in the main form but its not updating inserting any new record.

Are you able to edit the data there are in it?
Below is a link to someone who had more or less the same problem as you and got it solved, missing primary key and BIT Data Type field.
http://www.access-programmers.co.uk/forums/showthread.php?t=229836

 

[Khalid_Afridi]

Thanks JHB

The link says that they don't have the primary key one of the table and by creating the primary key and re-creating the table have solved the problem.

In my case I have two tables (tblQty and tblItem) with INNER Join.
'ItemID' comes from 'tblItem' in 'tblQty' as foreign key and there is no issue with primary keys in both tables, It allows the new data in 'tblQty' in SQL server.

I have created a stored procedure with some calculated fields to join the two tables as it was in Access table. This stored procedure don not allow data to insert.
It is just a select stored procedure with a parameter on 'SOFID' to get some specific rows on certain ID to filter for new entry. Its a SELECT stored Procedure with parameter.

How can I make it to add new record?

USE [Service_Contracts]
GO
/****** Object: StoredProcedure [dbo].[qryQty_sp] Script Date: 01/05/2014 14:28:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[qryQty_sp](@SOFID int)
AS SELECT
dbo.tblQty.QtyID, dbo.tblQty.SOFID, dbo.tblQty.ItemID, dbo.tblQty.EmpId, dbo.tblItem.ItemNo,
dbo.tblItem.ItemDesc, dbo.tblItem.Unit, dbo.tblQty.Qty,
dbo.tblItem.UnitRate, ROUND(ROUND(dbo.tblQty.Qty, 3) * ROUND(dbo.tblItem.UnitRate, 3), 3) AS Total,
dbo.tblQty.PreQtyCompleted,
dbo.tblQty.TotQtyCompleted, ROUND(dbo.tblQty.TotQtyCompleted - dbo.tblQty.PreQtyCompleted, 3) AS NetQty,
ROUND(ROUND(dbo.tblItem.UnitRate, 3) * (dbo.tblQty.TotQtyCompleted - dbo.tblQty.PreQtyCompleted), 3) AS NetTotal
FROM
dbo.tblItem INNER JOIN dbo.tblQty ON dbo.tblItem.ItemID = dbo.tblQty.ItemID
WHERE(dbo.tblQty.SOFID = @SOFID)
ORDER BY dbo.tblItem.ItemNo

 

[JHB]

Only for troubleshooting I would take away the calculation and rounding part, to see if it makes any difference!

I'm still not sure if you can edit the data already recorded or not!
I know you can not add new data.

 

[Khalid_Afridi]

I'm still not sure if you can edit the data already recorded or not!
I know you can not add new data.

I can not edit already recorded data too, sample sub-form's screen shot is attached with 'qryQty_sp' retrieved data.

 

[JHB]

A stupid question. Is the main/sub form property "Allow Addition" and "Allow Edits" set to "Yes"?

 

[Khalid_Afridi]

Yes they are