Set SharePoint List Template for Access Table

So I see that you guys closed the posting from the other blog entry and figured my problem using access 2007 is not worth responding to. So lets just close the blog. Well since we can't start a blog entry and it has to be started by a microsoft staff member, I guess will just find one to post to. Maybe someday you will respond. A) Code signing Cert required to get rid of nasty "microsoft feels that your customers shouldn't trust you and thus microsoft is gonna bug your customers constantly with untrust messages thus hurting your business and what was never a trust issue to begin with has now become one as a result" window that pops up B) getting a code signing signing cert doesn't resolve the problem. Further research shows that Microsoft has created a new thing called "Trusted locations" which is a directory that must be set in the trusted locations to say "oh my gosh, your code can only execute here, and no place else and if you try to execute it anyplace else, then you must be a bad piece of software cause your gonna destroy the hard drive" and microsoft feels its their duty to let everyone know that developers of applications are bad. So I decide to conform to microsofts new access 2007 model of trusted publishers by getting a code signing cert from comodo which is a microsoft recommended cert provider (which is a totall useless piece of money spent as it doesn't do anything to tell anyone who published the software). That seems to maybe have resolved half of the problem. The second half, trust locations is an interesting one. The user has to go into trusted locations and add the installation directory to the locations. But guess what? Microsoft in their infinite wisdom for Access 2007 runtime LOCKS the user out of adding a trusted location. So, were at a catch 22. Microsoft says "your a bad piece of software unless your trusted in a trusted location and as a trusted publisher" but Microsoft ALSO says "neener neener, were not gonna let you create a trust location using runtime, thus all software solutions using runtime are automatically evil" THUS ALL SOFTWARE INSTALLS ARE UNTRUSTED PERIOD WHEN USING RUNTIME Ok, so now it sure would be nice Zac if during your evening hours when your on here, you actually take some time to maybe resolve an issue. Instead of just overlooking it. I am frustrated beyond belief, and have wasted hours of my time due to this absolutly rediculous way you guys are doing things. SINCE WHEN IS IT MICROSOFTS JOB TO POLICE PROGRAM CODE???????? IF THE USER IS WORRIED THEN CAN GET A VIRUS SCANNER WHICH WILL CATCH VIRUSES. I TAKE IT THAT MICROSOFT HAS MADE A BUSINESS DECISION TO HINDER ITS ACCESS USERS.

David, I see that you are very concerned that I wasn't actively monitoring every post on the blog today to catch the 5 posts you submitted. I don't have any automatic notification of when someone posts, and this is the first I've learned of how you spent your day. I'm very sorry to hear that you had such a difficult time deploying your solution today. I can assure you that we do not stop people from commenting in order to deter their feedback. The reason you can no longer post to the thread you initially started on is because the Access blog is set to automatically disable new comments on any post after 7 days. You just happened to be submitting your comments on a post that was very near to that limit. Most of the people who spend their time here on this blog do it because we want to help support the Access community. Contributors create and submit posts and answer questions in their free time. It is not easy or simple to maintain the blog and keep this line of communication open we do it because we want to get feedback from customers and share information we think benefits the Access community as a whole. I'm sorry that you don't feel our efforts here are worthwile. I'm also sorry you've had a difficult time deploying the new Access runtime because of behaviors in the new Office and Windows security models. I can assure you that all changes that have been made were done because Microsoft believes they benefit customers by making the products more secure. I also know that we thought very hard about how customers would be able to deploy their solutions in our new model, and that while it may not be intuitively obvious to you how the design of your solution needs to be altered in order to work in the new model, it is absolutely possible for you to get your solution running. Here are some comments on what i think you're asking: A) I think you're talking about the cert trust dialog. This only appears on databases which have digital signatures. Digital signatures work really well if you install trust for the signature before you try to use code that has been signed by it. If you do this the other way around, the user is presented with this text: "A potential security concern has been identified. Note: The digital signature is valid, but the signature is from a publisher whom you have not yet chosen to trust." Maybe it isn't clear exactly what this feature is supposed to do. Digital signatures are not a mechanism to get past the trust dialogs without prompts. Instead, they are purely a way for you to share a database file with a customer, and for both of you to be sure that the file they received and tried to open is exactly the file you sent. In 2007's new .accdc format, if any part of your application was changed (by a malicious hacker, for example), the cert will be broken. Singned accdc files work well within a large corporation where all users have Access on their machines, and IT is rolling out trust for certificates automatically through Active Directory Group Policy. I don't think I would be using this solution for runtime based deployments though. By their nature, .accdc files require an extra step of removing the database file from the signed package. This doesn't happen at install time, but when the user first runs the .accdc. For runtime apps, I would deploy my .accde directly, to avoid that step. B) The best way to get your runtime app to work without prompting the user about security is to use the package wizard in the ADE, or some other setup technology to mark the folder in which you are installing the .accde as a trusted location. To do this, create a key in:

HKCU\Software\Microsoft\Office\12.0\Access\Security\TrustedLocations

Key name: Whatever you want, an ID, a GUID, or a string, only restriction is that it must be unique

Value: Path (string) - path to the folder where the .accde file is

Value: AllowSubFolders (DWORD) - This is optional, set to 1 if you want to trust subfolders of this location as well If you're installing for the whole machine, you'll need to set this key for each user currently on the machine. It might be easier to do this as an extra step in VBA code when the app is first run as an initial configuration step. Also, if you need to deploy trust more broadly, the OCT is probably the best way to do that:

technet2.microsoft.com/.../8faae8a0-a12c-4f7b-839c-24a66a531bb51033.mspx I hope this information is helpful. We don't usually use the blog for taking the time to answer such detailed questions that are so far removed from the topic. If there isn't enough information here, your best bet is to follow up with the MVPs in our newsgroups. I know you've already put a ton of time into this solution, and I absolutely appreciate your devotion to the product. I'm not trying to shuffle you off, I just know that they have the ability to respond to your questions much more quickly than I do. As I'm sure you are aware, they are extremely knowledgeable about Access, and are more than happy to answer respectfully worded queries. As a final word, although I appreciate your passion, and I know that you had an unpleasant surprise today, I don't think the tone of the posts you made was appropriate. In the future, I will appreciate it if you show the Access bloggers, the Access team, and the Access community at large the respect we all deserve. Pointed questions, constructive criticism, and statements of fact which will be useful to the Access community are welcome on the blog. Emotional insults written in capital letters disparaging Access as a product are not.

Having access to communication channels would help and my frustration level is due to the fact that I have a million dollar project that I cannot even deploy because of the security issues, and not getting with any method I have tried. I have tried respectfully wording this a few weeks ago concerning Code Signing, and was shuffled off. I have tried search all access documentation, msdn documentation, even all resources I can find on the internet. Nothing to discuss what you just told me. I have tried Utter access. Again, Nothing, cause no one seems to have an answer. So using the Access Blog since its supposed to be the developers themselves was a final attempt to gain results. Had I gotton these results 3-4 weeks ago when the problem was initially posed, it would not have been an issue. I was told back then, by bloggers or possibly clint that a Code Cert would be required for macros and that I could also remove the macros and go directly to VB and solve the issue. So I did, I moved all macros which did nothing bu call functions for the menu system to vb. And so now I have no macros and still getting macro security errors. Instead of fighting a loosing battle. I follow the other suggestion givie in access blogs to get a code signing cert, and all excited this is going to solve things. Only to find that it solve nothing. Cause the true problem is trusted locations. this is the first time I have seen any documentation about adding a trusted location programatcially or via installshield to created the trusted location. Unfortunatly, after this is deployed, I am going to be rewriting things in RealBasic with MySQL. I cannot put faith in an organization that is going to continue to sandbox and destroy legacy applications that have been running for 10 years without any issues and just by upgrading all hell breaks loose in the application. My faith level in the access development or whomever has decided to do this code signing and trusted locations has severly been damaged. I realize your intent is to keep malicious code from running. It has created a serious problem. My users download the software from our web site and can't even figure out where they saved the file to. This new model is going to require my users to take a accde file and save it in the appropriate directory or the application is broken. Now you tell me, how a user who can't figure out where they saved the setup.exe file at on their hard is giong to figure out where they are supposed to save the accde file at correctly If you wanted to give a code signing, you could have just required the accde to be signed itself. Personally I don't see how code signing is going to protect users to begin with. We have worked in the computer industry since what lets say the 80's and we didn't seem to have a need for it before, and I don't see how signing code is needed today. I can buy software from any company, corel, adobe, etc. even microsoft and never is the software required to be signed just to run. yet VBA code seems to have to be signed. Whats the reasoning behind it. You say its cause the code can be changed. Well, so can any other applications code. I could write a program right now to change kernel32, signing it doesn't secure it. So whereas I will apologize for my frustration and critcism, I was at my wits end and 3-4 weeks ago I could have been given accurate information. Even now having trusted locations and being able to simply modify the registry breaks the trusted locations. Its not secure, so there is no point in even having it.

and here is a suggested fix that I think will keep your digital signatures and also solve issues of users putting accde files in the wrong folder. you state the following:

=============================

Maybe it isn't clear exactly what this feature is supposed to do. Digital signatures are not a mechanism to get past the trust dialogs without prompts. Instead, they are purely a way for you to share a database file with a customer, and for both of you to be sure that the file they received and tried to open is exactly the file you sent. In 2007's new .accdc format, if any part of your application was changed (by a malicious hacker, for example), the cert will be broken.

========================= Can you not make it part of the accdc packaging to allow the developer to dictate that once the user has said "yes, lets trust this software" to extract it to the folder dictated by the developer when the accdc file was created The issue is not that the user has to trust the software, the issues is the user having the ability to extract it to a location anywhere on their hard, and putting it in the wrong location. It has to reside where the shortcuts are looking for it. In my example, the shortcut is as follows: "C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\BizWizard Professional\biz-wizard-programs.accde" /runtime /nostartup Now if the user extract the biz-wizard-programs.accde into any location other than "C:\Bizwizard Progressional\ then the application is broken. they will then double click on the icon on their desktop and get a message that says "ut oh, can't find it" and this is going to happen RIGHT AFTER they just got a trust message Thats going to really give them alot of confidence in our companies ability to develop.

For runtime apps, I would deploy my .accde directly, to avoid that step. ============================== Whem I deploy the accde file directly without it being in an accdc file, it comes up with the trust message which is only resolved by doing the accdc that is signed. Insight that I am missing here?

So I think the base assumption, and the reason that automatically running VBA without the user first taking an explicit trust decision is problematic, is that Access databases are single-file database solutions that are easy for users to pass around. What we're trying to do in the security model is to never run code that hasn't first been trusted by the user. That said, once the code is trusted once, you as the developer have the ability to make that into a permanent trust decision through your own code. Your .accdc suggestion around choosing a deployment folder is an interesting one. I'll pass it on to our security guy to see what he thinks. We're always looking for ways to help developers to simplify their deployments. The way I would think about a runtime solution like yours is as if the front end were any other executable package. I would use the package wizard to set a default install location, and create an msi that does the install. That way whenever your users need an updated version, they just download and install the msi, and it will update the file. Alternatively, you can use one of the Automatic front-end updater tools, like Tony's AutoFE Updater:www.granite.ab.ca/.../autofe.htm

Or FMS's Total Access Startup: www.fmsinc.com/.../index.asp

Note that because of changes in the security model in Windows Vista, your current install location probably won't work well for your back-end data. If you're linking to ODBC, this probably won't affect you much, but if you're linking to an .mdb or .accdb on the back end, that file needs to be in a folder that all users have read/write permission to (not just Admins). I suspect you'll find that a MySQL & RealBasic solution requires at least the same level of deployment management as an .accde file. If that isn't the case, I'd be interested in hearing how their model works. Generally speaking, software needs to be trusted on Windows Vista. Mostly, this just means that you took the initial step to install that software. If you did that, then you trust it implicitly. That is why it is okay to install a tusted location as a part of your install. You're already running the installer service as an elevated process through LUA, which means you've granted the software permission to run on your computer. The same is true of Office, Windows, etc... you've explicitly installed it, which is an implied trust decision. I hope this helps!

Our model does not use ODBC, we use program code we wrote to do the linking to the backend database which typically resides on a server location via a share. The code was original written with DAO and we do not want to change it casue there is too much legacy code to change We have 4 frontends to the backend for each install. Each frontend needs an shortcut. In addition, there are 5-6 ActiveX controls that must be insalled. There are countless numbers of documentation and sample files. total there are easily 20+ shortcuts that have to be created. The ADE allows you to package a single shortcut only. Yes you can include extra files, but you can't assign shortcuts to them. You cannot register DLL's and OCX's that your application depends. The package and deployment solution provided with access 2007 is extremly lacking. It has been lacking for as long as we have been developing. Which is why we use InstallShield. InstallShield gives us control over the installation. Previous to this release, we always installed in C:\Program Files\BizWizard. But this version release we moved it to C:\BizWizard Professional\ because the program files directory on vista is READ ONLY and since an accde file needs read/write access, you can no longer install into what has been traditionally the program installation folder I don't view access as a single file, pass it around, ad-hoc solution. Maybe many corporation clients use it as such. I would think that type of solution should be more of a Excel with VBA behind. I view access as a RAD tool on a very small scale which allows us to create quick solutions without having to go through the development steps required in compiled applications such as VB, C++, and yes, even RealBasic or Delphi. So for our solution we have a very elaborate business applicaiton system we have developed and continue to add funtionality too that started over 10 years ago. The Frontend is our program modules, it only houses WORK tables - the backend is another access file that is DB only and is linked via linked tables through a share. Granted under current technology avalable its not the best model, but under the technology available when development on it started, it was the best model and its legacy and it needs to continue to operate I think alot of the problem is the lack of documentation on what has been changed. So Im making model change decisions based upon trial and error to make the application work in a world whereby microsoft has broken the models of the past and causing legacy code problems. Previously, deployment was simply packaging using Installshield, install into the directory, register appropriate dll's and ocx' and boom, Solution works. Now Im jumping to hundreds of hoops due to vista security model changes and access 2007 model changes. your telling me having the system into int the root folder isn't good, yet, I have to make it simple for a cusotmer to find the correct location to extract teh accdc file Installing into the long directory tree for user folders is rediculous complex and I know my user base. They can't do it. They will mess it up. So our solution is not a "One file, pass it around" solution - the user does not even have to need where it is. In our mind, its just a program that processes the data on the backup. Thats all the user sees it as. They click a shortcut and boom, they are doing their work. You mentioned earlier: For runtime apps, I would deploy my .accde directly, to avoid that step - and I posed a question How can I do that because if I don't deploy it as accdc, they user gets trust message even though there is a signing cert, cause the signing cert only applies to accdc files and never to the accde

Your point about documentation is well heard. We need a good complete whitepaper that covers all you and I have discussed in this thread. I'll start the wheels rolling on our side to get something in place. On install shield: It sounds like you are doing the right thing here. The ADE package wizard was never designed to handle situations as complex as yours. Install Shield and/or Visual Studio's setup and deployment features are absolutely the right way to handle your case. I think it is awesome what you're doing with Access, but you should know that 99.9%( or more) of our user base is not using the tool for solutions which are this complex. If you came up to me at TechEd and asked what platform you should use for building your solution today from scratch, I would point you at the Visual Studio tool set as the best fit for your needs. On security and trust: for an .accde, that is copied by your installer logic into the folder that is a trusted location your installer added to the registry, you should get no trust prompts. This should just work, and if it doesn't, please let me know (there may be an Access bug that only happens in your specific config). Ideally your users should never have to update their .accde manually. You should be able to do that in the code of the .accde itself using one of the front-end'ing tools I noted in my last comment, or you could distrubte an updated .msi package which patches your files up to the new version. Either way, I agree with you that users won't have an easy time getting the .accde into the right folder, and I think you want to do some work to avoid that. Again, for your specific situation I would steer clear of cert signing and .accdc files. I don't think they will meet your needs well from a scenario perspective. A better solution for you is to have your installer logic ensure that the .accde file is always installed into a trusted folder.

I had difficulty executing the code sample: Compile Error, Argument not optional.

Seth! A comment that is actually about this post?! J/K Are you invoking the sample code using Access 2007? It works correctly in my immediate window. Note that you need to have a table with the correct name ("Table1" in the sample) in your db.

Ok, I got it to work finally. Although the code did not run in my immediate window without creating a function. I also saw on Utter Access (www.utteraccess.com/.../showflat.php) that there is a way to get the acCmdSaveAsOutlookContact to work with this methodology. However it looks like some of the WSSFieldIDs for mapping to Outlook are a bit goofy. Is there a complete listing you could post of valid Outlook WSSFieldIDs to correctly map all of the fields in a contact table to Outlook fields? Thanks,

Seth

Zac, thanks for the help. All is working without using the accdc. I think here is the problem. the message that pops up when you don't have a trusted location set indicates the following: ==================================

A Potential security concern has been identified Warning: It is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionatlity and you trust its source. File Path: c:\BizWizard Professional\biz-wizard-programs.accde The file might contain unsafe content that could harm your computer. Do you want to open this file or cancel the operation? ================================== Because the message indicated "trustworthy source" it led me to believe this was a code signing issue, not a trusted location issue. This is the stem of my frustration, which is finally resolved. I am able to get a successful install when I set the trusted location in the registry and NO code signing. the message text should be modified to accuratly reflect that the issue is a trusted location issue, not a trusted source issue. Source to me indicates "where you got it from" not "where it is residing" So my hours upon hours of dealing with this as a code signing issue was not the problem to begin with. Suggestion: correct the text of the message to indicate its a trusted location problem, not a code signing problem. in reality, I don't need a code signing cert AT ALL and have wasted $500 with comodo due to the text of this message. All in all. Application is running, as it used to, and problems are resolved. Thank you Zac for getting me the correct information I needed

David: Glad to help! I'll forward your feedback to the security team who owns those messages.

Seth: Let me see what I can dig up. Maybe I'll do another post on that...

Zac Don't you think that it would be a good idea to include sharepoint stuff fo rAccess in the help files? I searched for WSSTemplateID in the VBA help files and got nothing. One of the reasons our IT department is close to abandoning SharePoint is the lack of information where it should be.

Hey Bill, we do have pretty extensive documentation for SharePoint itself. In this particular case, we're talking about a particular behavior of Access. To be clear, the particular technology described in this post was not designed as a feature for developers to take advantage of. Instead, the supported way to achieve SharePoint and Outlook integration is to create one of our built-in contacts tables. Bill, if you send me mail on what it is specifically that your IT group is having a hard time finding, then I'll either hunt down the links for you, or get a request into the system that new content be created.