adodb recordset

[Jason]

Hi,

I'm a bit of a newbie and i need to learn more about ado, but i want to
start working with recordsets.

Basically what i would like to know is how to bind the ado-recordset to a
form in an access.mdb. Also i would like to know how i can pass a
parameter(s) from a form to stored procedures.

I've created a adodb connectionstring, so i hope i did it the right way. If
not please correct me.

Here's the code:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Provider = "SQLOLEDB"
cnn.Properties("Data Source") = "local"
cnn.Properties("Initial Catalog") = "Test"
cnn.Properties("User ID") = "sa"
cnn.Properties("Password") = "R44d"

If anyone can teach me a little bit, i would be very greatful.

 

[Douglas J. Steele]

At a quick glance, that looks okay. For an extremely detailed list of
possible connection strings, check out what Carl Prothman has at
http://www.able-consulting.com/ado_conn.htm

 

[Jason]

Hi Douglas,

Thnx for the tip, but can you help me with binding the recordset to a form.
I don't know how to do that.
This i would know also for stored procedures with parameters.

J

At a quick glance, that looks okay. For an extremely detailed list of
possible connection strings, check out what Carl Prothman has at
http://www.able-consulting.com/ado_conn.htm

 

[Douglas J. Steele]

From the Help file:

Global rstSuppliers As ADODB.Recordset

Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
End Sub

Take a look at
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp
for stored procedures & parameters

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Thnx for the tip, but can you help me with binding the recordset to a
form.
I don't know how to do that.
This i would know also for stored procedures with parameters.

J

 

[Ron Weiner]

Doug

Have you tried this code? When I use a variant of the code you posted I can
bind an Access form to the ADODB recordset, however either the form or the
recordset is reported as read only depending on the cursor location. Here
is the code I used for testing.

Dim rstSuppliers As ADODB.Recordset

Private Sub Form_Load()
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set Me.Recordset = rstSuppliers
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstSuppliers.Close
Set rstSuppliers = Nothing
End Sub

For testing purpose all this code was just jammed into a form. Again for
testing purpose I used just a couple of bound text boxes, and the table was
a LOCAL Access table. Works great for read! Refuses to allow updates/adds.

When the cursor location is adUseClient the form reports that the RECORDSET
is not updateable. When the cursor location is adUseNone Access reports
that the FORM is read only.

I am using a (I think) fully patched Access 2K SP3 on Win 2K workstation to
do this testing. What am I missing here? I'd like to be able to scale this
up so it could use this technique with Sql Server data sources.

Ron W

From the Help file:

Global rstSuppliers As ADODB.Recordset

Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
End Sub

Take a look at
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp
for stored procedures & parameters

 

[Jason]

Hello Doug,

Again thanks for the tip. However i tried to implement the method described
in
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp,
but i still cannot manage it to bind it to a form.

Could you help me out?

J

From the Help file:

Global rstSuppliers As ADODB.Recordset

Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
End Sub

Take a look at
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp
for stored procedures & parameters

 

[Douglas J. Steele]

Sorry, no, I haven't, because I rarely use ADO with Access (and never with
bound forms)

See whether there's anything of use in
http://support.microsoft.com/default.aspx?id=225048

http://support.microsoft.com/?id=190108 might be relevant.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug

Have you tried this code? When I use a variant of the code you posted I
can
bind an Access form to the ADODB recordset, however either the form or the
recordset is reported as read only depending on the cursor location. Here
is the code I used for testing.

Dim rstSuppliers As ADODB.Recordset

Private Sub Form_Load()
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set Me.Recordset = rstSuppliers
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstSuppliers.Close
Set rstSuppliers = Nothing
End Sub

For testing purpose all this code was just jammed into a form. Again for
testing purpose I used just a couple of bound text boxes, and the table
was
a LOCAL Access table. Works great for read! Refuses to allow
updates/adds.

When the cursor location is adUseClient the form reports that the
RECORDSET
is not updateable. When the cursor location is adUseNone Access reports
that the FORM is read only.

I am using a (I think) fully patched Access 2K SP3 on Win 2K workstation
to
do this testing. What am I missing here? I'd like to be able to scale
this
up so it could use this technique with Sql Server data sources.

Ron W

 

[Douglas J. Steele]

What exactly did you do?

And just to be certain, you're not expecting to have prompts come up, like
you would with a Jet parameter query, are you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Doug,

Again thanks for the tip. However i tried to implement the method
described
in
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp,
but i still cannot manage it to bind it to a form.

Could you help me out?

J

 

[Alex Dybenko]

Hi Ron,
AFAIR - while you bind form to ADO recorset connected to SQL Server - form
is ready only. this is by design. check online help for more info

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Doug

Have you tried this code? When I use a variant of the code you posted I
can
bind an Access form to the ADODB recordset, however either the form or the
recordset is reported as read only depending on the cursor location. Here
is the code I used for testing.

Dim rstSuppliers As ADODB.Recordset

Private Sub Form_Load()
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Set Me.Recordset = rstSuppliers
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstSuppliers.Close
Set rstSuppliers = Nothing
End Sub

For testing purpose all this code was just jammed into a form. Again for
testing purpose I used just a couple of bound text boxes, and the table
was
a LOCAL Access table. Works great for read! Refuses to allow
updates/adds.

When the cursor location is adUseClient the form reports that the
RECORDSET
is not updateable. When the cursor location is adUseNone Access reports
that the FORM is read only.

I am using a (I think) fully patched Access 2K SP3 on Win 2K workstation
to
do this testing. What am I missing here? I'd like to be able to scale
this
up so it could use this technique with Sql Server data sources.

Ron W

 

[Jason]

Hi Doug,

In the meantime i've manage to bind the results of a parameterized stored
procedure to a form.

The problem i'm having now is that when i pass nulls to the procedure, i'm
getting an errormessage which tells me to use the convert function. Can you
help me with fixing the code. I've placed the adjusted ado code and stored
procedure below. The stored procedure consist of 2 parameters.

Private Sub Form_Open(Cancel As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.parameter
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection

With cnn
.Provider = "sqloledb"
.Properties("Data Source") = "local"
.Properties("Initial Catalog") = "test"
.Properties("User ID") = "sa"
.Properties("Password") = "password"
End With

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
cnn.Open cnn
cnn.usp_searchPersons "808", Null, rst

Set Forms("Form1").Recordset = rst

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

End Sub
------------
CREATE PROCEDURE usp_SearchPersons (
@Id nvarchar(10) = null ,
@DOB char(10) = null
)
AS

SELECT id,
LTRIM(Firstname) + ', ' + LTRIM(Lastname) as Name,
DateOfBirth,
Personnr
FROM Persons
WHERE Id like coalesce(@Id,'') + '%'
OR DateOfBirth = coalesce(@DOB,'')
order by DateOfBirth
GO

What exactly did you do?

And just to be certain, you're not expecting to have prompts come up, like
you would with a Jet parameter query, are you?

 

[Ron Weiner]

Alex

Darn! Would have been sweet if it was possible to bind a Read/Write ADO
recordset (especially a disconnected one) to a form though. Coulda' rotated
the earth!

Ron W

Hi Ron,
AFAIR - while you bind form to ADO recorset connected to SQL Server - form
is ready only. this is by design. check online help for more info

 

[Jason]

Hi Ron,

It seems what you want can only be done in access 2003. I've adjusted my
code to use it in access 2000, maybe this is what your looking for.

Private Sub Form_Open(Cancel As Integer)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
With cnn
.Provider = "MSDataShape"
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
SOURCE=local;DATABASE=test;UID=sa;PWD=password;"
.CursorLocation = adUseServer
.Open
End With

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cnn
.Source = "SELECT * FROM Test"
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = rst

Set rst = Nothing
Set cnn = Nothing
End Sub

Alex

Darn! Would have been sweet if it was possible to bind a Read/Write ADO
recordset (especially a disconnected one) to a form though. Coulda' rotated
the earth!

Ron W

Hi Ron,
AFAIR - while you bind form to ADO recorset connected to SQL Server - form
is ready only. this is by design. check online help for more info

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/NamedParameters.asp

 

[Alex Dybenko]

Ron,
ADO recordset from Jet should be updatable in this case. Do not know if it
help you, but you can try to copy table to mdb and then open recordset from
mdb. I used once this workariund and it worked fine at me

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Alex

Darn! Would have been sweet if it was possible to bind a Read/Write ADO
recordset (especially a disconnected one) to a form though. Coulda'
rotated
the earth!

Ron W

 

[Ron Weiner]

Alex

Actually the table I was using was in an MDB, AND it was in the same MDB as
was the form I was experimenting with. Again Here is the Code I used.

Dim rstSuppliers As ADODB.Recordset

Private Sub Form_Load()

Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From MyTable", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
Set Me.Recordset = rstSuppliers
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstSuppliers.Close
Set rstSuppliers = Nothing
End Sub

Seem's to me that this should work, but at least in Access 2000 SP3 on a
patched Win 2K workstation it does not allow updates or adds.


Ron W

Ron,
ADO recordset from Jet should be updatable in this case. Do not know if it
help you, but you can try to copy table to mdb and then open recordset from
mdb. I used once this workariund and it worked fine at me

 

[Douglas J. Steele]

I wouldn't have expected cnn.usp_searchPersons "808", Null, rst to work with
or without the Null, so I'm afraid I'm not going to be able to help you
debug it!

Sorry.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

In the meantime i've manage to bind the results of a parameterized stored
procedure to a form.

The problem i'm having now is that when i pass nulls to the procedure, i'm
getting an errormessage which tells me to use the convert function. Can
you
help me with fixing the code. I've placed the adjusted ado code and stored
procedure below. The stored procedure consist of 2 parameters.

Private Sub Form_Open(Cancel As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.parameter
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection

With cnn
.Provider = "sqloledb"
.Properties("Data Source") = "local"
.Properties("Initial Catalog") = "test"
.Properties("User ID") = "sa"
.Properties("Password") = "password"
End With

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
cnn.Open cnn
cnn.usp_searchPersons "808", Null, rst

Set Forms("Form1").Recordset = rst

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

End Sub
------------
CREATE PROCEDURE usp_SearchPersons (
@Id nvarchar(10) = null ,
@DOB char(10) = null
)
AS

SELECT id,
LTRIM(Firstname) + ', ' + LTRIM(Lastname) as Name,
DateOfBirth,
Personnr
FROM Persons
WHERE Id like coalesce(@Id,'') + '%'
OR DateOfBirth = coalesce(@DOB,'')
order by DateOfBirth
GO

 

[Alex Dybenko]

Hi Ron,
i am sorry, have found my old code and see that i was wrong.
I actually used DAO recordset, see a sample (code from form_open event):

Set dbs = DBEngine.OpenDatabase(CurrentProject.Path & "\Mailmerge.mdb")
strSQL = "SELECT * FROM MailMerge ORDER BY Bedrijf"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set Me.Recordset = rst


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Alex

Actually the table I was using was in an MDB, AND it was in the same MDB
as
was the form I was experimenting with. Again Here is the Code I used.

Dim rstSuppliers As ADODB.Recordset

Private Sub Form_Load()

Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From MyTable", _
CurrentProject.Connection, adOpenStatic, adLockOptimistic
Set Me.Recordset = rstSuppliers
End Sub

Private Sub Form_Unload(Cancel As Integer)
rstSuppliers.Close
Set rstSuppliers = Nothing
End Sub

Seem's to me that this should work, but at least in Access 2000 SP3 on a
patched Win 2K workstation it does not allow updates or adds.


Ron W

 

[Ron Weiner]

Alex

Yea that makes sense. What I was really trying for was to see if I could
bind disconnected ADO recordsets to Access Forms. It aint the end of the
world we will go with either unbound forms or with forms bound to linked
temp tables in a special purpose Access MDB backend.

Many thanks to all for their help.

Ron W

Hi Ron,
i am sorry, have found my old code and see that i was wrong.
I actually used DAO recordset, see a sample (code from form_open event):

Set dbs = DBEngine.OpenDatabase(CurrentProject.Path & "\Mailmerge.mdb")
strSQL = "SELECT * FROM MailMerge ORDER BY Bedrijf"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Set Me.Recordset = rst