Binding MS Access form to ADO recordset: Invalid use of property

[mmcardle via AccessMonster.com]

Hi all,

I have an Access project that I'm using to access SQL Server 2000 data using
ADO recordsets and I've done quite a few now, but I've stumbled on this one.

I have an unbound form and want to bind an ADO recordset to it and then use
the control source on the Access form fields to display the returned data as
a datasheet. I found a MS KB article (281998) that pointed me in the right
direction with this and I've done a few test runs in a blank database to make
sure I understand it. In my live project however, it doesn't work. The code
is shown below:

Dim cnn As ADODB.Connection
Dim rsLines As ADODB.Recordset

Set cnn = CurrentProject.AccessConnection

Set rsLines = New ADODB.Recordset
With rsLines
Set .ActiveConnection = cnn
.Source = "SELECT [Item], [Product], [Qty], [Price], [Extended Price],
[Req'd Date] FROM xACKlines"
.LockType adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rsLines

Set rsLines = Nothing
Set cnn = Nothing


This is quite literally exactly as it is coded in the article and worked fine
in my test runs.

In my live project though, it falls over with VBA compile error "invalid use
of property" on this bit:

.LockType adLockOptimistic

Does anyone have any ideas why this might be?

Many thanks,

Mark

 

[Alex Dybenko]

Hi,
looks like you missed equal sign, try:

..LockType = adLockOptimistic

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[mmcardle via AccessMonster.com]

Hi Alex,

You are, of course, utterly right and I feel like such a fool for not
spotting that!

I had been looking at that bit of code for so long that I had become blind to
the problems in it!

Thanks for pointing me in the right direction.

Cheers,

Mark