[AccessD] Binding forms and controls directly to SQL Server

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!

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com