ADO Access For Value List Error

[George Lee]

I’m using ADO for reading and write an Access 2007 database. Writing to most
fields is no problem. Except, for value lists (where only selected values may
be entered). I get an error on that line. How do you write to value
list-defined fields?

Private Sub Command1_Click()
On Error GoTo ErrorHappened

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\TrialDatabase.accdb;Persist Security Info=False;"
cn.Open

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Trial_List", cn, adOpenKeyset, adLockPessimistic, adCmdTable

rs.AddNew
rs.Fields("Title") = "Favorite Foods"
rs.Fields("Cake_Type") = "Cheese cake" 'Value list entry. Error here.
rs.Update

Exit Sub

ErrorHappened:
MsgBox Err.Number
End Sub

 

[Alex Dybenko]

Hi,
here a sample for DAO, but don’t know if same approach works in ADO, suggest
you to use DAO
http://msdn.microsoft.com/en-us/library/bb258183.aspx

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

 

[GB]

Not sure if what I am thinking matches your problem (or problem description
for that matter), but if you have a list of items that is based on another
table/query, and it stores something other than what you are trying to set..
I.e. if item 1 of the table is Cheese Cake, and the bound column is 1 and
column 1 is the item number, then trying to set it to Cheese Cake will be an
error. If you set it to the value of 1, then it would "present" the
information associated to Cheese Cake, which in this case would be Cheese
Cake.

Sorry, if just clogging up the electronic message board, not familiar with
some of the calls made in the provided code, but is a situation that I have
run across in other databases.

 

[George Lee]

Good suggestion. I'm a .NET programmer who inherited this project, and this
wouldn't have been my approach if it were up to me. I'd rather not have to
rewrite it. At first glance, I thought it'd be easy, but I can't find any
reference to this through Google, yet.

 

[David W. Fenton]

I'm a .NET programmer who inherited this project, and this
wouldn't have been my approach if it were up to me. I'd rather not
have to rewrite it. At first glance, I thought it'd be easy, but I
can't find any reference to this through Google, yet.

The reason the DAO code works the way it is is because the
multivalue field is implemented behind the scenes as an actual
many-to-many join table. I have no idea where it is actually stored,
but when working in Access the UI hides all that behind the scenes.
In code, you have to deal with the real structures.

I hope that if you try using a child ADO recordset you'll report
back with the results. While I never use ADO (nor would I ever use
the multivalue field type -- I'm competent enough to manage N:N
structures myself!), I'd be interested to know if the approach
works. I'd likely think that it would, but if it doesn't, then it's
an interesting point, as it shows that the Access team is not
updating ADO to keep up with the ACE's features. That would be
fairly ironic given the history of ADO and DAO in the last 10 years.