Results 1 to 2 of 2
2010-09-15, 10:56 #1
- Join Date
- Jul 2002
- Thanked 0 Times in 0 Posts
I work in MS Access 7.0 and our MS Dynamics CRM is 4.0. I have 18 years experience using MS Access.
After we migrated to MS Dynamics CRM, I still needed to be able to view the data for the program I manage. I have no need to update CRM fields from Access. I just needed to view my data and create reports and queries on the data that work better for me than CRM. Besides, even after explaining what I want to our IT folks, it wouldn't be cost effective for them to spend that much time on just one user.
My actual goal, however, was to somehow link directly to the CRM data. Here is how I did it.
1. I built Advanced Find Queries in our CRM that corresponded to the Access table fields I needed.
2. In CRM, I exported the Advanced Find queries to a dynamic Excel sheet.
3. In Excel, I copied the dynamic sheet's data to a tab in a workbook I call "CRM_Workbook_BE".
4. My workbook has 8 worksheets in it. All sheets are dynamic, so when CRM has new data the workbook is updated automatically.
5. In my Access program, I linked to all of the worksheets and use them as tables.
6. The Excel table has to be open in order for Access to link to it.
7. The startup routine for my Access table displays a pop-up box where I click on a "hyperlink" that opens the Excel File. After I open the Excel file, I minimize it and the startup screen opens behind my pop-up.
8. I then close the pop-up and I'm good to go.
Now my Access database is just a component of my process for managing my program. I keep it open all day, confident that the data I am viewing in it is up-to-date. When I have new data to add or change, it is done in CRM. As soon as I save it on the CRM side, it shows up in my Access database. That's all I wanted in the first place.
If you want to do something like this, here is my advise:
1. Know your CRM data thoroughly.
2. Create Advanced Finds (queries) on CRM.
3. Run the Advanced find, and export the results to a dynamic Excel worksheet.
4. Save the Excel worksheet, naming it appropriate for your work.
5. If you need to save a number of Advanced Finds, you can store them in a workbook. However, “moving” the worksheet to the workbook doesn’t work for me. I “select all”, “copy”, switch over to the target workbook, click on an empty worksheet tab, then “paste”.
6. Once you have all the Advanced Find’s exported to dynamic Excel worksheets, you can link to them from Access.
7. The Excel Worksheets have to be open for Access to link to and see the data. If it’s not open, Access doesn’t understand what you are trying to do.
2010-09-15, 16:40 #2
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 60 Times in 60 Posts
Thanks for posting your tips - they are bound to be useful to someone else, and we don't have a separate accounting forum - by design as there are so many packages out there and most of us have limited experience with such applications.
Just out of curiosity, did you try creating ODBC data sources to the SQL Server Dynamics back-end? That would be a oossible alternative, but Microsoft may have it pretty well locked down - accounting folks are very nervous about anyone else touching their data, even just to read it.Wendell