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