Return a recordset from an MDB

Using Office 2003 and Windows XP:

I have the following code which is functioning well, now I have two questions:

1) Is it necessary to specify a CursorLocation ? - If so what value should
be used?

2) I need to return a RecordSet from the DB, could someone please post
example code on how to do this?

sFullNameSource = "C:\NorthWind.mdb"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.CursorLocation = 1 '<<<Is this necessary???
cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cn.Open "Data Source=" & sFullNameSource & ";"
If cn.State = 1 Then Msgbox "Connected..."
sSQL = "Select * From Customers;"
rs.Open ???????????????????????????

Thanks much in advance...

Dave
Fri Apr 06 13:31:17 CDT 2007

Give this a go.

Const adLockReadOnly=1
Const adOpenForwardOnly=0

Dim strConnect, strSQL
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data " _
& "Source=C:\Program Files\Microsoft " _
& "Office\Office12\SAMPLES\Northwind.mdb;" _
& "Persist Security Info=False"

strSQL ="SELECT Customers.ContactName " _
& "FROM Customers " _
& "WHERE (((Customers.CustomerID)='EASTC')); "


Set objConnection=CreateObject("ADODB.Connection")
Set objRecordset=CreateObject("ADODB.Recordset")

objConnection.Open strConnect
objRecordset.Open strSQL, objConnection, _
adOpenForwardOnly, adLockReadOnly
Do While objRecordset.EOF = False
Wscript.Echo objRecordset("ContactName")
objRecordset.MoveNext
Loop
objRecordset.Close
objConnection.Close

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"XP" wrote:
> Using Office 2003 and Windows XP:
>
> I have the following code which is functioning well, now I have two
> questions:
>
> 1) Is it necessary to specify a CursorLocation ? - If so what value should
> be used?
>
> 2) I need to return a RecordSet from the DB, could someone please post
> example code on how to do this?
>
> sFullNameSource = "C:\NorthWind.mdb"
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.CursorLocation = 1 '<<<Is this necessary???
> cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
> cn.Open "Data Source=" & sFullNameSource & ";"
> If cn.State = 1 Then Msgbox "Connected..."
> sSQL = "Select * From Customers;"
> rs.Open ???????????????????????????
>
> Thanks much in advance...

Richard
Fri Apr 06 13:43:16 CDT 2007


"XP" <XP@discussions.microsoft.com> wrote in message
news:550C8ABC-0B03-43C4-8B7A-32FA7F53AD89@microsoft.com...
> Using Office 2003 and Windows XP:
>
> I have the following code which is functioning well, now I have two
> questions:
>
> 1) Is it necessary to specify a CursorLocation ? - If so what value should
> be used?
>
> 2) I need to return a RecordSet from the DB, could someone please post
> example code on how to do this?
>
> sFullNameSource = "C:\NorthWind.mdb"
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.CursorLocation = 1 '<<<Is this necessary???
> cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
> cn.Open "Data Source=" & sFullNameSource & ";"
> If cn.State = 1 Then Msgbox "Connected..."
> sSQL = "Select * From Customers;"
> rs.Open ???????????????????????????
>
> Thanks much in advance...

The only time I use the cursorLocation property is when I need to use the
Execute method of the Command object to return a Recordset, because I need
to enable paging (a property of the Command object) and I also want to use a
cursor that allows forward and backup movement (so I can retrieve
RecordCount and still enumerate the Recordset). In that case (rare) I set
cursorLocation to 3 (adUseClient). Documentation indicates cursorLocation of
1 means adUseNone, which is obsolete. The default (2 which is asUseServer)
is fine. I would say to not set a value.

Below is code similar to what I use to query Access database.
=================
strPath = "C:\Scripts\Customers"
strConnect = "DRIVER=Microsoft Access Driver (*.mdb);" _
& "FIL=MS Access;DriverID=25;DefaultDir=" & strPath _
& ";DBQ=" & strPath & "\Customers.mdb;"

Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.ActiveConnection = strConnect
adoRecordset.Source = "SELECT CustomerID, ContactName, Email, Phone " _
& "FROM Customers"
adoRecordset.Open

Do Until adoRecordset.EOF
strCustomerID = adoRecordset.Fields("CustomerID").Value
strContact = adoRecordset.Fields("ContactName").Value
strEmail = adoRecordset.Fields("Email").Value
strPhone = adoRecordset.Fields("Phone").Value
Wscript.Echo strCustomerID & ";" & strContact _
& ";" & strEmail & ";" & strPhone
adoRecordset.MoveNext
Loop
adoRecordset.Close

Set adoRecordset = Nothing
============
I have also used:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strPath & "\Customers.mdb"

There are many ways to do these things. The method above skips the
Connection object. If you use a Connection object, then you assign the
Connection object to the ActiveConnection property of the Recordset object.
I either assign the query to the Source property of a Recordset object, or
to the CommandText property of a Command object (after which the Execute
method of the Command object is assigned to a Recordset object). So many
options can be confusing. The snippet I posted seems simplest.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Bob
Fri Apr 06 15:12:28 CDT 2007

XP wrote:
> Using Office 2003 and Windows XP:
>
> I have the following code which is functioning well, now I have two
> questions:
>
> 1) Is it necessary to specify a CursorLocation ?

If you don't, the default server-side cursor will be used
> - If so what value
> should
> be used?
It depends on the functionality required. This question cannot be answered
in a vacuum. Read about the different cursor types and the effect of cursor
location on the ability of ADO to supply the desired cursor type in the ADO
documentation. Here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp

>
> 2) I need to return a RecordSet from the DB, could someone please post
> example code on how to do this?
>
> sFullNameSource = "C:\NorthWind.mdb"
> Set cn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> cn.CursorLocation = 1 '<<<Is this necessary???

Again, it depends on the functionality you require. "1" (adUseServer) is the
default setting, so, if that is the type of cursor you need, then no, you
don't really need to specify it.

> cn.Provider = "Microsoft.Jet.OLEDB.4.0;"
> cn.Open "Data Source=" & sFullNameSource & ";"
> If cn.State = 1 Then Msgbox "Connected..."
> sSQL = "Select * From Customers;"

Read this: http://www.aspfaq.com/show.asp?id=2096

> rs.Open ???????????????????????????
>
> Thanks much in advance...

For the simplest (an most efficient) cursor type, a server-side,
forward-only cursor, all you need to do is this:

set cn=CreateObject("ADODB.Connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFullNameSource
set rs=cn.Execute(sSQL,,1) '1=adCmdText

For more expensive cursor types, you need to instantiate a recordset object
(as you did above) and specify the cursor type, either in the Open command
or by setting the property before calling Open. For example, to open a
server-side dynamic cursor, you could do this:

set cn=CreateObject("ADODB.Connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFullNameSource
Set rs = CreateObject("ADODB.Recordset")
const adOpenDynamic = 2
const adCmdText= 1
rs.Open sSQL,cn,adOpenDynamic,,adCmdText

Again, read the documentation for a more thorough explanation.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bob
Fri Apr 06 15:37:19 CDT 2007

Richard Mueller [MVP] wrote:
>
> The only time I use the cursorLocation property is when I need to use
> the Execute method of the Command object to return a Recordset,
> because I need to enable paging (a property of the Command object)
> and I also want to use a cursor that allows forward and backup
> movement (so I can retrieve RecordCount and still enumerate the
> Recordset).

1. I don't think you are making this clear. It's the cursor type, not its
location, that determines whether it supports the functionality you
describe. Of course, specifying a client-side cursor limits one to a Static
cursor type. All four cursor types are available with a server-side cursor.

2. You never need to use a Command's Execute method to open a recordset. If
all you want is the default cursor type, then sure, use Execute. If you want
a different cursor type, use the recordset's Open method, specifying the
Command object as the second argument:
rs.Open ,cmd,adopenDynamic


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Richard
Fri Apr 06 18:03:45 CDT 2007


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OomeTtIeHHA.4384@TK2MSFTNGP03.phx.gbl...
> Richard Mueller [MVP] wrote:
>>
>> The only time I use the cursorLocation property is when I need to use
>> the Execute method of the Command object to return a Recordset,
>> because I need to enable paging (a property of the Command object)
>> and I also want to use a cursor that allows forward and backup
>> movement (so I can retrieve RecordCount and still enumerate the
>> Recordset).
>
> 1. I don't think you are making this clear. It's the cursor type, not its
> location, that determines whether it supports the functionality you
> describe. Of course, specifying a client-side cursor limits one to a
> Static cursor type. All four cursor types are available with a server-side
> cursor.
>
> 2. You never need to use a Command's Execute method to open a recordset.
> If all you want is the default cursor type, then sure, use Execute. If you
> want a different cursor type, use the recordset's Open method, specifying
> the Command object as the second argument:
> rs.Open ,cmd,adopenDynamic

You are right and I ordinarily set cursorType on the Recordset object.
However, a question recently came up where someone wanted to turn on paging
(requiring a Command object) and also a cursorType that allowed MoveFirst to
be used after RecordCount. If you use the Command's Execute method to create
the Recordset, you cannot assign cursorType. I found that assigning
cursorLocation on the Connection object worked. I think it changes the
default cursorType. See the thread in this newsgroup subject "ADO Recordset
vs Command and AD" dated April 4, 2007. We figured this out from:

http://support.microsoft.com/kb/188857/EN-US/

Do you know a way to enable paging without a Command object? This is
probably only an issue with Active Directory. Unless we use:

adoCommand.Properties("Page Size") = 100

ADSI will only return 1000 rows. The value assigned is not important, the
max is 1000. It should be rare to require both paging and a cursorType that
allows MoveFirst, but the question was asked. I also assign a timeout on the
command object, which I think is different from the timeout property of a
connection object.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Bob
Fri Apr 06 19:30:51 CDT 2007

Richard Mueller [MVP] wrote:
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OomeTtIeHHA.4384@TK2MSFTNGP03.phx.gbl...
>> Richard Mueller [MVP] wrote:
>>>
>>> The only time I use the cursorLocation property is when I need to
>>> use the Execute method of the Command object to return a Recordset,
>>> because I need to enable paging (a property of the Command object)
>>> and I also want to use a cursor that allows forward and backup
>>> movement (so I can retrieve RecordCount and still enumerate the
>>> Recordset).
>>
>> 1. I don't think you are making this clear. It's the cursor type,
>> not its location, that determines whether it supports the
>> functionality you describe. Of course, specifying a client-side
>> cursor limits one to a Static cursor type. All four cursor types are
>> available with a server-side cursor.
>>
>> 2. You never need to use a Command's Execute method to open a
>> recordset. If all you want is the default cursor type, then sure,
>> use Execute. If you want a different cursor type, use the
>> recordset's Open method, specifying the Command object as the second
>> argument: rs.Open ,cmd,adopenDynamic
>
> You are right and I ordinarily set cursorType on the Recordset object.
> However, a question recently came up where someone wanted to turn on
> paging (requiring a Command object) and also a cursorType that
> allowed MoveFirst to be used after RecordCount. If you use the
> Command's Execute method to create the Recordset, you cannot assign
> cursorType. I found that assigning cursorLocation on the Connection
> object worked. I think it changes the default cursorType. See the
> thread in this newsgroup subject "ADO Recordset vs Command and AD"
> dated April 4, 2007. We figured this out from:
Execute (whether it's conn.Execute or Cmd.Execute) always returns a new
recordset that uses the defaults supplied by the connection. Instead of
changing the connection's cursorlocation and using Execute to open the
recordset, you could have set the recordset's cursorlocation property and
used rs.Open cmd to open it on the Command. 6 of one, half dozen of the
other.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"