opening and closing recordsets

[striker73]

In my code, I often use the syntax Me.Recordsource = "SELECT * FROM MyTable" The other day I was doing some testing and I received the error message "Can't open any more databases" or something similar. I was wondering if I should be closing off the recordset before I set it to some other SQL statement. Any advice?

 

[KellyK]

Yes. I would suggest using
Me.RecordsetX.Close before setting it to a different value.

Also, in your source string do you say
RecordsetX.Open "Select * from MyTable", CurrentProject.Connection?

Kelly

 

[KyleS]

Actually Kelly, I think striker is using that line to set the recordsource of his form, so his syntax should be fine.

Do you change the recordsource of your forms often?

Wouldn't it be easier (I know your PC's memory thinks it would be) to use filters and criteria to limit what you're viewing, or do you actually use different tables on the same form? Kyle

 

[striker73]

Yeah I change the recordsource quite a bit. It seems to take too much code to create another recordset. And yes I am using different tables. would it be better to use real recordsets? I'm not such a fan of the bound controls.

ps: fyi, striker is a she.

 

[KyleS]

OK, well, excuse me whilst I remove my foot (and entire leg) from my mouth...

If you're not using Bound Controls then are you setting the Recordsource property of the form and using it as a DAO.Recordset?

If that's what you're doing I would recommend just creating one recordset and closing then reusing it. This will avoid your problem altogether.

Dim rstTemp as DAO.Recordset
Set rstTemp = CurrentDB.OpenRecordset("SELECT * FROM MyTable&quot

'Do stuff here

rstTemp.Close
Set rstTemp = Nothing


Set rstTemp = CurrentDB.OpenRecordset("SELECT * FROM YourTable&quot

etc...
Kyle

 

[striker73]

heh. I don't suppose it's very healthy to be munching on one's own leg. So will that affect the performance of the database? I used something like that when I wanted to use a recordset in a global function. What's the whole DAO thing? Eventually this database will be used across a network, will that affect anything in a negative way? Thanks for your help

 

[KyleS]

Shows what you know, my leg is rather good for me!

Performance: In a 1-5 user environment, it MIGHT (very slight chance) slow the dB down a little (you won't notice) - in a multiuser environment (especially over a LAN or WAN) it will increase performance as well as give you some other benefits. This way, noone can lock up a record by leaving the dB on a record and going to lunch (or whatever else they decide to do).

DAO - Data Access Objects - Take a look here:

http://msdn.microsoft.com/library/d...sualc/vctutor/_gs_a_brief_overview_of_dao.htm

Network - As mentioned above (and in MS's site no doubt) this (or ADO if you've got A2K or XP) is a must for multi-user applications.

If you've got Access 2K or XP then you should ditch the DAO and go with ADO as it will be the faster method. But if you're running 97 like me then this is probably your best option, especially across a network.


Hope this answers some things, for info on ADO and other methods, take a look here:

http://www.microsoft.com/data/ado/default.htm

Kyle