Recordset DAO

[bratneedshelppls]

Hi there,



I am new at this and trying to understand, I would appreciate some help please.

I've created a simple database with forms to add new records and update information which works fine.

I'm just reading up on recordsets and DAO and trying to understand its use. Do I need to incorporate this into my database for it to work efficiently? I am assuming yes.

If there's a form being used to add a new client record for example, where would i be adding the recordset.addnew method? My form is based on my table and not a query in this case.

Thank you,
Tess

[moke123]

When using a bound form the form takes care of the recordset and will save the data when the form is closed or you move to another record. So technically you dont need to add a recordset. There are numerous uses for recordsets, probably too many to type out.

to add a new record on a form you can use a command button with code like

Private Sub Command25_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

you could also use (Me.Recordset refers to the forms recordset)

Private Sub Command25_Click()
me.recordset.addnew
End Sub

to use addnew in a dao recordset you would use something like

Sub test()


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    strSql = "select * from  sometable"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)


    rs.AddNew
    rs!someField = SomeValue
    rs.Update


MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

[CJ_London]

by default, access uses DAO. A form recordsource automatically creates a recordset and the form is bound the recordset

If there's a form being used to add a new client record for example, where would i be adding the recordset.addnew method? My form is based on my table and not a query in this case.

Really depends on how your form is required to work. In normal usage where the form is bound to a recordsource, the record is updated/created when you leave the record so no need for any code.

If the form is unbound (i.e. the form does not have a recordset), you would need a button or similar for the user to click to insert/update a record so your code would go in the button click event. Unbound forms are a lot more work and can cause problems in a multi user environment when two users try to edit the same record at the same time.

[bratneedshelppls]

Thank you both, I appreciate your help.

When I save the database on the server to be shared, it's very slow to open up and load forms and wondered if this had anything to do with the slowness.

I've split the database to give each user their own front end but it's still slow in loading. Two users are working from home and using VPN to load. Any suggestions please?

Thank you again.

[CJ_London]

I've split the database to give each user their own front end but it's still slow in loading.

that is a definite requirement.

Your problem is twofold. 1 VPN is significantly slower than a network connection (perhaps 100 times slower) and 2. Chances are you are attempting to load an entire table or query to a form for subsequent filtering.

Your options are to invest in a faster VPN, use terminal server and/or redesign forms etc to bring through the minimum amount of data. Indexing is also important.

So for example if you have an input form, set the form recordsource to something like

"SELECT * FROM myTable WHERE False"

which will return an empty recordset. If the form is set for additions only it should open pretty quickly - saving a record could still take some time due to VPN.

For searching, again have form recordset as above but instead of filtering (me.filter) update the recordsource - so instead of something like

me.filter="ID=1"
me.filteron=true

you have

me.recordsource="SELECT * FROM myTable WHERE ID=1"

(no need to requery the form)

If your two users are regularly working from home then I would recommend you invest in terminal server. It's not free but the performance should be close to having the FE and the BE on your local machine

Note you will see no performance benefit in using recordsets for this sort thing since VPN is the single most significant factor in performance

Only other suggestion is if you have spare machines connected to your server allow your users to connect to them (one each) using teamviewer or similar. The FE's would be located on these machines, not their local machine

[bratneedshelppls]

Thank you Ajax, I appreciate your help.