Form's unique table property and stored procedure parameters

[Dmitriy]

I have a form in an .adp file in Access 2007, and the project is connected to SQL Server 2005. The form uses a stored procedure to display records. The stored procedure does a JOIN of 2 tables, and has parameters which are passed in through the form's Input Parameters property. It compares the parameters to the corresponding columns and returns those records where there's a match. The records are returned correctly but they cannot be edited because the Unique Table property of the form is not filled in. I can't fill it in, however, because the drop-down list is empty and I can't type in the name of a table.
I found that if I take all of the parameters out of the stored procedure (so that it returns all records), the table names appear in the Unique Table list and everything works fine. Once I put the parameters back, the Unique Table field is blank again. Does anyone know why this is happening, and is there a way to make everything work so that I can use parameters in my query and set the Unique Table property?

Thanks

 

[Pat Hartman]

I don't use the .adp and neither do many other people which is why it has been deprecated. I am going to guess that a stored procedure is not updateable and you need to use a view instead.

 

[Dmitriy]

You were right, it's a known bug with Access 2007 and SQL Server. Stored procedures work fine with Access 2003 though.

 

[Pat Hartman]

God I'm good

 

[corydeppen]

Does anyone know if this bug has been confirmed by Microsoft and whether it will be addressed in an upcoming hotfix or SP?

 

[Dmitriy]

I called Microsoft Support about this (half a year ago) and they confirmed that it is a bug in Access 2007. I doubt they've fixed it though. I was told they'd let me know if there are any updates, but I haven't heard from them since.

 

[Pat Hartman]

Don't expect this bug to be fixed. The .adp has been deprecated and that means that no work will be done to improve or fix it.

 

[BobMcClellan]

I know this is an old thread but I have a solution. The access bug may not be fixed but I have found a way to get around it. Although the drop down list in the form for the Unique table property is blank, simply set the form.UniqueTable property when you open the form to the table you want to update.
example: Me.frmExtrasLaborSub.Form.UniqueTable = "ExtrasLabor"

I have tested this and so far it's working fine.

hth,
..bob

 

[overlordqd]

I know this is an old thread but I have a solution. The access bug may not be fixed but I have found a way to get around it. Although the drop down list in the form for the Unique table property is blank, simply set the form.UniqueTable property when you open the form to the table you want to update.
example: Me.frmExtrasLaborSub.Form.UniqueTable = "ExtrasLabor"

I have tested this and so far it's working fine.

hth,
..bob

Wow, great workaround. It saved me a lot of time. Thank you.

 

[JakeC]

BobMcClellan you are a rockstar.

 

[Stopher2475]

BobMcClellan, You are the man.