[RESOLVED] Need to be clarified

[Simply Me]

Hi! I need to be clarified with some issues.

when to Use:
1.

If rs.State = adStateOpen Then rs.Close
rs.Open sSQL, oConn, adOpenDynamic, adLockOptimistic

and

oConn.Execute

Questions:
1. Which is recommended from the two?
2. Is it always necessary to do this everytime I use sSQL = "SELECT * FROM TABLE"?

If rs.State = adStateOpen Then rs.Close
rs.Open sSQL, oConn, adOpenDynamic, adLockOptimistic

3. Which is recommeded, declare Dim rs As ADODB.Recordset in declaration area and Set to nothing every unload of the form or declare itin every Private Sub and set it to nothing in every Private Sub?

[morleyz]

1) You use rs.open when you want to open a recordset (SELECT). You use oConn.execute when you running a query that doesn't return a recordset(INSERT, UPDATE, DELETE, etc.)

2) Not sure what you're asking. Are you asking if you have to check the state and close the recordset before trying to open again?

3) I prefer to declare what I'm using locally in the local sub, if more than one sub needs access to something I declare it in the module's general declarations, otherwise it stays local. If for no other reason, it's helps me make sure that I deal with variables that are only local locally. So more directly, I would declare it locally in each sub.

[randem]

3. No, but you should do it because it does eliminate problems when you have a problem with your SQL statement execution and need to know exactly what the statement looks like. Then you can simply put a statement between the two

Debug.Print SQL

or put a breakpoint on the next line so you can see what the SQL statement looks like.

[Simply Me]

Thanks for the explanation.

1) You use rs.open when you want to open a recordset (SELECT). You use oConn.execute when you running a query that doesn't return a recordset(INSERT, UPDATE, DELETE, etc.)

Yes, that's is what im asking.

1)
2) Not sure what you're asking. Are you asking if you have to check the state and close the recordset before trying to open again?

Meaning it depends on the situation, right?

1)
3) I prefer to declare what I'm using locally in the local sub, if more than one sub needs access to something I declare it in the module's general declarations, otherwise it stays local. If for no other reason, it's helps me make sure that I deal with variables that are only local locally. So more directly, I would declare it locally in each sub.

[Simply Me]

What is No randem?

[morleyz]

Yes, #3 depend on the situation and I don't think either way is wrong or right...just different.

As far as #2 goes. I don't normally have to check if the recordset is already open because I keep careful track of recordsets in my code (always closing recordsets when I'm done with them). That being said, if you try to rs.open a recordset that's already open, you'll get an error. So if you don't know for sure, you should probably check.

[Simply Me]

Thanks to both of you!