January 11, 2009 at 10:56 pm
Greetings Folks,
I have an Access database with linked tables to SQL Express.
I can't figure out a way to make a Read/Write form when connecting directly to the SQL dbo.tables via ADO Recordsets.
Same thing with filling a form with a pass-through query as the record source.
How can you make a form that you can edit, write to, etc. when you fill it with an ADO recordset or pass-through query?
Thanks
January 13, 2009 at 8:03 am
I've always found that the forms provided by Access are largely useless for anything other than browsing data, as they give you no control over what happens. I always create my initial form using the wizard, and then unbind all the text boxes from their data sources, and write VBA code to do ALL the work, which includes running an ADO query and populating the textboxes and comboboxes, using events to fire on AfterUpdate for comboboxes, udpating the database as a result of clicking on a command button, verifying that all required fields have values before allowing that update, etc., etc. I eliminate any of Access's record navigation items from the form as well, and don't allow any view other than Form view. It's more work, but it's the only way to control what's going on. Once you've created one of these, you can generally borrow code from it every time you need to do it again.
As to where the data resides, it really doesn't matter. I would choose this path regardless of where the data is located. Does that help?
Steve
(aka smunson)
:):):)
vbguy (1/11/2009)
Greetings Folks,I have an Access database with linked tables to SQL Express.
I can't figure out a way to make a Read/Write form when connecting directly to the SQL dbo.tables via ADO Recordsets.
Same thing with filling a form with a pass-through query as the record source.
How can you make a form that you can edit, write to, etc. when you fill it with an ADO recordset or pass-through query?
Thanks
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 13, 2009 at 4:30 pm
Thanks smunson,
I'm not a pro Access guy. I'm into web apps with html, javascript, asp.net, etc. I've been learning Access for a couple projects and it keeps getting deeper.
What you describe sounds like the same path I'm following so I must be headed in the right direction.
I've been using ADO recordsets to handle LOCAL form loading and editing, saving, etc. It all works great in a local Access database. The trouble starts When I get data from a remote SQL Express through a ODBC connection.
I set up a couple forms just like you described by setting the DataSource to a linked table to fill the form with textboxes and labels and then removed the record source. I then populated the controls with the SQL table using a ODBC connection and ADO recordset.
The reason for the post is that when I do this, the form is not write-able. I'm using no other code other that the ADO code to load the data.
Are you saying that I can edit a form and write back to the SQL table using ADO through the DSN connection?
January 14, 2009 at 8:29 am
The key here is to NOT let Access do ANY of the work. Set up your textboxes as UNBOUND controls, ALWAYS. The only controls that should ever have a record source are things like combo or list boxes, where that makes sense, and you can even use the form load event to ensure those are set correctly, and you have to use the SQL Server syntax for your queries that would be present there, which means using single quotes as opposed to double for static string values.
Do not associate the form with any kind of record source either. The only way to get what you want is to do ALL the work in your code. For example, you could have a combo box that has a record source that's an SQL Query that returns all the values for some kind of unique identifier for that table, and then use the AfterUpdate event to take the value of that combo box as part of the WHERE clause in another query that returns the values for all the other fields on the form. You then manually extract your field values, a la:
Me!TextBox1 = rs.Fields("UserName")
Me!TextBox2 = rs.Fields("Address")
.
.
.
You could also have a command button that switches "modes" for your form, so that you can use the same form for data entry of new records if you want. When you then click on an "Update Record" command button, it then checks to see what the caption of a label you place on the form that tells which mode you're in, is equal to. That then determines which ADO code to run - your .Add New method, or your .Update method. Again, once you build one of these, you can borrow code from it for ever.
Steve
(aka smunson)
:):):)
vbguy (1/13/2009)
Thanks smunson,I'm not a pro Access guy. I'm into web apps with html, javascript, asp.net, etc. I've been learning Access for a couple projects and it keeps getting deeper.
What you describe sounds like the same path I'm following so I must be headed in the right direction.
I've been using ADO recordsets to handle LOCAL form loading and editing, saving, etc. It all works great in a local Access database. The trouble starts When I get data from a remote SQL Express through a ODBC connection.
I set up a couple forms just like you described by setting the DataSource to a linked table to fill the form with textboxes and labels and then removed the record source. I then populated the controls with the SQL table using a ODBC connection and ADO recordset.
The reason for the post is that when I do this, the form is not write-able. I'm using no other code other that the ADO code to load the data.
Are you saying that I can edit a form and write back to the SQL table using ADO through the DSN connection?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 14, 2009 at 8:54 am
I have to say - I don't agree with that at all. The point of even bothering to use Access, is that it actually uses BOUND controls. If you're not going to leverage the binding and the power it gives you, then Access is a waste of your time. You'll spend twice as much time building the bound form, then UNbinding everything, then building (from scratch mind you) all of the automation you need.
You'd be better using VB.NET at this point, if that's the direction you're heading in. At least it will wire up the events for you.
If you assign primary keys to your linked tables, and make sure Access knows about them, then the bound rowsets are very possible, and in fact, very powerful. The built in events work just fine, as long as you LET them work.
Sounds to me that you're trying to recreate a .NET forms feel in Access, and THAT will get you into more trouble that I care to imagine.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 8:59 am
I have to agree, if you are not going to use any access features such as bound forms, then you will save your-self a lot of trouble by developing this system in .Net or even vb6 would be easier..
January 14, 2009 at 9:21 am
Matt & Steveb,
Which version of Access are you referring to? If there's an easy way to control what Access does with bound controls in Office 2003, I'd love to see it in action. What Office 2007 can do or not do has become almost irrelevant to me. I've found so much more flexibility in writing my own code, and so much more value in it, that I can't imagine going back, but I'd need to see it to believe it. When someone shows an actual database that let's me control when to update the record, and let's me provide control over record navigation and can allow me to PREVENT the willy-nilly back and forth of going from one record to the next in the sequential order Access controls, then I'll jump behind it. It will also have to allow me to control a lot of other things that aren't usually possible with bound controls.
Also, let's not lose sight of the fact that code-reuse is VERY HIGH once you develop one of these. I've been able to get a new database up and running with this kind of setup in a matter of a couple of hours, complete with fancy user interface. Migrating data up to SQL Server is then a fairly simple matter of using Microsoft's SSMA tool.
This concept provides a tremendously productive prototyping tool for database creation and modelling, and once there, it's not that complicated to convert to .NET if need be.
I'd really like to see an Access 2003 database that uses bound controls but still allows me complete control over what and when. That's when I'll stop recommeding my method.
I also have to disagree with the assessment with regard to the "extra time". I've always more than made up for what little time it took to develop the first one of these. I doubt I have more than a work day or two invested in my first one. The greatest amount of time that gets spent, is in working with the user to ferret out what they really need, as opposed to what they initially ask for.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 14, 2009 at 9:59 am
Off 2007 is worthless from my point of view. I have been using Access 2003 for the longest time.
I think the point is - I don't necessary want to have every control over exactly when things are saved. It's actually fairly easy for my users to understand that in Access - the changes you make are "live" right then. There's no "I forgot to hit save so my changes are lost".
and in exchange - I get built-in data validation (I can't type in alpha-numeric values int a date field and get away with it) without having to build it. I then get to focus on the functionality I need, and not the wireup. It actually seems to foster good, strong database controls, since the Bound state directly enforces the controls in place within the DB. So - no games with orphans, bad data types, etc....
And amazingly enough - I too have a LOT of portability on my apps. The one thing that IS dynamic would be where the linked tables are pointing. One of the apps actually rebuilds all of the linked tables each and every time you start up the app.
Not to say I don't resort to unbound on occasion, though. If it's required, I will sometime do just that. It's just that's the exception rather than the rule.
It just sounds like we're talking about different coding techniques and expectations.
(Edited 4 times to add smallish tidbits - sorry!)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 11:25 am
I think we're definitely talking different expectations. Every Access app I ever had to create required the utmost level of control, lest the user accidentally navigate away, or most commonly, accidentally change a value, and then navigate away, and not even realize they've changed something. That kind of damage is the worst kind, because it may not be noticed until it's far too late to have any chance at easy recovery. Protecting the users from themselves has always been the number one priority, and anything less than total control over the save process just doesn't do that. Fortunately, the accidental save was fairly rare, and because most of the apps had their data migrated to SQL, recovery was fairly simple.
Steve
(aka smunson)
:):):)
Matt Miller (1/14/2009)
Off 2007 is worthless from my point of view. I have been using Access 2003 for the longest time.I think the point is - I don't necessary want to have every control over exactly when things are saved. It's actually fairly easy for my users to understand that in Access - the changes you make are "live" right then. There's no "I forgot to hit save so my changes are lost".
and in exchange - I get built-in data validation (I can't type in alpha-numeric values int a date field and get away with it) without having to build it. I then get to focus on the functionality I need, and not the wireup. It actually seems to foster good, strong database controls, since the Bound state directly enforces the controls in place within the DB. So - no games with orphans, bad data types, etc....
And amazingly enough - I too have a LOT of portability on my apps. The one thing that IS dynamic would be where the linked tables are pointing. One of the apps actually rebuilds all of the linked tables each and every time you start up the app.
Not to say I don't resort to unbound on occasion, though. If it's required, I will sometime do just that. It's just that's the exception rather than the rule.
It just sounds like we're talking about different coding techniques and expectations.
(Edited 4 times to add smallish tidbits - sorry!)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 14, 2009 at 11:39 am
Sounds more like different techniques than expectations. There are plenty of things to do to protect users from themselves, preventing the accidental edits, etc..., but without losing the bound concept.
But - that would get into a rather windy discussion, and techniques I am contactually not allowed to discuss. Let's just say - I don't think we're as far apart as you make it sound.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 12:09 pm
Ok, perhaps... Although I have to point out that you having available to you a "proprietary" technique (my assumption where "contractually not allowed to discuss" is concerned), that I wouldn't have access to, doesn't exactly help make your case.
More importantly, however, you haven't had to deal with the users I've had to deal with, and I wouldn't wish them on my worst enemy. Suffice it to say that the requirements for that were "unique". I developed my methods with those users in mind, as I was in the same work environment for over 25 years. It was a "sheltered life" in so many ways, and yet, in so many other ways, entirely the opposite (also something that might result in a rather "windy" discussion, and I'll just go ahead and forgo that one...)
Steve
(aka smunson)
:):):)
Matt Miller (1/14/2009)
Sounds more like different techniques than expectations. There are plenty of things to do to protect users from themselves, preventing the accidental edits, etc..., but without losing the bound concept.But - that would get into a rather windy discussion, and techniques I am contactually not allowed to discuss. Let's just say - I don't think we're as far apart as you make it sound.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 14, 2009 at 12:36 pm
I don't disagree that we're no doubt dealing with different scenarios. I was merely pointing out there (as always) are many ways to skin the proverbial cat. I tend to stay on the Bound control side, and you don't.
(The technique was all my idea. It was just purchased from me as "work product", separate from the payment for the execution of the project itself).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2009 at 4:13 pm
Many thanks guys for your input!
More importantly, however, you haven't had to deal with the users I've had to deal with, and I wouldn't wish them on my worst enemy
Dealing with users is a priority here too. It seems like more goes into trying to cover every base a user may touch than what goes into the actual meat and potatoes of an app.
I was merely pointing out there (as always) are many ways to skin the proverbial cat.
This is true! And also why I'm here looking for some help. I'd like to get off on the right foot so that I don't put a lot of time in it and then have to go back and start over.
I've got a firm grip on using the ADO recordsets in straight Access but drawing a blank writing back to SQL using ADO recordsets.
Should I forget ADO for writing to a SQL table? What is/are the alternative(s)?
January 16, 2009 at 7:02 am
The difference between an Access table and a linked table to SQL Server is almost no difference when it comes to writing ADO code. Here's some sample code from an Access 2007 mdb file's event code on a combo box for "AfterUpdate".
REM =======================================
REM Code goes here to check combo box's value for null or empty
REM =======================================
Dim rs As New ADODB.Recordset, strSQL As String
strSQL = "SELECT * FROM SCHOOLS WHERE SCHOOL_ID = " & Me!SCHOOL_ID_COMBO_BOX
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Me!SCHOOL_ID = rs.Fields("SCHOOL_ID")
Me!STATE = rs.Fields("STATE")
Me!SCHOOL_NAME = rs.Fields("SCHOOL")
Me!EMIS_IRN = rs.Fields("EMIS_IRN")
Me!UPDATED_BY = rs.Fields("UPDATED_BY")
rs.Close
Set rs = Nothing
REM =======================================
REM Code goes here for whatever else needs doing
REM =======================================
I, for one, would certainly not give up on ADO code, but the need for it with .NET may or may not be as common, depending on how you set things up. In other words, it depends. Matt and I are largely on the same page, but perhaps just at opposite corners of that page, having had differing user-bases and expectations placed on us. If you have users to worry about, then indeed, you may spend most of your time asking detailed questions to ensure that what they asked you for is actually what they really want. That's far more important than any issue around how to code the solution. There's always more than one way to skin the proverbial cat...
Best practices for coded solutions depend largely on the nature of the solution, so it's awfully hard to have any particularly specific ones, save ensuring that your code is well documented, and to the extent possible, the users (or at least their management), understands the basic premises on which your solution is based, so that if those assumptions need to change, someone knows enough to speak up.
Steve
(aka smunson)
:):):)
vbguy (1/15/2009)
Many thanks guys for your input!More importantly, however, you haven't had to deal with the users I've had to deal with, and I wouldn't wish them on my worst enemy
Dealing with users is a priority here too. It seems like more goes into trying to cover every base a user may touch than what goes into the actual meat and potatoes of an app.
I was merely pointing out there (as always) are many ways to skin the proverbial cat.
This is true! And also why I'm here looking for some help. I'd like to get off on the right foot so that I don't put a lot of time in it and then have to go back and start over.
I've got a firm grip on using the ADO recordsets in straight Access but drawing a blank writing back to SQL using ADO recordsets.
Should I forget ADO for writing to a SQL table? What is/are the alternative(s)?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 16, 2009 at 2:06 pm
I, for one, would certainly not give up on ADO code
No no no! I have no intentions of doing that. I don't know if I explained correctly in my first post. That's very possible.
What I'm trying to figure do is open a write-able form that gets data from the ADO recordset from something like the following:
Variables and connection stuff
rs.Open "Select * From MyTable"
Set Me.Recordset = rs
Me.txtName.ControlSource = "UserName"
Me.txtAddress.ControlSource = "Address"
Is there any way to make a form Read/Write on Open when the form is bound to the ADO recordset?
I've been saving data with a form running the following oledb connection:
Dim conMy As ADODB.Connection
Dim rsMy As ADODB.Recordset
Set conMy = New ADODB.Connection
conMy.Open "Provider=sqloledb;Driver={SQL Native Client};Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=MyDataBase;User Id=me;Password=mypass"
Set rsMy = New ADODB.Recordset
rsMy.Open "Select * FROM dbo.MyTable", connRec, adOpenKeyset, adLockOptimistic
With rsMy
.AddNew
rsRec(0) = Me!txtName
rsRec(1) = Me!txtAddress
.Update
End With
One more question:
How can you save a record to the table without having to load the entire table into the recordset? Or is that even possible?
Many Thanks, I appreciate your input?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply