By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Upsized Database - uneditable control source on form

P: 76
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?
Dec 10 '06 #1
Share this Question
Share on Google+
10 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 10 '06 #2

NeoPa
Expert Mod 15k+
P: 31,616
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
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. 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.
Dec 10 '06 #3

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 10 '06 #4

P: 76
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
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. 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.
Expand|Select|Wrap|Line Numbers
  1. 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, 
  2.                       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, 
  3.                       dbo.[PR Only Info].Gender, dbo.[PR Only Info].[Spouse Age], dbo.[PR Only Info].[Spouse Gender], dbo.[PR Only Info].[Spouse Smoker], 
  4.                       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], 
  5.                       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], 
  6.                       dbo.[PR Only Info].[Guaranteed Issue], dbo.[PR Only Info].[Option Rx], dbo.[PR Only Info].[Option Office Visit Copay], 
  7.                       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], 
  8.                       dbo.[PR Only Info].Deductible, dbo.[PR Only Info].[Primary BMM] AS IQPrimaryBMM, dbo.[PR Only Info].[Primary Rx] AS IQPrimaryRx, 
  9.                       dbo.[PR Only Info].[Primary OV Copay] AS IQPrimaryOVCopay, dbo.[PR Only Info].[Primary Maternity] AS IQPrimaryMaternity, 
  10.                       dbo.[PR Only Info].[Primary Mental Health] AS IQPrimaryMentalHealth, dbo.[PR Only Info].[Spouse BMM] AS IQSpouseBMM, 
  11.                       dbo.[PR Only Info].[Spouse Rx] AS IQSpouseRx, dbo.[PR Only Info].[Spouse OV Copay] AS IQSpouseOVCopay, 
  12.                       dbo.[PR Only Info].[Spouse Maternity] AS IQSpouseMaternity, dbo.[PR Only Info].[Spouse Mental Health] AS IQSpouseMentalHealth, 
  13.                       dbo.[PR Only Info].[Child 1 BMM] AS IQChild1BMM, dbo.[PR Only Info].[Child 1 Rx] AS IQChild1Rx, 
  14.                       dbo.[PR Only Info].[Child 1 OV Copay] AS IQChild1OVCopay, dbo.[PR Only Info].[Child 1 Maternity] AS IQChild1Maternity, 
  15.                       dbo.[PR Only Info].[Child 1 Mental Health] AS IQChild1MentalHealth, dbo.[PR Only Info].[Child 2 BMM] AS IQChild2BMM, 
  16.                       dbo.[PR Only Info].[Child 2 Rx] AS IQChild2Rx, dbo.[PR Only Info].[Child 2 OV Copay] AS IQChild2OVCopay, 
  17.                       dbo.[PR Only Info].[Child 2 Maternity] AS IQChild2Maternity, dbo.[PR Only Info].[Child 2 Mental Health] AS IQChild2MentalHealth, 
  18.                       dbo.[PR Only Info].[Child 3 BMM] AS IQChild3BMM, dbo.[PR Only Info].[Child 3 OV Copay] AS IQChild3OVCopay, 
  19.                       dbo.[PR Only Info].[Child 3 Maternity] AS IQChild3Maternity, dbo.[PR Only Info].[Child 3 Rx] AS IQChild3Rx, 
  20.                       dbo.[PR Only Info].[Child 3 Mental Health] AS IQChild3MentalHealth, dbo.[Final Action].Fname AS FAFname, dbo.[Final Action].LNAme AS FALname, 
  21.                       dbo.[Final Action].City AS FACity, dbo.[Final Action].[Issue State] AS FAIssueState, dbo.[Final Action].Zip AS FAZIP, dbo.[PR Only Info].Zip, 
  22.                       dbo.[Final Action].Age AS FAAge, dbo.[Final Action].Gender AS FAGender, dbo.[Final Action].Smoker AS FASmoker, 
  23.                       dbo.[Final Action].[Spouse Age] AS FASpouseAge, dbo.[Final Action].[Spouse Gender] AS FASpouseGender, 
  24.                       dbo.[Final Action].[Spouse Smoker] AS FASposueSmoker, dbo.[Final Action].[Child 1 Age] AS FAChild1Age, 
  25.                       dbo.[Final Action].[Child 1 Gender] AS FAChild1Gender, dbo.[Final Action].[Child 2 Age] AS FAChild2Age, 
  26.                       dbo.[Final Action].[Child 2 Gender] AS FAChild2Gender, dbo.[Final Action].[Child 3 Age] AS FAChild3Age, 
  27.                       dbo.[Final Action].[Child 3 Gender] AS FAChild3Gender, dbo.[Final Action].[Initial Pay Mode] AS FAInitialPayMode, 
  28.                       dbo.[Final Action].[Medical Product] AS FAMedicalProduct, dbo.[Final Action].[Guaranteed Issue] AS FAGuaranteedIssue, 
  29.                       dbo.[Final Action].[Option Rx] AS FAOptionRx, dbo.[Final Action].[Option Office Visit Copay] AS FAOptionOVCopay, 
  30.                       dbo.[Final Action].[Option Maternity] AS FAOptionMaternity, dbo.[Final Action].[Option Mental Health] AS FAMentalHealth, 
  31.                       dbo.[Final Action].[COINS In-Network] AS FAParCOINS, dbo.[Final Action].[COINT OON] AS FANonParCOINS, 
  32.                       dbo.[Final Action].Deductible AS FADeductible, dbo.[Final Action].[Primary BMM] AS FAPrimaryBMM, dbo.[Final Action].[Primary Rx] AS FAPrimaryRx, 
  33.                       dbo.[Final Action].[Primary OV Copay] AS FAPrimaryOVCopay, dbo.[Final Action].[Primary Maternity] AS FAPrimaryMaternity, 
  34.                       dbo.[Final Action].[Primary Mental Health] AS FAPrimaryMentalHealth, dbo.[Final Action].[Spouse BMM] AS FASpouseBMM, 
  35.                       dbo.[Final Action].[Spouse Rx] AS FASpouseRx, dbo.[Final Action].[Spouse OV Copay] AS FASpouseOvCopay, 
  36.                       dbo.[Final Action].[Spouse Maternity] AS FASpouseMaternity, dbo.[Final Action].[Spouse Mental Health] AS FASpouseMentalHealth, 
  37.                       dbo.[Final Action].[Child 1 BMM] AS FAChild1BMM, dbo.[Final Action].[Child 1 Rx] AS FAChild1Rx, 
  38.                       dbo.[Final Action].[Child 1 OV Copay] AS FAChild1OVCopay, dbo.[Final Action].[Child 1 Maternity] AS FAChild1Maternity, 
  39.                       dbo.[Final Action].[Child 1 Mental Health] AS FAChild1MentalHealth, dbo.[Final Action].[Primary Non-Build Rate], dbo.[Final Action].[Primary Build rate], 
  40.                       dbo.[Final Action].[Spouse Non-Build Rate], dbo.[Final Action].[Spouse Build Rate], dbo.[Final Action].[Child 1 Non-Build Rate], 
  41.                       dbo.[Final Action].[Child 1 Build Rate], dbo.[Final Action].[Child 2 Non-Build Rate], dbo.[Final Action].[Child 2 Build Rate], 
  42.                       dbo.[Final Action].[Child 3 Non-Build Rate], dbo.[Final Action].[Child 3 Build Rate], dbo.[Final Action].[Child 2 BMM] AS FAChild2BMM, 
  43.                       dbo.[Final Action].[Child 2 Rx] AS FAChild2Rx, dbo.[Final Action].[Child 2 OV Copay] AS FAChild2OVCopay, 
  44.                       dbo.[Final Action].[Child 2 Maternity] AS FAChild2Maternity, dbo.[Final Action].[Child 2 Mental Health] AS FAChild2MentalHealth, 
  45.                       dbo.[Final Action].[Child 3 BMM] AS FAChild3BMM, dbo.[Final Action].[Child 3 Rx] AS FAChild3Rx, 
  46.                       dbo.[Final Action].[Child 3 OV Copay] AS FAChild3OVCopay, dbo.[Final Action].[Child 3 Maternity] AS FAChild3Maternity, 
  47.                       dbo.[Final Action].[Child 3 Mental Health] AS FAChild3MentalHealth, dbo.[Final Action].[FPMC no Rateup]
  48. FROM         dbo.[PR Only Info] INNER JOIN
  49.                       dbo.[Final Action] ON dbo.[PR Only Info].ID = dbo.[Final Action].[Master ID]
  50.  
Dec 10 '06 #5

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 10 '06 #6

P: 76
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.
Dec 10 '06 #7

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 10 '06 #8

P: 76
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.
Dec 10 '06 #9

NeoPa
Expert Mod 15k+
P: 31,616
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 :(.
Dec 10 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 11 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.