[AccessD] Binding forms and controls directly to SQL Server

anitatiedemann at gmail.com anitatiedemann at gmail.com
Tue Mar 23 20:52:58 CDT 2010


John,
I use mostly SQL Server BE with Access FE's these days. Here is how I do it:

1. Use a project with connection to SQL Server - once you create that you  
will see the tables/views and stored procedures within your Access FE
2. Use stored procedures to feed forms and reports. You can bind a form to  
a stored procedure exactly the same way you can with a normal Access query
3. You can pass parameters to forms and reports by using the Input  
Parameter property which can be found on forms and reports

Example - this procedure feeds the customer form and accepts a customer id;

CREATE PROCEDURE spFrmCustomers
(
@CustID long
)
AS
SELECT
*
FROM
tblCustomers
WHERE
CustomerID = @CustID


The customer form would then have the following Input Parameter =  
CurrCust() ... and its recordsource would be spFrmCustomers

CurrCust being a Property that can be set before the customer form is opened

I hope this helps. I find the above approach really works well and ensures  
that no form is opened with a massive recordset behind it

Anita Smith

On , jwcolby <jwcolby at colbyconsulting.com> wrote:
> 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






More information about the AccessD mailing list