Run Time Error - 3251 with Linked database (1 Viewer)

Hi all,

I wrote this routine temporarily using a local table in my front-end database. No problems... however, when I changed it to use the linked table, I'm getting the famous (I've seen so many conversations concerning this...) VBA: 3251 error - Current provider does not support the necessary interface for Index functionality.

In support.microsoft.com, I found a Jan 27, 2009 Run-time error 3251 document stating that this error has been fixed in R06670 Service Pack. Has anyone downloaded this service pack and does it fix the 3251 problem?

Or - maybe something I can change in my code? From what I gather from my searching is I can't seek on either a primary or alt key with a linked table. I've asked for the download but I haven't received it yet.

Thanks!


Dim cnn As ADODB.Connection
Dim errCur As ADODB.Error
Dim lngValue As Long
Dim rstEmpSkill As ADODB.Recordset ' this will be tblEmployeeSkillSet
Dim varValueArray As Variant
' if nothing selected, exit
If Me.lstEmps.ItemsSelected.Count = 0 Or Me.lstTasks.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one entry from both lists to continue", vbCritical
Exit Sub
End If
Set cnn = CurrentProject.Connection
Set rstEmpSkill = New ADODB.Recordset
rstEmpSkill.CursorType = adOpenDynamic
rstEmpSkill.LockType = adLockOptimistic
rstEmpSkill.Open "tblEmployeeSkillSet", cnn, , , adCmdTableDirect
rstEmpSkill.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 tblEmployeeSkillSet.
' 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

varValueArray = Array(Me.lstEmps.ItemData(varItem), Me.lstTasks.ItemData(varItem2))
rstEmpSkill.Seek varValueArray, adSeekFirstEQ
If rstEmpSkill.EOF Then ' record doesn't exist, add it
rstEmpSkill.AddNew
rstEmpSkill.Fields("intEmployeeID") = Me.lstEmps.ItemData(varItem)
rstEmpSkill.Fields("intSkillID") = Me.lstTasks.ItemData(varItem2)
rstEmpSkill.Update
End If
skipdups:
Next varItem2
Next varItem
MsgBox "Your selections have been processed..."

Not sure if you want to go this route, but I used all linked tables with DOA instead of ADO and have no problems.
Instead of using the SEEK function, I just give the recordset a SQL statement when it opens.


Hope this helps,
Evan

First Evan, thanks very much for your suggestion - and maybe you can help me again...

My problem is the index... I get the same error as I mentioned above, 3251. I haven't had any problems with linked files before with either DAO or ADO, until now.

I am writing to a many to many relationship file and I have to see if there is a duplicate value prior to appending a new row. If I can't do this, then I'll have to let the row error and trap the error and continue.

I'm still trying to get my IT dept. to download the patch I also mentioned at the top.

So, would you try to open a alternate index with DAO on a linked file and let me know if that works for you?

Thanks! and I look forward to your response.



Dim rstEmpSkill As DAO.Recordset
Set rstEmpSkill = CurrentDb.OpenRecordset("tblEmployeeSkillSet")
rstEmpSkill.Index = "UniqueSkillSet" ' index is two long fields

I'm away from my Access computer now, so can't try the index function.
But, if you use an SQL statement to open the recordset with search criteria, I think it will do the same thing.


If it finds a record that matches your criteria, RS.EOF will be false.

You could also use DCount


I haven't used the Seek function in a while because I ran into problems and found other ways of getting the job done. Perhaps this means I'm missing a tool in my arsenal...

Evan