October 6, 2014 at 11:15 am
I'm looking for some way for a SQL Server query to be able to be used as an updatable record source for an Access form. The Access query I'm using is updatable, but very slow. A clone of it within a stored procedure is not updatable. Sure would like to use the excellent speed of SQL Server for this updatable form.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 6, 2014 at 9:13 pm
The simplest way to achieve that is to create a view, and give it a unique index. Access will see that as a primary key when you link to it using ODBC, and make it updateable, and it will have the speed of SQL Server. You will want to use a filter on the form to limit the number of records returned initially, but we use that to get sub-second response times from tables containing several million rows.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 6, 2014 at 9:28 pm
Wendell, that is what I thought, but when I tried to link to a view, I didn't see how to do it. All I was able to see were table names. As I was typing this I was thinking, would I use a pass-through query to link to the view? I don't have anything at home to test that with.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 7, 2014 at 7:34 am
We typically name views vwXXXXX so they show up after tables, which we prefix with t so they look like tXXXXXX and then the views show up at the bottom of the list. But the views should all show up in the linking process. Unfortunately that process is different depending on the version of Access, so what version are you using?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 7, 2014 at 7:46 am
Access 2010. When I get to work this morning, I will absolutely try to find that view. Thank you!
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
October 7, 2014 at 8:10 am
In 2010 you use the Import tab of the ribbon and choose ODBC. You then specify the DSN which points to the SQL Server database and indicate you want to link, not import, and that should display all the tables and views. Note that stored procedures are not displayed, and cannot be linked to, so their use is limited to situations where you want to get a recordset that is not updateable, and are typically used in conjunction with a pass-through query that must have T-SQL syntax and a proper connection string.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 8, 2014 at 6:59 am
Wendell, thank you for your suggestions and ideas. The boss is going to be extremely happy.I greatly appreciate your willingness to share your knowledge and experience in a very helpful way. Thank you again.
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply