Upsized Database - uneditable control source on form

[jpatchak]

Hello all,

I recently tried upsizing a DB with the Access Upsizing Wizard. I ran into the standard problems with datatypes and have to rebuild some queries that didn't get upsized. I am having a problem with one of my forms, however. The control source of the form is a query based on two tables that are in a one-to-one relationship. Each table's recordset is editable. However, the query's recordset is not. Thus, my form is not working correctly. Any tips on making an upsized query's recordset editable?

Thanks,
Josh

PS I need to change some of by code in another form from DAO to ADODB. Anyone aware of any good online resources so I can learn some of the ADODB commands?

[nico5038]

The standard solution to this problem is to use a (datasheet or (single) form) subform with the related recordset.
It's always risky to have a JOINed query as a form's recordsource.

Nic;o)

[NeoPa]

Can you post the SQL for the query and the MetaData for the tables.
Here is an example of how to post table MetaData :
Posting Table/Dataset MetaData

[b]Table Name=tblStudent[/b]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

I'm curious about your question Josh. I don't know much about upsizing, I must admit, but assuming it follows the normal rules then I'm surprised a linked pair of tables would not be updatable.

[NeoPa]

PS I need to change some of by code in another form from DAO to ADODB. Anyone aware of any good online resources so I can learn some of the ADODB commands?

I would really just use the Help system. There's whole heaps of info in there.

[jpatchak]

Can you post the SQL for the query and the MetaData for the tables.
Here is an example of how to post table MetaData :
Posting Table/Dataset MetaData

[b]Table Name=tblStudent[/b]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

I'm curious about your question Josh. I don't know much about upsizing, I must admit, but assuming it follows the normal rules then I'm surprised a linked pair of tables would not be updatable.

Here is the SQL, as you can see from the the SQL from this view, the tables on which they are based have a lot of fields (150+), so posting the table Meta Data would be pretty lengthy. The primary key is for the [PR Only Info] table id [ID]. This is the foreign key in the [Final Action] table. The [Final Action] table also has a primary key called [PK] that was created just in order to make the table editable.

SELECT     dbo.[PR Only Info].ID, dbo.[PR Only Info].[Prospect Number], dbo.[PR Only Info].[List Name], dbo.[PR Only Info].Fname, dbo.[PR Only Info].LNAme, 
                      dbo.[PR Only Info].[Primary SS#], dbo.[PR Only Info].City, dbo.[PR Only Info].[Issue State], dbo.[PR Only Info].Smoker, dbo.[PR Only Info].Age, 
                      dbo.[PR Only Info].Gender, dbo.[PR Only Info].[Spouse Age], dbo.[PR Only Info].[Spouse Gender], dbo.[PR Only Info].[Spouse Smoker], 
                      dbo.[PR Only Info].[Child 1 Age], dbo.[PR Only Info].[Child 1 Gender], dbo.[PR Only Info].[Child 2 Age], dbo.[PR Only Info].[Child 2 Gender], 
                      dbo.[PR Only Info].[Child 3 Age], dbo.[PR Only Info].[Child 3 Gender], dbo.[PR Only Info].[Initial Pay Mode], dbo.[PR Only Info].[Medical Product], 
                      dbo.[PR Only Info].[Guaranteed Issue], dbo.[PR Only Info].[Option Rx], dbo.[PR Only Info].[Option Office Visit Copay], 
                      dbo.[PR Only Info].[Option Maternity], dbo.[PR Only Info].[Option Mental Health], dbo.[PR Only Info].[COINS In-Network], dbo.[PR Only Info].[COINT OON], 
                      dbo.[PR Only Info].Deductible, dbo.[PR Only Info].[Primary BMM] AS IQPrimaryBMM, dbo.[PR Only Info].[Primary Rx] AS IQPrimaryRx, 
                      dbo.[PR Only Info].[Primary OV Copay] AS IQPrimaryOVCopay, dbo.[PR Only Info].[Primary Maternity] AS IQPrimaryMaternity, 
                      dbo.[PR Only Info].[Primary Mental Health] AS IQPrimaryMentalHealth, dbo.[PR Only Info].[Spouse BMM] AS IQSpouseBMM, 
                      dbo.[PR Only Info].[Spouse Rx] AS IQSpouseRx, dbo.[PR Only Info].[Spouse OV Copay] AS IQSpouseOVCopay, 
                      dbo.[PR Only Info].[Spouse Maternity] AS IQSpouseMaternity, dbo.[PR Only Info].[Spouse Mental Health] AS IQSpouseMentalHealth, 
                      dbo.[PR Only Info].[Child 1 BMM] AS IQChild1BMM, dbo.[PR Only Info].[Child 1 Rx] AS IQChild1Rx, 
                      dbo.[PR Only Info].[Child 1 OV Copay] AS IQChild1OVCopay, dbo.[PR Only Info].[Child 1 Maternity] AS IQChild1Maternity, 
                      dbo.[PR Only Info].[Child 1 Mental Health] AS IQChild1MentalHealth, dbo.[PR Only Info].[Child 2 BMM] AS IQChild2BMM, 
                      dbo.[PR Only Info].[Child 2 Rx] AS IQChild2Rx, dbo.[PR Only Info].[Child 2 OV Copay] AS IQChild2OVCopay, 
                      dbo.[PR Only Info].[Child 2 Maternity] AS IQChild2Maternity, dbo.[PR Only Info].[Child 2 Mental Health] AS IQChild2MentalHealth, 
                      dbo.[PR Only Info].[Child 3 BMM] AS IQChild3BMM, dbo.[PR Only Info].[Child 3 OV Copay] AS IQChild3OVCopay, 
                      dbo.[PR Only Info].[Child 3 Maternity] AS IQChild3Maternity, dbo.[PR Only Info].[Child 3 Rx] AS IQChild3Rx, 
                      dbo.[PR Only Info].[Child 3 Mental Health] AS IQChild3MentalHealth, dbo.[Final Action].Fname AS FAFname, dbo.[Final Action].LNAme AS FALname, 
                      dbo.[Final Action].City AS FACity, dbo.[Final Action].[Issue State] AS FAIssueState, dbo.[Final Action].Zip AS FAZIP, dbo.[PR Only Info].Zip, 
                      dbo.[Final Action].Age AS FAAge, dbo.[Final Action].Gender AS FAGender, dbo.[Final Action].Smoker AS FASmoker, 
                      dbo.[Final Action].[Spouse Age] AS FASpouseAge, dbo.[Final Action].[Spouse Gender] AS FASpouseGender, 
                      dbo.[Final Action].[Spouse Smoker] AS FASposueSmoker, dbo.[Final Action].[Child 1 Age] AS FAChild1Age, 
                      dbo.[Final Action].[Child 1 Gender] AS FAChild1Gender, dbo.[Final Action].[Child 2 Age] AS FAChild2Age, 
                      dbo.[Final Action].[Child 2 Gender] AS FAChild2Gender, dbo.[Final Action].[Child 3 Age] AS FAChild3Age, 
                      dbo.[Final Action].[Child 3 Gender] AS FAChild3Gender, dbo.[Final Action].[Initial Pay Mode] AS FAInitialPayMode, 
                      dbo.[Final Action].[Medical Product] AS FAMedicalProduct, dbo.[Final Action].[Guaranteed Issue] AS FAGuaranteedIssue, 
                      dbo.[Final Action].[Option Rx] AS FAOptionRx, dbo.[Final Action].[Option Office Visit Copay] AS FAOptionOVCopay, 
                      dbo.[Final Action].[Option Maternity] AS FAOptionMaternity, dbo.[Final Action].[Option Mental Health] AS FAMentalHealth, 
                      dbo.[Final Action].[COINS In-Network] AS FAParCOINS, dbo.[Final Action].[COINT OON] AS FANonParCOINS, 
                      dbo.[Final Action].Deductible AS FADeductible, dbo.[Final Action].[Primary BMM] AS FAPrimaryBMM, dbo.[Final Action].[Primary Rx] AS FAPrimaryRx, 
                      dbo.[Final Action].[Primary OV Copay] AS FAPrimaryOVCopay, dbo.[Final Action].[Primary Maternity] AS FAPrimaryMaternity, 
                      dbo.[Final Action].[Primary Mental Health] AS FAPrimaryMentalHealth, dbo.[Final Action].[Spouse BMM] AS FASpouseBMM, 
                      dbo.[Final Action].[Spouse Rx] AS FASpouseRx, dbo.[Final Action].[Spouse OV Copay] AS FASpouseOvCopay, 
                      dbo.[Final Action].[Spouse Maternity] AS FASpouseMaternity, dbo.[Final Action].[Spouse Mental Health] AS FASpouseMentalHealth, 
                      dbo.[Final Action].[Child 1 BMM] AS FAChild1BMM, dbo.[Final Action].[Child 1 Rx] AS FAChild1Rx, 
                      dbo.[Final Action].[Child 1 OV Copay] AS FAChild1OVCopay, dbo.[Final Action].[Child 1 Maternity] AS FAChild1Maternity, 
                      dbo.[Final Action].[Child 1 Mental Health] AS FAChild1MentalHealth, dbo.[Final Action].[Primary Non-Build Rate], dbo.[Final Action].[Primary Build rate], 
                      dbo.[Final Action].[Spouse Non-Build Rate], dbo.[Final Action].[Spouse Build Rate], dbo.[Final Action].[Child 1 Non-Build Rate], 
                      dbo.[Final Action].[Child 1 Build Rate], dbo.[Final Action].[Child 2 Non-Build Rate], dbo.[Final Action].[Child 2 Build Rate], 
                      dbo.[Final Action].[Child 3 Non-Build Rate], dbo.[Final Action].[Child 3 Build Rate], dbo.[Final Action].[Child 2 BMM] AS FAChild2BMM, 
                      dbo.[Final Action].[Child 2 Rx] AS FAChild2Rx, dbo.[Final Action].[Child 2 OV Copay] AS FAChild2OVCopay, 
                      dbo.[Final Action].[Child 2 Maternity] AS FAChild2Maternity, dbo.[Final Action].[Child 2 Mental Health] AS FAChild2MentalHealth, 
                      dbo.[Final Action].[Child 3 BMM] AS FAChild3BMM, dbo.[Final Action].[Child 3 Rx] AS FAChild3Rx, 
                      dbo.[Final Action].[Child 3 OV Copay] AS FAChild3OVCopay, dbo.[Final Action].[Child 3 Maternity] AS FAChild3Maternity, 
                      dbo.[Final Action].[Child 3 Mental Health] AS FAChild3MentalHealth, dbo.[Final Action].[FPMC no Rateup]
FROM         dbo.[PR Only Info] INNER JOIN
                      dbo.[Final Action] ON dbo.[PR Only Info].ID = dbo.[Final Action].[Master ID]

[NeoPa]

It's starting to make some sense.
Would I be right in thinking that 'Upsizing' means replacing Access tables with links to MS SQL tables?
If so, then this will not work.
What you need to do is define a 'query' (term is not query but it does basically the same thing and I'm at home and can't remember the term :() on your SQL Server which links the two tables and returns the values you need. You then link to this from your Access database as if it were a table.

[jpatchak]

It's starting to make some sense.
Would I be right in thinking that 'Upsizing' means replacing Access tables with links to MS SQL tables?
If so, then this will not work.
What you need to do is define a 'query' (term is not query but it does basically the same thing and I'm at home and can't remember the term :() on your SQL Server which links the two tables and returns the values you need. You then link to this from your Access database as if it were a table.

I think the word you're looking for is "View." And I do have the view built on the SQL Server. The form is working in that it displays the correct information from the tables. However, I cannot make any changes with VB code or directly in the controls. If I try in VBA, I get an error message that says the recordset is not updateable. If I try in the form's controls, the same message appears in the status bar.

[NeoPa]

Yes indeed it is 'View'. Thank you.
You don't say that your form is built on the 'linked table' connected to the View you refer to?
Any JOINing in an Access query to MS SQL linked tables will result in a non-updatable recordset.
If linking to the View is also non-updatable then test if it is also that way on the SQL Server itself.
If it can work there, but can't when connected to Access (That would surprise me but it is possible) then you're in a hole I'm afraid. If it doesn't work on the SQL Server then you need to get that sorted and it's a SQL Server issue.

[jpatchak]

Yes indeed it is 'View'. Thank you.
You don't say that your form is built on the 'linked table' connected to the View you refer to?
Any JOINing in an Access query to MS SQL linked tables will result in a non-updatable recordset.
If linking to the View is also non-updatable then test if it is also that way on the SQL Server itself.
If it can work there, but can't when connected to Access (That would surprise me but it is possible) then you're in a hole I'm afraid. If it doesn't work on the SQL Server then you need to get that sorted and it's a SQL Server issue.

The control source of the form is the View that joins the tables. It works fine in Access, but not in SQL server after it was upsized. I think I'll try a subform as suggested above. If that doesn't work, I'll go and try the SQL Server discussion zone. Thanks for your help.

[NeoPa]

Sorry I couldn't be more help Josh.
I'm interested to know what the upsizing wizard does though. Are you connecting to SQL Server or are you porting everything to it. I wouldn't have thought it would handle forms in any meaningful way like Access does. If you are connecting across to SQL Server why is your SQL using an INNER JOIN rather than linking directly to the view? Obviously there's something I'm not quite 'getting' here :(.

[MMcCarthy]

The control source of the form is the View that joins the tables. It works fine in Access, but not in SQL server after it was upsized. I think I'll try a subform as suggested above. If that doesn't work, I'll go and try the SQL Server discussion zone. Thanks for your help.

If you link the tables using odbc or dsn etc. You can keep the query in Access. Unless you are moving the whole database into SQL Server, in which case you would probably get better help in the SQL Server forum.

Mary