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