Access VB Recordset

Antwan McCoy
Antwan McCoy used Ask the Experts™
on
I created a code for my database to open a recordset and view, edit and update the table, I can't get the code to open the table...

Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

Private Sub CustomersRecordset()

    strSql = "Select * FROM tblCustomers;"
   
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCustomers")

End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
to open the "Table", try use OpenTable method instead, like:
DoCmd.OpenTable "tblCustomers", acViewNormal

Open in new window


DoCmd.OpenTable method (Access)
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.opentable
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
@McCoy ... whit that code, you are just opening the Recordset in code ... which is not visible per se.
Use Ryan's method ...
John TsioumprisSoftware & Systems Engineer

Commented:
The OpenRecordSet is a method to get "access" to the data of the table..
The DoCmd.OpenTable is to open the table
What do you really need to do.... ?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

We need some more info. Is the code throwing an error message or is it just not working as you expect? In which case, please try to explain what you ARE expecting?
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
The end goal is to be able to open the recordset and edit or make changes to it then update the table...
Is the code throwing an error message or is it just not working as you expect?

Are you wanting to make data changes with your CODE, or you do want to open it for a user to make changes through an interface?
John TsioumprisSoftware & Systems Engineer

Commented:
If this is the case then
Dim rs As DAO.Recordset
Dim strSql As String

Private Sub CustomersRecordset()

    strSql = "Select * FROM tblCustomers;"
   
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
With rs
'Pick ONE of the following two
rs.Edit ' For Editing
rs.AddNew ' For Adding New Record
'Your Operations 
'e.g.
.Fields(FieldName) = SomeValue
.Update
End With
rs.Close
If not rs is Nothing then set rs = Nothing

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Or if you just want to open the table directly (like an Excel spreadsheet) then use OpenTable as Ryan suggested.
Distinguished Expert 2017

Commented:
I can't believe you are all going along with this.  You are loading bullets in Antwan's gun which he has pointed directly at his head!

Antwan, it is NEVER correct to open a query or table for the user to edit the raw data.  You have no ability to control anything he does.  If he accidentally deletes all the records, color them gone.  When you are creating applications for others to use, it is your job as the developer to protect the data.  The only way you can do that is to hide the inner workings of Access from the user and allow him to only interact with forms and reports.  If you want the form to present the data in a table-like view, use the DataSheet view of the form.  Using a form allows you to use validation code in the BeforeUpdate event of the form to ensure that required fields are entered, dates make sense (9/9/219 is a valid date but it sure is a common typo and almost certainly contextually invalid), and ensure that codes are valid so that something like a state code comes from a combo and you simply cannot enter an invalid state code because you can only choose an option from the combo.
Most Valuable Expert 2012
Top Expert 2014

Commented:
I agree with everything Pat said, but the simple fact is they guy asked how to open a table. He may have a very good reason to do so. But I am glad you pointed out the very obvious dangers of doing so, should have done that myself ...
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
Thanks Pat, John, and the Expert Exchange community, my goal is to keep the user's interaction with queries and editing records to a minimum which Pat is spot on in saying. I think my wording was off a bit, it should be view the recordset from which the query is pulling in my code. To my understanding and I'm still learning, I can create a recordset and within the recordset, editing can occur. Also, if I'm pulling in records from a spreadsheet, I can view the spreadsheet as a recordset prior to appending or updating the actual table to ensure accuracy of the data and no duplicates are being added to the table based on the records being uploaded....Which is my next question in how to accomplish :-)...Thanks so much and I'm definitely learning a lot from you all...The do's and don't....John, how can I view the recordset before  any changes occur or is that possible? With everything said, the goal is to eliminate so much macro usages and so much user intervention with the macros and get the data ready for the next level via VBA and DAO...
Most Valuable Expert 2012
Top Expert 2014

Commented:
As others have said, you don't really "view" a recordset. A Recordset is an in-memory process that houses your data. In order for a user to work with that recordset you must present it to the user. As Pat and others have said, generally you would bind a form to your recordset - or, better yet, just create a form that is based on a query that represents your data. You can do this easily by creating a form and setting the Form Recordsource to a valid query (or even your SELECT statement). If you do this, Access will perform all the heavy lifting for you.
Distinguished Expert 2017

Commented:
Linked spreadsheets are not directly updateable so although you can bind them to a form, that form will not be updateable.  If you need to update the spreadsheet data before applying it to the permanent tables, you could import the spreadsheet to a local table and update it there.  Once the user is happy with the contents, he can press a "button" and your code would append the data to the permanent tables.
Distinguished Expert 2017

Commented:
Antwan, did you actually go with opening the table/query directly and letting the user update the naked data?  If you didn't, you accepted the wrong solution.  If you did, I hope your luck doesn't run out because that method is simply too dangerous to contemplate for ANY reason.  Even I don't update table data directly in production databases unless it is an emergency and I create a full backup first.  It is very poor practice.
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
No Pat, I did not and I truly understand your point and agree, that is what we are trying to stop from happening now and I have taken over responsibility of the re-engineering of ensuring the users are not doing this in production environment....
Distinguished Expert 2017

Commented:
But you still accepted the wrong post as the answer to this question.
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
What is the solution Pat, I'm a bit confused on this now....Thanks
Distinguished Expert 2017
You accepted the answer by Ryan which may or may not answer your original question which was "how do i do something that is really bad practice and will almost certainly lead to bad data".  But since you were convinced that this was a bad idea, you are using forms instead of opening queries or tables for user editing.  I think there may be a button where you can ask for moderator attention to allow you to change the answer you want to award.  Mine was the first to suggest that opening tables and queries for user editing was a bad idea and Scott agreed.  So, you can select mine or your own.  If you want to thank people for their help, do that by awarding points rather than accepting their post as the "answer"

The point of selecting a single response as the "answer" is that it helps people who find this thread in a web search.  It tells them what action worked for you and so is most likely to work for them.
Antwan McCoyDatabase Solutions Engineer

Author

Commented:
Thank you Pat, I'm still new to the Experts Exchange community but I will definitely exercise awarding the points to the solution I used moving forward and I corrected that action immediately from you educating me better...Thanks again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today