I ask myself is it better to bind the form to a table to create new records, or change old ones (Single record form) or to use recordsets
though it's easier to bind the form to a table using recordsets give more options and freedom.
it's also give a good start if you want to move from Access for the front end application.
What's better, a hammer or a screwdriver? The answer to that, like your question, is "it depends". They are tools, each better suited to different tasks. A friend wrote this:
Which I would mainly agree with, though I'd disagree with the non-Jet point. I use forms bound to SQL Server linked tables all the time. My general philosophy is to use a bound form most the time, an unbound form under appropriate circumstances.
When I first started Access, I bound stuff to tables. Once I got involved with split databases (FE/BE), I realized there are tons of reasons to bind to queries, not tables.
When to bind, when to not bind? Easy to do in one way, harder in another.
My bind / no bind decision is always based on what I'm doing with the form. When I'm doing table maintenance, I bind to a query that is identical in structure to the table. But when I'm picking and choosing actions in a junction table I work via recordsets. I bind the recordsets to the queries and just use the form's controls for steering the process. In other words, if the form's implicit behavior on navigation does what I wanted, OK. If not, then I don't bind the form to a recordset because I'm going to have to "roll my own" for that database.
Hope that was clear enough to answer your question.
Working with recordsets behind a form, you quickly determine that the recordset - when bound to a query - doesn't need to know which database holds the data. It knows the path to the BE database. But if you use a split database, you cannot use this:
Code:
Set rsVAR = CurrentDB.OpenRecordset( ..., dbOpenTable, ...)
because if it is an FE/BE split, it isn't the current database you wanted to open. Using a query, you don't CARE where your tables are.
I am not sure you are going to get a clear opinion on or answer to your questions. People do this differently. I am not an expert and am self taught and still learning.
Binding a form to a table is like carrying a bucket from home to a well and back just to drink a glass of water and throw the rest of the bucket away.
"Microsoft Access Developers Guide to SQL Server" The water example comes from this book.
When you think about it a person can only look at one record at a time so why give them more.
Like it was said above I also first started with forms bound to tables. It is easy to do.
Now I use a query for each form and for adding new records I use totally unbound forms.
The difficult thing for me was how to deal with queries that returned no records as the form does not like this.
If it also helps - I have been playing with .NET stuff and ADO.NET (call this the current or future) and from what I understand (not so much) the idea or current thought is get in, get just your data and get out each time as quickly as possible.
most of the forms in my current project are using recordsets (unbound).
at some point I was afraid that this approach is wrong because of speed, resources...
as much as I go deeper into the question it seems to me I took the correct way
Sorry, I dont know the answer to your question. I learn from doing. On your database have you tried it to see what happens. Get 2 user to view the same record. One changes and moves to a new record. The second then does the same. What happens?
I am not 100% sure how I got mine the way I did but the second person will get a message if he tries to change an already changed record.
When using unbound forms, I use a method from the same book mentioned earlier. I have a numeric field in the data named "LastUpdated" or whatever. When I load the form I grab the current value. When I update a record, I increment that value. If two people got a record at the same time, they got the same number to begin with. The first one to update will increment the number, so when the second tries to update theirs will fail, which I trap.
