suppress macro warning msgs when tranferring remote BE tables

[tuxalot]

When changing paths to a BE db located on a network via DoCmd.TransferD atabase is there a way to suppress the macro warning messages that appear? I have 14 tables, and 14 warning messages appear in succession one for EACH transfer.

Thanks as always :)

Tux

[DonRayner]

You can disable / enable warning with the following. Just make sure that you re-enable warnings in your code after you finish with the transferdatabas e.

Docmd.SetWarnings false
Docmd.SetWarnings True

[NeoPa]

This is relatively safe as it only disables warning messages and not the more serious error messages. Still important to return to default state afterwards of course, but no need to worry that you may miss bugs in your code with this set.

[tuxalot]

Thanks for the quick replies.

[tuxalot]

Not working folks. Still getting the macro warnings (18 in total) when I am linking to my BE. Here's my code:

    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            'turn off macro warnings
            DoCmd.SetWarnings False
            'transfer tables
            DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(strDbPath), acTable, tdf.Name, tdf.Name
            SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
        End If
    Next tdf

Then, before exiting I am turning the warnings back on.

Any ideas??? Warning bmp is attached.

[Stewart Ross]

Ahh, the message you show is nothing at all to do with Access's internal user warnings. The user warnings are the ones which say things like 'you are about to update 100 rows in table xxxx - are you sure?'.

The security warning you are receiving is one from the OS itself, not from Access. I don't know how to disable these - but they have nothing to do with the SetWarnings settings in Access at all.

-Stewart

[DonRayner]

I believe all you have to do is to add the server to your trusted zone in Internet Explorer.

[ChipR]

I had instructions for my users to add the file locations to Access trusted locations, so here they are:

· Open Access 2007 and select Access Options from the Office Button menu.
· In the Access Options dialog, select Trust Center on the left and click Trust Center Settings…
· In the Trust Center dialog, select Trusted Locations on the left and check Allow Trusted Locations on my network.
· Click Add new location… and type or browse to the location of the application.
· Check Subfolders of this location are also trusted.
· Click OK.

[tuxalot]

Thanks all,

Sure, I could provide instructions to my users on how to add the path as a trusted location per Chip's instructions if the BE were known and static. Problem is I've no idea where the end user will want to store the BE so I've given them the option to move it. So consider this scenario: A user wants to store their BE on a network share. While the db is closed, they move their BE. During start up, code in my start-up form checks if the BE path is valid and if not, presents a form to allow the user to change the BE path. This bit is now working except the user must accept a host (18 in total) warning messages (one for each linked table) if the BE is not in a trusted path.

Keep in mind this is an A07 runtime deployment so their is no way for a user to get into the trust center to change paths.

I am learning that the following solution may be possible. Take the new BE path selected by the user and convert it to UNC. Then save the UNC path as a trusted location by adding it to the registry. Then link the BE using DoCmd.TransferD atabase and the warnings should not appear. Does this sound like a logical approach?

Possible issue here:

https://WHAMMY

Looks like if the EU selects an updated BE path on their server, it may not transfer at all using Transferdatabas e. I cannot test this as I'm developing in a non-server environment.

[ChipR]

Is it better to use TransferDatabas e rather than just change the link location? Right now I'm using:

Function ReLink() As Boolean
...
    For Each tdf In db.TableDefs
        If Len(tdf.Connect) > 0 Then
            tdf.Connect = ";DATABASE=" & strNewPath
            Err = 0
            On Error Resume Next
            tdf.RefreshLink ' Relink the table.
            If Err <> 0 Then
                ReLink = False
                Exit Function
            End If
        End If
    Next tdf
...

[tuxalot]

Not sure what would be better. this is my code:

Private Sub cmdUpdatePath_Click()

    'much of the below code was found at http://www.dbforums.com/microsoft-access/1005409-how-change-link-path-vba.html
    'if update path button is clicked when the path textbox is empty then exit sub
    If Len(txtChangePathBackEnd & "") = 0 Then GoTo Err_cmdUpdatePath
    
    On Error GoTo ErrTrap
    Dim dbs    As Database
    
    Dim tdf    As TableDef
    Dim strDbPath As String
    Dim stLinkCriteria As String

    'delete all linked tables if they exist, but not linked tblReportsState!
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 15) <> "tblReportsState" And _
           (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
            CurrentDb.TableDefs.Delete tdf.Name
        End If
    Next tdf
    Set tdf = Nothing

    'update linked location based on user input
    strDbPath = txtChangePathBackEnd
    Set dbs = OpenDatabase(strDbPath)

    For Each tdf In dbs.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(strDbPath), acTable, tdf.Name, tdf.Name
            SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
        End If
    Next tdf

    SysCmd acSysCmdClearStatus

    Set dbs = Nothing
    Set tdf = Nothing

    'success. send message to EU
    MsgBox "Path to back end Database has been updated.", vbOKOnly, "Update Successful"
    
    DoCmd.Close acForm, "frmChangePathToBackEnd", acSaveYes
    DoCmd.OpenForm "frmMain"
...