[AccessD] Binding forms and controls directly to SQL Server

However,

If you want to bind an Access form to SQL Server while using an OLEDB
connection, take a look at MS KB 281998.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 23, 2010 8:12 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Binding forms and controls directly to SQL Server

Guys,

One thing I have always wanted to learn is how to bind Access objects
directly to SQL Server.  Does 
anyone out there do this and would you be willing to assist me in figuring
this out.

My billing database is in SQL Server.  SQL Server 2008 is running directly
on my laptop and the 
database files are on my laptop.  I know some stuff about SQL Server but
there is a bunch that I 
don't know.  I tend to know the "data side" but not know the admin side.  I
can create tables, 
indexes, views and stored procedures.  I have never created a stored
procedure that returned a 
result set, only SPs that return an integer, and also (from C#) SPs that
have Output Parameters for 
things like ErrorNo and ErrorDescr and NoOfRecsAffected.  Stuff like that.

So, what do I do to:

1) Cause combos to pull their data from SQL Server.  Do I create a view on
the server side?  If so 
how do I then cause Access to see the view and use that for the combo.
2) Pass parameters to the server side so that (for example) I could filter a
combo to only display 
products for a specific client.
3) Cause forms to pull data from sql server such that the forms are able to
see and edit the data, 
but bound to something (a view?) out in sql server so that SQL Server "does
the work" of narrowing 
down the data displayed.

Let's take a reasonable simple example.

I have a "time sheet form".  This form has a pair of controls which I can
use to set from / to dates 
so that by default when opened the form displays this week's time entries.
However I can set dates 
for any time period and display the time records that match that to / from
filter.

I have a Client Product combo that displays only products for currently
active Clients and currently 
active products for those clients.  Thus that combo pulls data from the
client and product table, 
filtered on the active flag of both.

I have a work category combo that pulls data from a table which is
essentially the product / bill 
rate, so once a client is selected, a filtered (by client) product is
selected, and then a filtered 
(by product) work category is selected.

Pretty simple stuff to do directly in Access.  But how do I do this such
that SQL Server does this 
filtering and presents the filtered data to the combos?

I have never even looked at this stuff so I need very specific instructions
one time.  I seem to 
vaguely remember that I need to build views (or SPs?) out in SQL Server, and
then do a passthrough 
query?

Can anyone help me figure this out?  Once I do it one time I will be off to
the races.

Thanks guys!

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com