ADO Recordset Form Binding 4

[SiJP]

Database: MS Access 2003

I am trying to bind a form to an ado recordset, but having a little problem with the updating side of things. The fields populate ok, however when trying to update any of the data, I get the "This recordset is not updatable" message.

Here is my code:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
           
'Open connection to object
    Set cn = New ADODB.Connection
    With cn
      .Provider = "MSDataShape"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "127.0.0.1" 'local loopback for now, until live.
      .Properties("User ID").Value = "user"
      .Properties("Password").Value = "pass"
      .Properties("Initial Catalog").Value = "UsersDatabase"
      .CursorLocation = adUseServer
      .Open
    End With

'Create instance of the recordset
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT tblUsers.* FROM tblUsers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With

'Bind controls within the form to the ado recordset
    Set Me.Recordset = rs

'Close the connections
    rs.Close
    cn.Close

I've a feeling one of the locks is not going server side, but dont know which :|


------------------------
Hit any User to continue

 

[RoyVidar]

As usual, I'm on thin ice, haven't tried it, but I thought the MSDataShape thingie was needed only for the 2000 version? Though I haven't used IP, might have a look here How to bind Microsoft Access forms to ADO recordsets (2002/2003)

Roy-Vidar

 

[SiJP]

Hi Roy,

Yep, the MSDataShape i left in there from a variety of different providers that I tried, with no avail. I'd also tried the KB article as per your link with the same results.

I wouldn't have thought using a loopback address would cause the recordset to be read only, so I tried by connecting to a different SQL server within our network.. the recordset IS updatable.

On both servers (local and network) I was using the sa account, same table and same SQL statement. The only variable was literally the Data Source. My first example used the loopback address, the second used named pipes to a networked server.

I would assume therefore you cannot create updatable recordsets for local servers :/

Thanks for the idea of checking the IP out.

------------------------
Hit any User to continue

 

[imterpsfan3]

I'm using a Data Shape hierarchical recordset for a form-subform-subform unbound application and it works fine. I'm not sure if this works for bound data. But then again I don't like using bound forms.

 

[imterpsfan3]

The question I have is why you are using a datashape provider? It appears to me that you are deriving data from only one table or am I wrong? Data shaping is only relevant when you need to return a recordset from several related tables and want to keep the data synchronized. Otherwise I would use a client-side recordset with the SQLOLEDB provider.

The other thing you may want to consider is that even if you bind an Access form to an ADO recordset, a DAO recordset is still created behind the scenes - which increases the workload. This is another reason I don't use bound forms. I know this was true for Access 2000. I am not sure if this got ironed out for the later versions.

You might want to check out the book "Access 2000 Developers Guide to SQL Server". It's really the best book out there if you are planning on designing a solution of an Access front end to a back end SQL Server.

 

[SiJP]

Helpful thoughts imterpsfan3.

The datashape was used as part of many providers I tested, rather than the only one.

At the moment, I'm just playing around with bound forms as a code saving exercise, but of course as the old saying goes, there's more than one way to skin a cat!

I'm not going to delve too deep into this issue (sorry for any onlookers looking for a solution) as ultimately anything released to end users I'm probably going to do using managed code - it was just helpful to get a quick interface up and running in access to get an idea of what I wanted.. talk about stumbling at the first hurdle!

Cheers for the thoughts guys,

------------------------
Hit any User to continue

 

[RoyVidar]

The other thing you may want to consider is that even if you bind an Access form to an ADO recordset, a DAO recordset is still created behind the scenes - which increases the workload. This is another reason I don't use bound forms. I know this was true for Access 2000. I am not sure if this got ironed out for the later versions." - if this is true, I think it must be said that it's probably rather far behind the scenes, and using very clever ways of not making us find out. When trying the following on a2k - a2k3 with forms assigned ADO recordsets

[tt]dim rs as object
set rs = me.recordsetclone ' <- on a2k2, a2k3
' set rs = me.recordset.clone ' <- on a2k
if typeof rs is dao.recordset then
msgbox "DAO ..."
elseif typeof rs is adodb.recordset then
msgbox "seems to be ADO ..."
else
msgbox "dunno ..."
end if[/tt]

it's the second one popping up ...

SiJP - so you weren't able to make the recordset updeateable? Not that I've experienced much, but using the same setup as in the Microsoft KB article, has given me updateable form recordsets in a2k2 and a2k3, while other variations have not.

Roy-Vidar

 

[SiJP]

Hi Roy,

Yes, I did get the recordset to be updateable using the emicrosoft code. In order to do so I needed to use a SQL server that was not local to the code being executed (which kind of doesn't makes sense as to why for me).

Data source that didn't work: "127.0.0.1"
Data source that did work: "DEVSQL02"



------------------------
Hit any User to continue

 

[imterpsfan3]

Oddly enough however I am returning an ADODB.Recordset when I use the TypeOf. Maybe this is something that was fixed in further iterations of Jet through a service pack. These books could have been released prior to this.

 

[RoyVidar]

It's just the same as in the link though, but this gives updateable form recordsets on two of my setups with local databases (a2k2 local/a2k3 named instance - both MSDE)

[tt] With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
' Named instance (a2k3 - MSDE)
.Properties("Data Source").Value = "<server>\<instance>"
' (Local) (a2k2 - MSDE)
'.Properties("Data Source").Value = "(Local)"
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = "<mydb>"
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
strSql = "select * from mytable"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open strSql, , , , adcmdtext
End With[/tt]

Roy-Vidar