Using rst.append and adFldKeyColumn 1

[Parke]

I am not understanding the significance of adFldKeyColumn. I thought this might make a field as in MS Access the key or primary field so that the value could only be in the recordset once. But in the following code, the string "ab" is inserted twice. Any help in clearing up what I am doing wrong or not understanding would be appreciated.

Dim arValues(5) As String
Dim rst As Recordset
Dim s As String
Dim v As Integer

arValues(0) = "ab"
arValues(1) = "bcd"
arValues(2) = "cdef"
arValues(3) = "zzz"
arValues(4) = "ab"

Set rst = New Recordset
rst.Fields.append "itemKey", adVarChar, 15, adFldKeyColumn
rst.open

For v = 0 To 4
rst.AddNew
rst.Fields("itemKey&quot = arValues(v)
rst.Update
Next

s = ""
rst.MoveFirst
Do Until rst.EOF
s = s & rst("itemKey&quot & vbCrLf
rst.MoveNext
Loop
MsgBox s
rst.close

TIA

Parke

 

[MatsHulten]

Well, if you are referring to the fact that msgbox print 'ab' as the first and last item, then I suggest you check the values you assing to the array...

I don't get any duplicate values.

If you want to crate the DB dynamically i suggest you use ADOX instead.

Try something like this:

'Need a reference to ADOX
Dim tbl As New ADOX.Table
Dim key As New ADOX.key
Dim cat As New ADOX.Catalog

' Open the Catalog.
cat.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\Program Files\Microsoft Office\" & _
    "Office\Samples\Northwind.mdb;"

' Define the table and append it to the catalog
tbl.Name = "MyTable"
tbl.Columns.Append "itemKey", adVarWChar, 50
cat.Tables.Append tbl

'Define the key and append it to the tables key collection
key.Name = "KeyName"
key.Type = adKeyPrimary
key.Columns.Append (cat.Tables("mytable").Columns("itemKey"))
cat.Tables("MyTable").Keys.Append key

Good Luck
-Mats

 

[Parke]

Mats:

So what I hear you say is that there is no way to create a recordset on the fly with a primary key and if I want to filter out duplicates I should either use a dictionary or formally create an Access db/table.

Thanks, I appreciate the help.

Parke

 

[MatsHulten]

As ADO is primarily designed to be an interface between code (VB, C++, etc) and data sources, it relies on the provider for each individual datasource to provide much of the functionality.
ADO (as far as I know) do not have any way to create a disconnected recordset with key/indexing support, as it relies on the underlying provider for that functionality.

What you could do to cach duplicates is to create the recordset using the WithEvents keyword.
Then in the WillChangeField or WillChangeRecord event check for duplicates in the record, and if you find any you can deal with it, either by setting the adStatus property to adStatusCancel or by raising an error.

If the recordset is connected to a provider that already supports the use of unique fields, this approach is unneccesary and likely to slow things down, but when you want to create a disconnected recordset it could be of use.

As of the use of dictionary objects, I can't advise you as I only just now looked up what is was.

Good Luck
-Mats