how to make a connection string for ADO? 3

[chiuchimuN]

I have a database(db1.mdb) with a table(TblRec)
In a form I want to connect to TblRec by using an ADO connection but am confussed on the parameters.

I want to open the table for reading,editing,del, and adding records thru the form.

 

[LittleSmudge]

What you need is :-

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic
rst.Open "SELECT etc.. .."

' Clever code in here

rst.Close

rst.ActiveConnection sets up the connection and as you are connecting to a table in the current database then you can use "CurrentProject.Connection "

rst.CursorType = adOpenKeyset
rst.LockType = adLockPessimistic

The rst.Open line is usually show in the example code in help files containing all of the Connection, Cursor, Lock etc info all in one line.
It does not have to be.
To my mind laying it out as above makes it easier to read and hence debug.

If all the other parameters are pre-set - then the rst.Open line just needs to contain the SQL string that defines the recordset.

rst.Open "SELECT etc.. .."




Then don't forget to
rst.Close
when you've finished with it.

By doing it this way you can re-use the same Recordset object with different recordsets in one module.
eg.
..
all the lines before rst.Open then
rst.Open "SELECT * FROM tbl1 etc First recordset
' Your first lot of work
rst.Close
rst.Open "SELECT * FROM tbl2 etc Secord recordset
' Your second lot of work
rst.Close
rst.Open " SELE .. etc.

You don't need to re-set the
rst.Connect.. ..
rst.Lock.. ..
rst.Cursor.. ..


'ope-that-'elps

G LS

 

[chiuchimuN]

If I want to open all the recordsets in a table can I use:

Recset.open("select * from Tablename&quot

 

[Quintios]

Well, I wouldn't refer to the information in a table as a recordset. What you grab *from* the table is a recordset, but what's *in* the table are records.

But yes, using "SELECT * FROM tablename" would be the correct syntax.

I'll include two observations as I've recently grasped the concept of DAO and ADO:

1. People (and the computer) always use CAPITALS when typing in the keywords for an SQL statement. It makes it easier to read.

2. It seems rare that the actual string is put into an SQL statement, but rather a string variable into which the string was entered. This is especially useful for long SQL strings.

For example:

strSQL = "SELECT TagName, Sequence from tblTags"
strSQL = strSQL & " WHERE Sequence = " & intSeqNum
strSQL = strSQL & " AND TagTypeID = 9"
strSQL = strSQL & " ORDER BY tblTags.SortOrder"

So that way you just have the variable 'strSQL' in your 'open' statement. It makes it cleaner overall I think, plus if you want to check your statement before you run it, all you have to do is enter:

msgbox strSQL

Have fun!
Onwards,

Q-

 

[LittleSmudge]

An alternative to Quintios multi-line build up of the SQL string in the example above is to use the line extender underline character

For example:

strSQL = "SELECT TagName, Sequence FROM tblTags " _
       & "WHERE Sequence = " & intSeqNum & " " _
       & "AND TagTypeID = 9 " _
       & "ORDER BY tblTags.SortOrder"

This does exacly the same as Quintios example but to my mind is easier to read/debug/etc.

And as Quintios rightly said Capitalise all Key Words ( SELECT, FROM, WHERE etc ) to make reading easier.
Then add
MsgBox strSQL
when you want to debug the misbehaving thing.


G LS

 

[Quintios]

I HATE LINE EXTENDERS!



There, I said it. I feel better.

Onwards,

Q-

 

[LittleSmudge]

And I would never want to force you into anything that you're not comfortable with Quintios - but lets give Kurage the available options and the opportunity to experiment and find his own style.


Personally I LOVE line extenders
They're pretty
They're efficient
I'm waffling
.. ..


G LS

 

[Quintios]

You're too darn nice... ;-)
Onwards,

Q-

 

[chiuchimuN]

Thanks for the info guys. I'll make my next project ADO. This time I'm sticking to DAO and use ADO after a little experimentation.

 

[scking]

I believe line extenders are significantly easier to read and are an excellent aid to debugging. Great idea LittleSmudge.

Steve King Growth follows a healthy professional curiosity