[AccessD] SPAM-LOW: Re: Binding forms and controls directly to SQL Server

Rusty Hammond rusty.hammond at cpiqpc.com
Tue Mar 23 09:50:42 CDT 2010


If you setup a view on SQL, you can add it as a linked table just like
you would a sql table.     When adding the linked table, keep scrolling
through the list until you get to your views.  Be sure to select a
primary key field if you want to make updates to the data.

For combo boxes, I'll often use a pass through query.  To filter the
data of the pass through, you can just change the sql of the pass
through query via code.


HTH

Rusty

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

I know how to link to the tables, and in fact do that right now.  I am
talking about the next step.

If you just link to the tables, then when you try to pull two tables,
join them on PK/FK, and filter, all of that work is done client-side.
The indexes used in joins and filters have to be sent to Access (JET),
which in memory client-side grabs data, joins, filters etc. and finally
applies the data to a combo or form.

I want to set up SQL Server to join the two tables, apply filters and
just send the DATA to Access.

I know that this is possible, but I do not know the details, the
mechanics of doing this.

John W. Colby
www.ColbyConsulting.com


Darren - Active Billing wrote:
> Hi John
> 
> Using a paradigm we all know and love
> Link to the tables and build forms over these linked tables Edits are 
> directly entered into the SQL tables from the ACCESS Forms You can 
> build a wrapper for the stuff that handles the connections - but I've 
> included it all here in one function so you get the idea
> 
> OK Assume You have a table named "Ledger" in SQLSVR - Assume I have 
> form called frmLedgerItems in my Access dB that I want to be the 
> interface to this SQL table
> 
> Also - You get the relevant data for your combos the same way - It's 
> very quick and very easy
> 
> Now I don't claim to know a lot about this stuff so those who know can

> look at this stuff and point out better ways to do it I'm sure
> 
> Got the Transferdatabase tip this from Stuart By the way
> 
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> Private Sub ps_GetLedgerItems()
> 
> On Error GoTo Err_
> 
> Dim stDocName As String
> 
> Dim strUserID As String
> Dim strPassword As String
>     
> Dim strServer As String
> Dim strServerIPAddress As String
> Dim strdBName As String
> 
> Dim strODBCConn As String
> 
> stDocName = "frmLedgerItems"
> 
> strUserID = "UuserNameHere" ''Could be a DLOOKUP strPassword = 
> "passwordHere" ''Could be a Dlookup
> 
> strServer = DLookup("[DefaultServer]", "tblClients", "ClientID = " &
> Forms!frmClients!txtClientID)
> 
> strServer = Trim(strServer)
> strServerIPAddress = DLookup("[ServerIPAddress]", "tblServers", 
> "ServerName = '" & strServer & "'") strdBName = 
> DLookup("[DatabaseName]", "tblClients", "ClientID = " & Forms!
> frmClients!txtClientID)
> 
> 
> strODBCConn = "ODBC;Description=Test Connection;DRIVER=SQL 
> Server;SERVER=" & strServerIPAddress & ";APP=Microsoft Data Access 
> Components;DATABASE=" & strdBName & ";UID=" & strUserID & ";PWD=" & 
> strPassword & ";Trusted_Connection=no"
> 
> 'I connect to many SQL Server dBs so I need to always get the 'right
one'
> 'So I delete the link to any previous table DoCmd.DeleteObject 
> acTable, "tbl_ledger"
> 
> DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable, 
> "ledger", "tbl_ledger", False
> 
> DoCmd.OpenForm stDocName
> 
> Exit_:
>     Exit Sub
> 
> Err_:
> 
>     MsgBox Err.Number & " " & Err.Description, vbCritical, "error in 
> ps_GetLedgeritems"
> End If
> 
> Resume Exit_
>     
> End Sub
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, 24 March 2010 12: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!
> 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************




More information about the AccessD mailing list