Dan Waters
dwaters at usinternet.com
Tue Mar 23 10:01:51 CDT 2010
If you can find an old book named 'Microsoft Access Developer's Guide to SQL
Server', you'll have what you need - Chapter 10. $5.70 used.
http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446
/ref=sr_1_1?ie=UTF8&s=books&qid=1269356371&sr=8-1
Or, the ADH 2002 Enterprise Edition also has info on this subject.
-----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