Solved Form behave differently when linked to SQL Server?

[marlan]

Hi all you experts,

I've bin developing acc for quite a while, against acc back end. Lately, I've bin hired by a company that develops access against SQL server.
A few differences I've noticed:

1. When linking a table with an autoincrement PK field, the autoinc value is yet not created by the time the Forms Before_Insert occurs (and in my case, fails the action).
2. A subform with a master/child field filter, will not display its records, unless I do some action in the subform's Timer event (after the form is already open.

Is this normal? Is Working with SQL Server Back end so much different?

Am I getting something wrong?

 

[theDBguy]

Hi. The one difference I know between Access and SQL Server with regards to the Autonumber/Identity fields is that Access immediately creates it as soon as the form is dirtied; whereas, SQL Server doesn't do it until the record is saved to the table.

 

[Pat Hartman]

For #1, if you need to capture the ID after it is generated for some reason, you need to use @@Identity to get it.
For #2. This is not normal. Have you set the master/child links. Get rid of the Timer code.

            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
            NewProvProcID = rsScope!NewID

Another thing to worry about is that table defined defaults are also not applied until the record is saved so if you have code that relies on a value being present, you might need to add the defaults to the control on the form so they will get applied before the record is saved.

 

[gemma-the-husky]

Hi all you experts,

I've bin developing acc for quite a while, against acc back end. Lately, I've bin hired by a company that develops access against SQL server.
A few differences I've noticed:

1. When linking a table with an autoincrement PK field, the autoinc value is yet not created by the time the Forms Before_Insert occurs (and in my case, fails the action).
2. A subform with a master/child field filter, will not display its records, unless I do some action in the subform's Timer event (after the form is already open.

Is this normal? Is Working with SQL Server Back end so much different?

Am I getting something wrong?

You might be better not reading the new index value until after the record has been saved, whether using Access or SQL server.

One problem with an early read is resolving issues with intact sequences if you delete a record after using an auto number value.

So one alternative is to not rely on the auto number, and allocate the next number from a separate table storing a counter value.

You need to think about it, as whatever you do might be more complex than you expect.

 

[marlan]

Hi, and thanks for all your replies!

We've solved these issues for now:
#1 - this Before_Insert code was some legacy code, I just had to work around.
#2 - I found ODBC link to SQL Server is read only when linked to a table with no Primary Key (I usually have one...). Add that to some other human error I had there, and took me some time to notice...