VBA code correction for Adding record by DAO method

Question

Trying to test the code to add record and delete it. So added one test record manually and running the code for the further.

No records get added neither it deletes the records.

Could not understand what it error in the code, please check and guide

Note1: when I type the field name in VBA screen, automatically it converts into small case, only this issue I found, tried to type same case as in the table, still it auto changes. rest I see ok,

Code is as below :

Dim dbs1 As DAO.Database

Dim rst1 As DAO.Recordset

Set dbs1 = CurrentDb

Set Recordset = dbs1.OpenRecordset("TblCheckConnect", dbOpenDynaset)

'****Below test to add record

Recordset.AddNew

Recordset!reconoid=2

Recordset!checkdate = Now()

Recordset!EditAuditInfo = "test"

Recordset.Update

Recordset.MoveLast

'*****Below test to delete the record

Recordset.MoveFirst

Do Until Recordset.EOF

Recordset.Delete

Recordset.MoveNext

Loop

End If

Recordset.Close

dbs1.Close

Set dbs1 = Nothing

Set Recordset = Nothing

My Reference selection is below (I am pasting, generally this selection also creates issue)

Please check and advise the correction.

Regards

Answer 1

Does your code compile without errors?

You should always have error handling.

For one thing you declare

Dim rst1 As DAO.Recordset

but your code uses

Set Recordset = dbs1.OpenRecordset("TblCheckConnect", dbOpenDynaset)

This also indicates to me that you didn't set Option Explicit at the beginning of your module. You should ALWAYS do this.

Also, you can't close the currentdb, so the following line is useless

dbs1.Close

You may like to review: https://www.devhut.net/access-recordset-looping/ 

If you're deleting the entire table just use a SQL Statement

CurrentDb.Execute "DELETE FROM TblCheckConnect", dbFailOnError

Why do you have the extensibility reference library?

Answer 2

Sir,

Thank you. Applied the changes and it worked.

A.

Tested both works - But worried may be later issue, so selected as you guided.

Dim rst1 As DAO.Recordset

OR

Set Recordset = dbs1.OpenRecordset("TblCheckConnect", dbOpenDynaset)

B.
"Option Explicit at the beginning of your module."

I Follow always and it was there.

C.

"dbs1.Close"

I use as I created for a job and wanted to clean after the job is done, so it will not have affect on the application running. As you highlighted, I was closing the current database by dbs1.close.

But surprisingly, if I use this also, this is making no difference.

D.

You link is good learning about DAO. Special thank you for such a site.

E.

"Why do you have the extensibility reference library?"

No idea why I selected it, while testing a xls import file, I was having trouble and while searching I found in some of the website to be selected.

Is there any website, which you can guide me to learn more about these references.

I means some of the important reference, I must selected for the application to run smooth, which should include the excel import etc.

Thanking you once again and will be looking forward to your advise/links.

Regards