• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 850
  • Last Modified:

Doesn't Work on Linked Tables

The following code works if the tables are in the same database.  I get a "3219-Invalid Option" when the tables are linked to another database.  

What to do?


Public Function UpdateOrAddEmployeeRecords()
On Error GoTo Err_UpdateOrAddEmployeeRecords_Click

Dim DB As DAO.Database, CO As DAO.Recordset, RO As DAO.Recordset
Dim RETVAL As Variant, SEQ As Long, COUNTRECS As Long, MSGTXT As String

Set DB = CurrentDb()
Set CO = DB.OpenRecordset("tblTraining_P", dbOpenTable)
Set RO = DB.OpenRecordset("tblEmployees", dbOpenTable)
RO.Index = "PrimaryKey"       ' specify the key field in your target table

' next 5 lines get the source table recordcount and set up a progress bar so you can follow how your process is going
CO.MoveLast
CO.MoveFirst
COUNTRECS = CO.RecordCount
iNumberOfPRecords = COUNTRECS
MSGTXT = "Adding Employee Records........."
RETVAL = SysCmd(acSysCmdInitMeter, MSGTXT, COUNTRECS)

' now loop through source recordset and use SEEK to see if the target exists
' if it does (Not RO.NoMatch) then edit the existing record
' if it does not, insert a target record

Do Until CO.EOF
RO.Seek "=", CO![fldSSN]     ' search for exact match on Key Field

If Not RO.NoMatch Then
    RO.Edit
    RO![fldLastName] = CO![fldLastName]
    RO![fldFirstName] = CO![fldFirstName]
    RO![fldMiddleInitial] = CO![fldMiddleInitial]
    If CO![fldSupervisorStatus] = "No" Then
        RO![fldSupervisorStatus] = 0
    Else
        RO![fldSupervisorStatus] = -1
    End If
    'RO![fldSupervisorStatus] = CO![fldSupervisorStatus]
    RO![fldSubOrganizationIndex] = CO![fldSubOrganizationIndex]
    RO![fldStatus] = CO![fldStatus]
    RO.Update
Else
    RO.AddNew
    RO![fldSSN] = CO![fldSSN]
    RO![fldLastName] = CO![fldLastName]
    RO![fldFirstName] = CO![fldFirstName]
    RO![fldMiddleInitial] = CO![fldMiddleInitial]
    If CO![fldSupervisorStatus] = "No" Then
        RO![fldSupervisorStatus] = 0
    Else
        RO![fldSupervisorStatus] = -1
    End If
    'RO![fldSupervisorStatus] = CO![fldSupervisorStatus]
    RO![fldSubOrganizationIndex] = CO![fldSubOrganizationIndex]
    RO![fldStatus] = CO![fldStatus]
    RO.Update
End If

' update the progress bar and get next record from source
SEQ = SEQ + 1
RETVAL = SysCmd(acSysCmdUpdateMeter, SEQ)
CO.MoveNext
Loop

Exit_UpdateOrAddEmployeeRecords_Click:
    ' now tidy up and release variables
    CO.Close
    RO.Close
    DB.Close
    Set CO = Nothing
    Set RO = Nothing
    Set DB = Nothing
    RETVAL = SysCmd(acSysCmdRemoveMeter)
    Exit Function

Err_UpdateOrAddEmployeeRecords_Click:
    If Err.Number = 3021 Then
        MsgBox "There are no P records to process."
    Else
        MsgBox "Update Or Add Employee Records module error " & Err.Number & ", " & Err.Description
    End If
    iMsg = 1
    Resume Exit_UpdateOrAddEmployeeRecords_Click

End Function
0
goldstar
Asked:
goldstar
1 Solution
 
NevaarCommented:
Remove the ON ERROR GOTO statement and re-execute to narrow down to the error line.

I don't think that you can use .index on a linked table, but I could be wrong.
0
 
goldstarAuthor Commented:
I have DEBUG on.
It stops on
Set CO = DB.OpenRecordset("tblTraining_P", dbOpenTable)
If I remove ,dbOpenTable it will open the table, but then it stops on
RO.Index
The error on RO.Index is 3215, Operation is not supported for this type of object.

0
 
DSSCommented:
1. You need to use dbOpenDynaset instead of dbOpenTable.
2. You can't Seek on a linked table.  Use the Findfirst method.

0
 
AlMounkez ConsultantDeveloperCommented:
your problem is that you can't use the index in the attached tables
all you have to do is

Public Function OpenForSeek(TableName As String) As Recordset
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase(Mid(CurrentDb().TableDefs(TableName).Connect, _
                    11), False, False, "").OpenRecordset(TableName, dbOpenTable)
End Function
'************ Code End ***************
'Just use:
'    Dim rst As Recordset
'    Set rst = OpenFormSeek("TableName")
0
 
goldstarAuthor Commented:
Thanks!

Changing from dbOpenTable to dbOpenDynaset and using FindFirst vs. Seek worked.

note: this was the easiest to incorporate into my code vs. creating & calling a function (but thanks, innosys).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now