Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Doesn't Work on Linked Tables

Posted on 2002-04-19
5
Medium Priority
?
838 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 800 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

972 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