jwcolby
jwcolby at colbyconsulting.com
Tue Mar 23 11:53:43 CDT 2010
> Pass-Through queries are read-only which is why I only use them for combo boxes or to call stored
procedures.
Which would explain why I can't update or add records.
But this is the big time sink for this form. Can I bind the form to a stored procedure and pass
parameters to the stored procedure?
John W. Colby
www.ColbyConsulting.com
Rusty Hammond wrote:
>>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.
> **********************************************************************
>