Disabling security warning on tables imported/linked via VBA

Question

How do you stop this window popping up for tables being imported via DoCmd.TransferSpreadsheet?

I'm importing quite a few tables, so having to click "open" on this pop-up for each one is annoying.

I've tried DoCmd.SetWarnings False but that doesn't seem to work.

I know that it may also be possible to set the folders that contain the files being imported to trusted locations, but unfortunately I don't have permissions on our network to do that at the moment (I also think that would only affect the settings of my version of Access rather than the Access file I'm creating and distributing).

Answer 1

The issue I was having was due to our network security policy. Our IT tech has amended the policy so that I don't get these pop-ups from files I try to import in to Access from a particular folder.

He has given the following instructions should anyone else have the same issue in Access on a network, though I suspect this could be done from other Office programs too:

For a Microsoft Active Directory domain network, add trusted locations using Group Policy. Creating, linking and security filtering for group policy objects should be familiar before continuing, also the admx files for the version of MS Office in use should be loaded in the central store.

Settings are under: User configuration/Policies/Administrative Templates/Microsoft Access /Application Settings/Security/Trust Center/Trusted Locations

Policy: Allow trusted locations on the network – set to enabled (must be enabled otherwise any trusted locations on a network share specified below will be ignored)

Trusted location #1

1. Set to enabled
2. Path - can be network path (e.g. \\), if using mapped drive letter the policy is applied using the network path anyway, quotes around the path should not be used even with spaces in the path name
3. Date - enter a date from which the location should be trusted (e.g. todays date)
4. Description - optional – this will show up in Access trust centre locations list Allow subfolders – tick to include any subfolders under the above path if needed

Add other locations using Trusted Location #2, #3 etc.

For standalone Access, add the locations directly into Trust Centre locations dialogue using the Add New Location button. If the locations are in a shared folder on the network, make sure the box: Allow trusted locations on my network (not recommended) is ticked otherwise any network locations will be ignored.

Answer 2

You need to create a Digital Certificate that determines that the source is trusted. I wrote a doc for my company, I'll copy/paste it here but it's going to lose the screenshots I provided.

In order to get around this issue, you will need to create a Digital Certificate.  Digital
 Certificates are good only on the computer they are created on, so if this database will be used
 on multiple computers then each one will have to create a Digital Certificate.

To do this, you will need to perform the following tasks:

1)  Click on Start -> All Programs -> Microsoft Office -> Microsoft Office Tools -> 
    Digital Certificate For VBA Projects (If you don’t have this, you will need to contact the CSC)
2)  Enter a Certificate Name.  Make it generic like “MyCertName” and Click OK
3)  Open your Access database that you want to bypass the security warning
4)  Go into the Design View of any Module
5)  Click on Tools -> Digital Signature
6)  Choose your Digital Certificate you created in Step 2
7)  Save and close the database
8)  Re-Open the database.  You will now be prompted with a different Security Warning:
9)  Check off the “Always trust files from this publisher…” box and click the Open button
10) All subsequent times you enter this database, you will not be prompted with a security warning.

The CSC, in Step 1, is our internal help desk. In our case, it requires specific priviledges that they need to assign in order to be able to do this.