[AccessD] Binding forms and controls directly to SQL Server

Jim Lawrence accessd at shaw.ca
Wed Mar 24 19:29:29 CDT 2010


Hi John:

You are not and can not really connect to an MS SQL DB as you can to a MDB
BE. You can at the most hold a connection open and therefore hold a group of
records. As long as that connection remains on you effectively are bound to
these records

This connection does not have the same functionality as your would have with
an MDB file. Depending on the recordset connection type to the SQL any
changes that someone else may make to the records will not be reflected in
the records that you are holding. Their changes may just be queued until you
release your connection.

If you are tricky enough and are adamant about holding on to a
process/connection and another person or operation is also requesting the
same record a situation called the 'deadly embraces' can be created. I do
not know if you could do that in MS SQL but in Oracle, at times the DBA
would have to go in and unlock records. I think MS SQL just either cancels a
second connection to the same records or just cancels the whole process.

Binding records to any major SQL DB is basically a waste of time as SQL DBs
are designed to work unbound. Conflicts in changing or deleting data are
most likely ignored by the system...It tends to be a first come first serve
rule based system. If a particular process must go through then it may be
better to code a loop which holds a set of changes and processes those
changes when the conflicting records have been released or prompts the user
to abandon those changes... but you would have to write some code.

SQL DBs require a whole different mindset than you would and could have with
an MDB.

That said, there are some pseudo 'external links' to SQL tables through an
ODBC connection you can achieve and even queries. All results I have seem
using this method to emulate an MDB BE has been slow at best, unstable at
worst and down-right dangerous in certain situations...if you think data
corruption is dangerous. (In the past I have made some good coin rebuilding
from some of these aborted attempts caused, with the best of intensions,
from some deluded application developers.) 

Redesigning a is not a simple task but I have been doing this application
function and design over 10 years now... so if you need any help.

Jim

   
  
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, March 23, 2010 6: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




More information about the AccessD mailing list