Solved

Doesn't Work on Linked Tables

Posted on 2002-04-19
5
814 Views
Last Modified: 2012-06-22
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
Comment
Question by:goldstar
5 Comments
 
LVL 4

Expert Comment

by:Nevaar
ID: 6954752
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
 

Author Comment

by:goldstar
ID: 6954760
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
 
LVL 2

Accepted Solution

by:
DSS earned 200 total points
ID: 6954862
1. You need to use dbOpenDynaset instead of dbOpenTable.
2. You can't Seek on a linked table.  Use the Findfirst method.

0
 
LVL 1

Expert Comment

by:innosys
ID: 6955708
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
 

Author Comment

by:goldstar
ID: 6959827
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question