I am using Access 97 with a MB Foster ODBC connection to Amisys.  Each
one
of our computers is loaded with ODBC and because it is on the hard
drive,
each load has a unique password identifying the computer owner.  If I
create an Access database utilizing the ODBC it registers that I am whom
I
am by checking my log on and password.  When I send the database out for
the users, when they attempt to run queries or print reports, they
receive
an error stating that the ODBC connection failed and prompts the user
with the Log On Screen for MB Foster ODBC connection asking for the user
to log on with user id and password.    We have written some code around
this that refreshes the link to the Amisys tables via ODBC upon opening of
the database, however, this code does not work for the reports.
Function RelinkOne()
    On Error GoTo Errmsg:
    'Relinks one of the Amisys Attached tables
    'which will make Access recognize the current machine's user
    Dim db As Database, tdf As TableDef
    Dim rst As Recordset
    Dim mytype As Integer
    Dim myname As String
    Dim mynewname As String
    Dim foreignname As String
    Dim sql As String
    Dim mymess As String
    'Dim sngStart As Single
    'Dim sngEnd As Single
    'Dim seconds As Single

    'sngStart = Timer

    Set db = CurrentDb
    Set rst = db.OpenRecordset("msysObjects")

    'Loop through System Objects table until finding the first attached
table
    DoCmd.Hourglass True
    rst.MoveFirst
    Do Until rst.EOF
        mytype = rst![Type]
        If mytype = 4 Then 'Relink just one table then end program
            myname = rst![Name]
            mynewname = myname & "a"
            foreignname = rst![foreignname]
            Set tdf = db.TableDefs(myname)
            tdf.Name = mynewname
            Set tdf = db.CreateTableDef(myname)
            tdf.Connect = "ODBC;DSN=AMISYS-LIVE;DATABASE=;;"
            tdf.SourceTableName = foreignname
            db.TableDefs.Append tdf
            sql = "Drop table " & mynewname & ";"
            db.Execute sql
            DoCmd.Hourglass False
            'sngEnd = Timer
            'seconds = sngEnd - sngStart
            'MsgBox "This program took " & seconds & " seconds"
            Exit Function
        End If
        rst.MoveNext
    Loop

    DoCmd.Hourglass False
    Set db = Nothing

Errmsg:
    Set tdf = db.TableDefs(mynewname)
    tdf.Name = myname
    mymess = "Error code " & Err & "." & Chr(10) & Chr(13) & Chr(10) &
Chr(13)
    mymess = mymess & "There has been an error in relinking the attached
Amisys tables.  "
    mymess = mymess & "Please call the IM department for assistance."
    MsgBox mymess, vbOKOnly, "Important!"
    DoCmd.Hourglass False
    Exit Function

End Function

The above works for running queries but again, not for print preview or
printing of reports.  Any ideas?
TIA
Sidney Stone
App Systems Analyst


Sidney Stone
Application Systems Analyst
Information Management Dept
Carilion Health Plans, Inc.
540-857-5340
Fax:  540-857-5342
[log in to unmask]

CONFIDENTIALITY NOTICE:  This e-mail communication and any attachments
hereto contains confidential information which may be privileged and is
intended for the use of the designated recipients named above or their
authorized employees or agents.  If you are not the intended recipient,
you are hereby notified that you have received this communication in error
and that any review, disclosure, dissemination, distribution or copying of
it or its contents is strictly prohibited.  If you have received this
communication in error, please call us at the number above and destroy all
copies of this communication (electronic/written form) and any attachments
hereto.  Thank you.

              The NOSPIN Group is now offering Free PC Tech
                     support at our newest website:
                          http://freepctech.com