Solved

Doesn't Work on Linked Tables

Posted on 2002-04-19
5
812 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 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