Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-21-2009, 01:53 PM   #1
Magster
Newly Registered User
 
Join Date: Jul 2008
Location: Arizona
Posts: 115
Thanks: 11
Thanked 1 Time in 1 Post
Magster is on a distinguished road
Run Time Error - 3251 with Linked database

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..."

Magster is offline   Reply With Quote
Old 04-21-2009, 06:36 PM   #2
evanscamman
Newly Registered User
 
Join Date: Feb 2007
Posts: 274
Thanks: 0
Thanked 1 Time in 1 Post
evanscamman is on a distinguished road
Re: Run Time Error - 3251 with Linked database

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.

Code:
Dim rs as DAO.Recordset, rs2 as DAO.Recordset
Dim strSQL as string
 
'Open 1st Recordset
strSQL = "Select from MyTable blah blah blah... WHERE CustomerID = Blah Blah"
set rs = CurrentDB.Openrecordset(strSQL)
 
strSQL = "Select ....."
set rs2 = CurrentDB.Openrecordset(strSQL)
 
Do while not rs.eof
Do while not rs2.eof loop
loop rs.close rs2.close set rs = nothing set rs2 = nothing
Hope this helps,
Evan
evanscamman is offline   Reply With Quote
Old 04-22-2009, 06:31 AM   #3
Magster
Newly Registered User
 
Join Date: Jul 2008
Location: Arizona
Posts: 115
Thanks: 11
Thanked 1 Time in 1 Post
Magster is on a distinguished road
Re: Run Time Error - 3251 with Linked database

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

Magster is offline   Reply With Quote
Old 04-22-2009, 12:35 PM   #4
evanscamman
Newly Registered User
 
Join Date: Feb 2007
Posts: 274
Thanks: 0
Thanked 1 Time in 1 Post
evanscamman is on a distinguished road
Re: Run Time Error - 3251 with Linked database

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.

Code:
 
strSQL = "Select FROM tblItem .....  WHERE ItemID = " & lngItemID & ";"
rs = CurrentDB.OpenRecordset(strSQL
If RS.EOF then msgbox "No Records Found"
If it finds a record that matches your criteria, RS.EOF will be false.

You could also use DCount

Code:
 
dim intRecords as integer
intRecords = dCount("[ItemID]","tblItem","[ItemID = " & lngItemID)
if intRecords = 0 then ......  Append Record....
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

evanscamman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Workgroup Information File JTQ911 General 1 06-17-2008 01:46 PM
Access database response times Aenathras Theory and practice of database design 6 04-18-2008 02:10 AM
What's linked to my database xltmike Tables 3 04-25-2007 03:31 AM
Linked Pictures In Database INFOS General 1 09-07-2005 06:27 AM
Linked table security problem... Mitch_____W Queries 2 06-05-2002 05:47 PM




All times are GMT -8. The time now is 03:51 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World