ADO Seek with compond foreign key

[Magster]

Hi all,

I've never used foreign keys for searching before... so I've researched all morning and I can't find any samples of how to use a unique foreign key that is comrpised of two long fields in an ADO recordset. Maybe it has to be DAO?

I want to add new rows to tblEmpSkills from data in two lists boxes, but only if the two fields together won't cause a duplicate entry in my receiving table.

As I'm step thru the code, and when the values would be duplicate and be found and not EOF - it just sails thru and ends up in the error routine.

How can I check for duplicate values in my foreign key and skip that entry if a duplicate exists?

Set cnn = CurrentProject.Connection
Set tblEmpSkills = New ADODB.Recordset
tblEmpSkills .CursorType = adOpenStatic
tblEmpSkills .LockType = adLockOptimistic
tblEmpSkills .Open "tblEmpSkills", cnn, , , adCmdTableDirect
tblEmpSkills .Index = "UniqueSkillSet" ' index is two long fields
Dim varItem As Variant
Dim varItem2 As Variant
' Enumerate through listbox selected items and if not duplicate on foreign key, add to tblEmpSkills.
' the two values from the lstbox contain the two long fields of the foreign key
'
For Each varItem In Me.lstEmps.ItemsSelected
For Each varItem2 In Me.lstTasks.ItemsSelected
lngValue = Me.lstEmps.ItemData(varItem) & Me.lstTasks.ItemData(varItem2)
tblEmpSkills .Seek lngValue, adSeekFirstEQ
If tblEmpSkills .EOF Then ' record doesn't exist, add it
tblEmpSkills .AddNew
tblEmpSkills .Fields("intEmployeeID") = Me.lstEmps.ItemData(varItem)
tblEmpSkills .Fields("intSkillID") = Me.lstTasks.ItemData(varItem2)
tblEmpSkills .Update
End If
Next varItem2
Next varItem

'
MsgBox "Work is done"

Thanks in advance for reading this and providing help! I'm really behind on solving this issue...

 

[MarkK]

My read of the ADO seek method in Access Help is that your first parameter needs to be a variant array of index values. Rather than ...

lngValue = Me.lstEmps.ItemData(varItem) & Me.lstTasks.ItemData(varItem2)
tblEmpSkills.Seek lngValue, adSeekFirstEQ

You probably need to ...

Dim varValueArray as variant
...
...
varValueArray = Array(Me.lstEmps.ItemData(varItem), Me.lstTasks.ItemData(varItem2))
tblEmpSkills.Seek varValueArray, adSeekFirstEQ

 

[Magster]

I truly appreciate your advice - and it works perfectly. Once can be so close, yet so far away.

 

[MarkK]

You bet. Glad to help.

 

[Magster]

I wanted to share this with everyone because it really stumped me.

The above code of Lagbolt worked GREAT when I was working with a table located in my front-end database. But, that was just my testbed. The table that I wanted to work with resides in the linked back-end database which just happens to be Access 2007. So when I tried to use the code it wouldn't work. My provider was wrong.

To make a long story short, when using Access 2007 the new engine is called: Microsoft Access database engine.

Prior to Access 2007, the provider may be: cnn.Provider = "Microsoft.Jet.OLEDB.4.0" Access 2007 is: cnn.Provider = "Microsoft.ACE.OLEDB.12.0"

You must also have the following references indicated:
Microsoft ActiveX Data Objects 2.1 Library or later version
Microsoft ADO Ext 2.1 for DDL and Security or later versions

And all of this came about because I couldn't use my alternate key search and I was force to connect manually.

I've always received help from this web site, so here is my contribution back.

Thanks Lagbot for your help with the array