Greetings All! David Smith (whom you will recognize as a contributor of good info once in a while) tried to send the following eMail to ProFox, but for some reason it would not post. So he asked me to post it on his behalf. It is so slick I (Gil Hale) would have liked to have taken credit for his work and finding, but that would be a wrong thing to do to a friend, yes? Here goes:
========================================================== Hi Gil I posted this on the VFP mailing list but haven't seen it come through yet . it's a summary of the techniques I've been been working on with .dbc's and MS Office connections. I've got them down pretty well and have deployed a few sheets out there already with some nice feedback from them. You know, I think this sort of thing may have some commercial legs, and I am developing a way to make custom Excel and Outlook tabs so the user has only to point and click their way to DCMS data bliss. I actually have it sort of working, but still a ways to go until I can get the assembly of them down to the "stupid-easy" level that I want. I can get the tabs to appear, but haven't quite nailed the linking of them to VFP reports. Imagine. DCMS custom reports on demand with but a single click inside the comfort of Excel, or the same reports integrated into the communications powerhouse that is Outlook, and from there to everywhere Outlook goes. wow. Could be fun. So anyhow, here's the email. _______________________________________ Howdy Fox-folk! Below is a series of techniques I've ( sort-of ) perfected that allow local users at my company to benefit from up to date data we now have available within a VFP .dbc file.( It leverages the data extraction portion of Gil Hale's VFP-based DCMS system by tapping into the core .dbc file Gil created for us that houses the combined extraction data from across our company stores.) The thought is not everyone needs/can have access to/ has the skills or interest in learning the DCMS front end, so instead I aimed for MS Office Excel, which is comfortable and non-threatening to non-PC folk. The final product kicks out VFP .dbc data that updates automatically when opened and just works great. It's probably old news, but I don't remember reading anything like this on these lists before, so hopefully this will come in handy some time. ______________________________________ How to create a Excel-friendly database connection using the DCMS CDBM translationengine.dbc. Assumptions: 1.Excel 2007 2.VFP OLEDB provider drivers on client PC. 3.Paste-able SQL query that uses data from the translation engine.dbc or good enough SQL skills to be able to write the statemnt from scratch. I cut and paste mine from Visual Studio connection templates I created, but whatever works for you is fine. ______________________________________ One-time setup of dataconnection to the VFP database. ______________________________________ 1. Open Excel, click on Data tab, then From Other Sources, then Data Connection Wizard 2. Click other/advanced, then select 'MS OLEDB Provider for Visual Fox Pro' and click the next button. In next window choose the VFP databases option, then click browse. Navigate to the translationengine.dbc and select it. Click the 'ok' button and then click the 'test connection' button to test it using the machine collating sequence option. 3. click the 'okay' button, and uncheck the 'select a specific table' option in the new window. Click Next, then type in a new name for the connection file, and for the freindly name as well. click Finish, then Cancel. 4. Still on the Excel data tab, click connections, then Add.choose the file you just created from above. when the table list appears, select any table you wish, then click Properties, then click definitions in the new window. delete all contents of the command text window and paste in your SQL query code or type it in manually if you're skilled in SQL. Once done, click Export Connection File and save it somewhere handy. I saved mine to a shared folder on the DCMS server so I could use it from different client PC's without having to rewrite it every time. click OK, click close. whew! All done! _______________________________________ data connection use _______________________________________ 1. From a new workbook, click the Data tab, then connections, click Add, then click the browse button. Navigate to the .odc file you just saved and select it. In the window that opens up, draw out a size appropriate to the ooutput of your query, or just select the whole sheet and then click okay. the query processess, then taa daa! your data appears :) Now, save the workbook somewhere on the local client PC. If you have multiple users of the same query, just copy the workbook to whoever needs it, just be sure they have the same drive mappings.Once created, you can use the sheets within Office 2003 as well with only some minor cosmetic loss. I hope this was helpful :) David Smith Systems Administrator Doan Family of Dealerships (585) 352-6600 ext.1730 [EMAIL PROTECTED] ========================================================== _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
