ADO & DAO in same sub? And how to null a value in a table from code? 1

[Vie]

I think my approach to this is probably way off base which is part of the reason I'm posting this question. I have a sub procedure that is actually using both ADO and DAO to perform what I would guess should be a fairly simple operation -- whatever record has the focus on the form, find the corresponding ID for that record in a table (where the record's ID is a FK but occurs only once in the table) and remove the ID.

The reason I'm using both ADO and DAO is that I need to refer both to the recordset of the table (for this I'm using ADO) and to the recordset on the form. I haven't figured a way to refer to the form's recordset and the record with the current focus using ADO whereas it is simple in DAO. So I'm mixing the two of them for now although I'd prefer to go 100% ADO if I can.

That's one problem. The other problem is that although this all works so far as finding the ID in the table that matches the ID in the form, I can't get it to change the value in the table. I get a data type mismatch error if I use something like: rst2("TenantID").Value = "" I'm not trying to delete the record, just remove the ID.

Can anyone help me out with what I'm doing wrong here. Any advice is appreciated.

 

[RoyVidar]

Try setting to Null:

rst2("TenantID").Value = Null

Which would normally work if it's not a PK, there are no validation rules on the field, it doesn't violate any enforced referential integrity rules - now, what have I forgotten?

Roy-Vidar

 

[Vie]

Roy-Vidar,

Thanks for the speedy reply. I tried setting it to Null but nothing happens. No datatype mismatch error but no nulled out value either. The field has no validation rules and is not set to enforce referential integrity. I also tried changing the value to another value but still nothing happens.

This is the block of code that I'm working on:

With rst
    intTenantID = rst!TenantID
    Debug.Print intTenantID
        With rst2
            rst2.Find "TenantID = " & intTenantID
            Debug.Print "Found match. " & rst2!TenantID
            rst2("TenantID") = Null
        End With
End With

rst is the DAO recordset and rst2 is the ADO recordset. rst2's cursor type is set to adForwardOnly and the lock type is adLockPessimistic.

Hmmmm....
Vie

 

[RoyVidar]

What happens if you do an

[tt].Update[/tt]

after assigning?

I just don't see, since you have the tenantid in your dao recordset, why not just:

[tt]rst.edit
rst!TenantID=Null
rst.update[/tt]

In stead of using ADO too. Form recordsets are DAO in Access, at least versions 2000 and xp unless it's an ADP, so we'll have to do a bit of both. Another issue, is that you might get into weirdness because of having the same table/recordset open twice...

btw - for safety, check for .eof after find, else you might have challenges if it's not found.

Roy-Vidar

 

[Vie]

Thanks again, Roy-Vidar. Actually the recordsets aren't the same, they just have this value in common. The DAO recordset's TenantID though is the PK of one of the tables used in the form's recordsource. Anyway, I think I got the code down:

With rst
intTenantID = rst!TenantID
With rst2
rst2.Find "TenantID = " & intTenantID
rst2.Update "TenantID", Null
End With
End With

Just a minor syntactical change and it works.

In any case, I'm still worried about the DAO, ADO dilemma. This database will, at some point, connect via the web to a couple of other Access databases on a remote server. I've not even begun to grasp the kinds of problems this is going to bring to light but to be on the safe side I'm guessing it would be best to have things use ADO despite the added difficulty and the extra lines of coding connections and whatnot.

What is your opinion on that? I keep hearing how DAO is going to be phased out altogether one of these days.

Thanks again,
Vie

 

[RoyVidar]

Hi!

Thanx for the star!

Just to be on the safe side:

[tt]With rst2
rst2.Find "TenantID = " & intTenantID
if not rst2.eof then rst2.Update "TenantID", Null
End With[/tt]

I've heard all of that too, and started using ADO in 2000. But since the form recordset is still DAO, we need a little of that too, from time to time. I use ADO for everything except manipulating form recordsets.

xtra lines of code - you can do something like this too, if you're within access:

[tt]set rs=new adodb.recordset
rs.open sSql, currentproject.connection, _
adopenforwardonly,adlockpessimistic,options:=adcmdtext[/tt]

- and as in DAO drop of some cause of defaults;-)

Roy-Vidar

 

[Vie]

Ah, good point. I will add that the EOF check to the code. And thanks for the advice about DAO/ADO. That makes this novice feel more comfortable with what I've been doing in that regard. Nice to know I'm not alone in mixed use. I'm going to try out your connection advice as well. Again, much thanks for your insights and help!

 

[mo2783]

You can also do it this way

rst.fields("TenantID") = Null

 

[Vie]

mo2783,

I thought that would work too and I tried it earlier (and now again at your suggestion) but it didn't work. Of everything I tried, only:

rst2.Update "TenantID", Null

worked. But thanks for the input. It's always good to know more than one way to do things and to test out all possibilities.

Vie