469,943 Members | 2,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,943 developers. It's quick & easy.

suppress macro warning msgs when tranferring remote BE tables

tuxalot
200 100+
When changing paths to a BE db located on a network via DoCmd.TransferDatabase 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
Mar 26 '09 #1
10 4974
DonRayner
489 Expert 256MB
You can disable / enable warning with the following. Just make sure that you re-enable warnings in your code after you finish with the transferdatabase.

Expand|Select|Wrap|Line Numbers
  1. Docmd.SetWarnings false
  2. Docmd.SetWarnings True
Mar 26 '09 #2
NeoPa
32,233 Expert Mod 16PB
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.
Mar 26 '09 #3
tuxalot
200 100+
Thanks for the quick replies.
Mar 26 '09 #4
tuxalot
200 100+
Not working folks. Still getting the macro warnings (18 in total) when I am linking to my BE. Here's my code:
Expand|Select|Wrap|Line Numbers
  1.     For Each tdf In dbs.TableDefs
  2.         If Left(tdf.Name, 4) <> "MSys" Then
  3.             'turn off macro warnings
  4.             DoCmd.SetWarnings False
  5.             'transfer tables
  6.             DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(strDbPath), acTable, tdf.Name, tdf.Name
  7.             SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
  8.         End If
  9.     Next tdf
  10.  
Then, before exiting I am turning the warnings back on.

Any ideas??? Warning bmp is attached.
Attached Images
File Type: bmp warning.bmp (24.3 KB, 388 views)
Mar 27 '09 #5
Stewart Ross
2,545 Expert Mod 2GB
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
Mar 27 '09 #6
DonRayner
489 Expert 256MB
I believe all you have to do is to add the server to your trusted zone in Internet Explorer.
Mar 27 '09 #7
ChipR
1,287 Expert 1GB
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.
Mar 27 '09 #8
tuxalot
200 100+
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.TransferDatabase and the warnings should not appear. Does this sound like a logical approach?

Possible issue here:
WHAMMY

Looks like if the EU selects an updated BE path on their server, it may not transfer at all using Transferdatabase. I cannot test this as I'm developing in a non-server environment.
Mar 27 '09 #9
ChipR
1,287 Expert 1GB
Is it better to use TransferDatabase rather than just change the link location? Right now I'm using:

Expand|Select|Wrap|Line Numbers
  1. Function ReLink() As Boolean
  2. ...
  3.     For Each tdf In db.TableDefs
  4.         If Len(tdf.Connect) > 0 Then
  5.             tdf.Connect = ";DATABASE=" & strNewPath
  6.             Err = 0
  7.             On Error Resume Next
  8.             tdf.RefreshLink ' Relink the table.
  9.             If Err <> 0 Then
  10.                 ReLink = False
  11.                 Exit Function
  12.             End If
  13.         End If
  14.     Next tdf
  15. ...
Mar 27 '09 #10
tuxalot
200 100+
Not sure what would be better. this is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdatePath_Click()
  2.  
  3.     'much of the below code was found at http://www.dbforums.com/microsoft-access/1005409-how-change-link-path-vba.html
  4.     'if update path button is clicked when the path textbox is empty then exit sub
  5.     If Len(txtChangePathBackEnd & "") = 0 Then GoTo Err_cmdUpdatePath
  6.  
  7.     On Error GoTo ErrTrap
  8.     Dim dbs    As Database
  9.  
  10.     Dim tdf    As TableDef
  11.     Dim strDbPath As String
  12.     Dim stLinkCriteria As String
  13.  
  14.     'delete all linked tables if they exist, but not linked tblReportsState!
  15.     For Each tdf In CurrentDb.TableDefs
  16.         If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 15) <> "tblReportsState" And _
  17.            (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
  18.             CurrentDb.TableDefs.Delete tdf.Name
  19.         End If
  20.     Next tdf
  21.     Set tdf = Nothing
  22.  
  23.     'update linked location based on user input
  24.     strDbPath = txtChangePathBackEnd
  25.     Set dbs = OpenDatabase(strDbPath)
  26.  
  27.     For Each tdf In dbs.TableDefs
  28.         If Left(tdf.Name, 4) <> "MSys" Then
  29.             DoCmd.TransferDatabase acLink, "Microsoft Access", Trim(strDbPath), acTable, tdf.Name, tdf.Name
  30.             SysCmd acSysCmdSetStatus, "Processing table [" & tdf.Name & "]..."
  31.         End If
  32.     Next tdf
  33.  
  34.     SysCmd acSysCmdClearStatus
  35.  
  36.     Set dbs = Nothing
  37.     Set tdf = Nothing
  38.  
  39.     'success. send message to EU
  40.     MsgBox "Path to back end Database has been updated.", vbOKOnly, "Update Successful"
  41.  
  42.     DoCmd.Close acForm, "frmChangePathToBackEnd", acSaveYes
  43.     DoCmd.OpenForm "frmMain"
  44. ...
  45.  
Mar 27 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Krishna Srinivasan | last post: by
9 posts views Thread by Doug Ly | last post: by
6 posts views Thread by Kim Hellan | last post: by
4 posts views Thread by J Swift | last post: by
1 post views Thread by Chris Stankevitz | last post: by
12 posts views Thread by Laurent Deniau | last post: by
13 posts views Thread by Rex Mottram | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.