Rusty Hammond
rusty.hammond at cpiqpc.com
Tue Mar 23 10:21:04 CDT 2010
>From the SQL view of the query, go to the Query menu, SQL Specific,
Pass-Through. Then right click the title bar of the query and go to
Properties. Setup your ODBC Connect Str and you should be good to go.
The SQL of the pass-through needs to be in T-SQL and not access sql.
Pass-Through queries are read-only which is why I only use them for
combo boxes or to call stored procedures.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 23, 2010 10:11 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SPAM-LOW: Re: Binding forms and controls directly
to SQL Server
OK, I can do that. To make a query a pass through query I set a
property of the query?
John W. Colby
www.ColbyConsulting.com
Rusty Hammond wrote:
> 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.
> **********************************************************************
>
--
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.
**********************************************************************