Form Bound to SQL Indexed View

Thanks Charles.

So are you saying that with Access 2007 the PK for the new row would be
returned with the 'save' and Access could then retrieve the complete new row
on the first try, rather than two or three tries based on other fields?

The articles in the links are very general. I can't seem to find anything
that explains any real advantages of 2007 over 2003 regarding SQL Server,
other than 'it's better'.

Does 2007 still use ODBC to connect to SQL server?

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
Hi AG,
Regarding your question, "Would there be any additional
advantages/features
(regarding SQL Server) if I were to go to 2007?", though few documents
clearly talk about this. Per my use experiences, I do feel that Access
2007
has better performance and more effective communications with SQL Server.
In addition, you cannot utilize the SQL Server 2005 features from older
version ADP project. I recommend that you upgrade your Access 2000 to 2007
so that you can get more benefits from Access 2007. You can refer to the
articles:
Microsoft Office Access 2007 top 10 benefits
http://office.microsoft.com/en-us/ac...650211033.aspx
Database specifications
http://office.microsoft.com/en-us/ac...307391033.aspx

Regarding new PK value, I think that you need not worry about this in
Access 2007. Assume that your SQL table has an identity column, you can
conveniently add new rows as well as in SQL Server Management Studio. The
new PK value will be returned immediately after you add a new record.

Hope this helps. Please feel free to let me know if you have any other
questions or concerns.


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Hi AG,
Regarding your concerns, please see the following answers:
So are you saying that with Access 2007 the PK for the new row would be
returned with the 'save' and Access could then retrieve the complete new
row
on the first try, rather than two or three tries based on other fields?

Yes. Once you finished inputting a new row, the PK value will be
automatically returned into the new row's PK field. You can see it.

Does 2007 still use ODBC to connect to SQL server?

Yes, for Access 2007 database.

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks Charles. I am going to install 2007 and start exploring.

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
Hi AG,
Regarding your concerns, please see the following answers:
So are you saying that with Access 2007 the PK for the new row would be
returned with the 'save' and Access could then retrieve the complete new
row
on the first try, rather than two or three tries based on other fields?

Yes. Once you finished inputting a new row, the PK value will be
automatically returned into the new row's PK field. You can see it.

Does 2007 still use ODBC to connect to SQL server?

Yes, for Access 2007 database.

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Charles,

I installed Office 2007 and converted my small test mdb to Access 2007
format.
I opened SQL Profiler, like I did when running the Access 2003 version.
There seems to be no difference in the way Access interacts with SQL Server
2005.
After an insert, Access tries to retrieve the row via a select fields From
myview Where pk field is null.
Obviousely, that will return nothing, but it then tries again with the exact
same request.
When that fails, Access then makes a new request using all the other fields
(except the PK) for the where criteria, and that returns the new row.

I guess this is by design, but just does not make sense. Access will not
recognize a view (or table) as updateable without a unique key, so what is
the sense in doing a select based on a null key (twice)? Just seems very
inefficient to me.

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
Hi AG,
Regarding your concerns, please see the following answers:
So are you saying that with Access 2007 the PK for the new row would be
returned with the 'save' and Access could then retrieve the complete new
row
on the first try, rather than two or three tries based on other fields?

Yes. Once you finished inputting a new row, the PK value will be
automatically returned into the new row's PK field. You can see it.

Does 2007 still use ODBC to connect to SQL server?

Yes, for Access 2007 database.

Please feel free to let me know if you have any other questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Hi AG,
Thank you for your response. I think that there must be something different
between our tests.

Per my test, the new PK value was returned immediately. I connected to a
SQL Server 2005 instance (ODBC) from Access 2007 and opened the table with
Datasheet View, then I added a new row, and when I finished my input, the
new ID was automatically returned.

Could you please elaborate your process so that I can perform a test to
check if I can reproduce your issue?

Look forward to your response. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Charles,

Please re-read my last post.
I did not say it was never returned. Fire up SQL Server Profiler and monitor
what is going on. You will see that it takes Access three tries to get the
new row. It just seems inefficient to me.

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
Hi AG,
Thank you for your response. I think that there must be something
different
between our tests.

Per my test, the new PK value was returned immediately. I connected to a
SQL Server 2005 instance (ODBC) from Access 2007 and opened the table with
Datasheet View, then I added a new row, and when I finished my input, the
new ID was automatically returned.

Could you please elaborate your process so that I can perform a test to
check if I can reproduce your issue?

Look forward to your response. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Hi AG,
What I saw from SQL Profiler was as following:
====================================
[RPC:Completed] exec sp_executesql N'INSERT INTO "dbo"."T2" ("NAME")
VALUES (@P1)',N'@P1 varchar(20)','asdfasdf'

[SQL:BatchStarting] SELECT @@IDENTITY
[SQL:BatchCompleted] SELECT @@IDENTITY

[RPC:Completed] declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "ID","NAME" FROM
"dbo"."T2" WHERE "ID" = @P1',6
select @p1

[RPC:Completed] exec sp_execute 1,6

[RPC:Completed] declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6
int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "ID","NAME" FROM "dbo"."T2"
WHERE "ID" = @P1 OR "ID" = @P2 OR "ID" = @P3 OR "ID" = @P4 OR "ID" = @P5
OR "ID" = @P6 OR "ID" = @P7 OR "ID" = @P8 OR "ID" = @P9 OR "ID" =
@P10',1,2,3,4,5,6,6,6,6,6
select @p1
======================================

That looks fine to me. Could you please let me know what you saw and what
your expected result were from SQL Profiler?

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks Charles.

In my case I don't get 'SELECT @@IDENTITY'
I looked at your example again and it is similar to mine, but I am not clear
on how you are adding the new record.

Where are you executing the statement INSERT INTO v_A1A2A3
VALUES(2,'H2',getdate(),2,20,1,'A3.1')?

My situation is a bound Access datasheet view form.

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
.. .
Hi AG,
What I saw from SQL Profiler was as following:
====================================
[RPC:Completed] exec sp_executesql N'INSERT INTO "dbo"."T2" ("NAME")
VALUES (@P1)',N'@P1 varchar(20)','asdfasdf'

[SQL:BatchStarting] SELECT @@IDENTITY
[SQL:BatchCompleted] SELECT @@IDENTITY

[RPC:Completed] declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "ID","NAME" FROM
"dbo"."T2" WHERE "ID" = @P1',6
select @p1

[RPC:Completed] exec sp_execute 1,6

[RPC:Completed] declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6
int,@P7 int,@P8 int,@P9 int,@P10 int',N'SELECT "ID","NAME" FROM
"dbo"."T2"
WHERE "ID" = @P1 OR "ID" = @P2 OR "ID" = @P3 OR "ID" = @P4 OR "ID" = @P5
OR "ID" = @P6 OR "ID" = @P7 OR "ID" = @P8 OR "ID" = @P9 OR "ID" =
@P10',1,2,3,4,5,6,6,6,6,6
select @p1
======================================

That looks fine to me. Could you please let me know what you saw and what
your expected result were from SQL Profiler?

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Hi AG,
I am sorry that my last test was using a table but not an updatable view.
Regarding an updatable view, it indeed had the issue as you mentioned. I
agree with you that it is really inefficient. However I am curious why a
single table worked fine, but an updatable view failed. Now I am trying to
consult our product team to see why this issue happened. I may need more
time to get back to you. Appreciate your patience.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks Charles,

I am glad that you saw the same thing that I did. I too, am very interested
in their answer!

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
Hi AG,
I am sorry that my last test was using a table but not an updatable view.
Regarding an updatable view, it indeed had the issue as you mentioned. I
agree with you that it is really inefficient. However I am curious why a
single table worked fine, but an updatable view failed. Now I am trying to
consult our product team to see why this issue happened. I may need more
time to get back to you. Appreciate your patience.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======