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

• mmcardle

  SSCommitted

  Points: 1543

  More actions

  June 9, 2009 at 4:55 am

  #73921

  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

  'Dim strSQL As String

  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

• mmcardle

  SSCommitted

  Points: 1543

  More actions

  June 9, 2009 at 9:31 am

  #1006789

  OK, I've spotted what I did wrong. I've obviously been staring at the code for too long because I've become blind to it!

  It was simply that the .LockType was missing an equals sign! So, it should have read:

  .LockType = adLockOptimistic

  I think the only answer to that is DOH!