Access Multiple Dev Application Front-End for SQL Server 2008

Question

I have a SQL Server 2008 R2 instance in production on a Server accessed from various Excel client machines. My current approach updates the Database from Excel using INSERT & UPDATE Triggers on Views that then populate various base tables. Essentially, the SQL Server Database is done (along with USP's & UDF's) and I am looking to build a GUI.

Given we have Access 2010 within our Microsoft Office installation, it may make sense for me to build an Access 2010 Form that better captures the business logic and deploy an Access 2010 App? I have seen most of the material on Access seems to assume Developers have more familiarity with Access and need to scale up to SQL Server. In my case, I have no experience with Access and a SQL Server 2008 R2 in production.

I have seen that it is possible to create linked tables within Access 2010 to SQL Server, but is it possible to link Access to an already existing SQL Database (i.e. to retain the SQL Data, Triggers, UDF's, ...) and in my case, build an Access Application with a Login Page that allows me to choose the connection string so that I can use the same GUI and target multiple instances (i.e. Production on Server, Development on Laptop)?

Would the Access query be able to display a View if the view is generated from a Stored Procedure? Can the select statement access the results set from a UDF returning a Table?

I am hoping that Access is to SQL Server what APEX is to Oracle (i.e. Rapid GUI Prototyping into a Database)? FYI, I am not a .Net developer and prefer Python over VB.Net or C#. My alternative choice is to use PySide or wxPython and conjour something up in Python, but I would prefer the quick win with Access. Appreciate if you could make clear whether this is possible with Access 2010? Thanks

Answer 1

You've been a member of Stack Overflow for a couple of years now, so I'd be surprised if you weren't aware that the most common response to a question like this is:

"What have you tried?"

Still, I'll attempt to hit the high points:

is it possible to link Access to an already existing SQL Database (i.e. to retain the SQL Data, Triggers, UDF's, ...)

Yes.

and in my case, build an Access Application with a Login Page that allows me to choose the connection string so that I can use the same GUI and target multiple instances (i.e. Production on Server, Development on Laptop)?

Probably, by tweaking the .Connect property of your Access linked tables and pass-through queries. Failing that, you could always just stick with an ODBC DSN and tweak its settings to connect to a particular instance of SQL Server.

Would the Access query be able to display a View if the view is generated from a Stored Procedure?

That depends on what you mean by "a View". A pass-through query in Access can return (read-only) results from a SQL Server Stored Procedure. An Access "linked table" can be bound to a SQL Server View, but AFAIK a SQL Server View cannot directly return results from a SQL Server Stored Procedure. However, a SQL Server View can return results from a Table-Valued Function, which leads into...

Can the select statement access the results set from a UDF returning a Table?

"The select statement" is ambiguous. Do you mean "a SELECT statement in Access against an ODBC linked table"? Or maybe "a SELECT statement in an Access pass-through query"?

What I can say is that an Access linked table bound to a SQL Server View retrieving results from a SQL Server table-valued function does work, and the linked table appears to be updateable if the SQL Server View can comply.