Access CRM database from Access

Is it feasible to access my CRM database from an access frontend with linked 
tables?
I created a new ODBC connection and found a CRM filtered view but that is 
read only.


0
Pat
10/23/2007 3:32:04 PM
crm 35858 articles. 1 followers. Follow

5 Replies
571 Views

Similar Articles

[PageSpeed] 53

I would strongly suggest that you not try to update CRM data through any 
interface other than CRM -- Web or Outlook Client.  You're just asking for 
trouble, because of the interconnectedness of the tables, views, and security.



"Pat Horridge" wrote:

> Is it feasible to access my CRM database from an access frontend with linked 
> tables?
> I created a new ODBC connection and found a CRM filtered view but that is 
> read only.
> 
> 
> 
0
Utf
10/23/2007 3:43:00 PM
On Oct 23, 8:32 am, "Pat Horridge" <p...@remove-spam.vet.co.uk> wrote:
> Is it feasible to access my CRM database from an access frontend with linked
> tables?
> I created a new ODBC connection and found a CRM filtered view but that is
> read only.

You can set up the ODBC connection just as you would to any other SQL
backend.  There are two tables for most objects in CRM.  So the
account table is held in accountbase and accountextensionbase.  The
view you connected to would be called account.

I agree with KKL in that you should not be using Access to make
updates or inserts into MSCRM.  But for reporting or viewing mass
data, Access could be a useful tool.  I usually prefer just SQL or SRS
though.

-Owen

0
Owen
10/23/2007 11:00:16 PM
My issue is I have some issues with the length of field names.
Our integrator has created fileds with names too long for C360 mail merge to 
use (max limit 40 chars)
As I can't rename those fields now they are cretaed tyhe only solution is to 
make new fields and copy the data across.
Copying the data in CRM doesn't seem practical but in Access (or even SQL) 
it's a doddle.

"Owen" <owest@dynamicmethods.net> wrote in message 
news:1193180416.940731.272840@k35g2000prh.googlegroups.com...
> On Oct 23, 8:32 am, "Pat Horridge" <p...@remove-spam.vet.co.uk> wrote:
>> Is it feasible to access my CRM database from an access frontend with 
>> linked
>> tables?
>> I created a new ODBC connection and found a CRM filtered view but that is
>> read only.
>
> You can set up the ODBC connection just as you would to any other SQL
> backend.  There are two tables for most objects in CRM.  So the
> account table is held in accountbase and accountextensionbase.  The
> view you connected to would be called account.
>
> I agree with KKL in that you should not be using Access to make
> updates or inserts into MSCRM.  But for reporting or viewing mass
> data, Access could be a useful tool.  I usually prefer just SQL or SRS
> though.
>
> -Owen
> 


0
Pat
10/24/2007 7:28:20 AM
Can you use a view with C360?  You could write a view and use Cast or Convert 
for those fields to only return them as varchar(40).

Granted, user-created views aren't officially supported by MS (last I heard) 
but at least you're not manipulating the data outside the environment.

If you do decide to create new fields and copy the data over to them, create 
the fields in CRM. You can use a SQL update query to copy the field data. You 
would want to run a query first, to see if anyone has entered more than 40 
characters in these fields.  Then do it as a one-time deal, and customize the 
form(s) so that only the new shorter fields are available to users to add or 
change data.

If you use Access, in order to get write access to the database, you have to 
connect directly to the tables, and use the "sa" or dbo user to connect with 
ODBC.


Good luck!
kkl


"Pat Horridge" wrote:

> My issue is I have some issues with the length of field names.
> Our integrator has created fileds with names too long for C360 mail merge to 
> use (max limit 40 chars)
> As I can't rename those fields now they are cretaed tyhe only solution is to 
> make new fields and copy the data across.
> Copying the data in CRM doesn't seem practical but in Access (or even SQL) 
> it's a doddle.
> 
> "Owen" <owest@dynamicmethods.net> wrote in message 
> news:1193180416.940731.272840@k35g2000prh.googlegroups.com...
> > On Oct 23, 8:32 am, "Pat Horridge" <p...@remove-spam.vet.co.uk> wrote:
> >> Is it feasible to access my CRM database from an access frontend with 
> >> linked
> >> tables?
> >> I created a new ODBC connection and found a CRM filtered view but that is
> >> read only.
> >
> > You can set up the ODBC connection just as you would to any other SQL
> > backend.  There are two tables for most objects in CRM.  So the
> > account table is held in accountbase and accountextensionbase.  The
> > view you connected to would be called account.
> >
> > I agree with KKL in that you should not be using Access to make
> > updates or inserts into MSCRM.  But for reporting or viewing mass
> > data, Access could be a useful tool.  I usually prefer just SQL or SRS
> > though.
> >
> > -Owen
> > 
> 
> 
> 
0
Utf
10/24/2007 12:10:00 PM
Ok thanks I think it was the user authority that was the issue.
Will give it a try.

"kkl" <kkl@discussions.microsoft.com> wrote in message 
news:5A1D5F41-DB58-48DC-9650-C0444EEAE8A1@microsoft.com...
> Can you use a view with C360?  You could write a view and use Cast or 
> Convert
> for those fields to only return them as varchar(40).
>
> Granted, user-created views aren't officially supported by MS (last I 
> heard)
> but at least you're not manipulating the data outside the environment.
>
> If you do decide to create new fields and copy the data over to them, 
> create
> the fields in CRM. You can use a SQL update query to copy the field data. 
> You
> would want to run a query first, to see if anyone has entered more than 40
> characters in these fields.  Then do it as a one-time deal, and customize 
> the
> form(s) so that only the new shorter fields are available to users to add 
> or
> change data.
>
> If you use Access, in order to get write access to the database, you have 
> to
> connect directly to the tables, and use the "sa" or dbo user to connect 
> with
> ODBC.
>
>
> Good luck!
> kkl
>
>
> "Pat Horridge" wrote:
>
>> My issue is I have some issues with the length of field names.
>> Our integrator has created fileds with names too long for C360 mail merge 
>> to
>> use (max limit 40 chars)
>> As I can't rename those fields now they are cretaed tyhe only solution is 
>> to
>> make new fields and copy the data across.
>> Copying the data in CRM doesn't seem practical but in Access (or even 
>> SQL)
>> it's a doddle.
>>
>> "Owen" <owest@dynamicmethods.net> wrote in message
>> news:1193180416.940731.272840@k35g2000prh.googlegroups.com...
>> > On Oct 23, 8:32 am, "Pat Horridge" <p...@remove-spam.vet.co.uk> wrote:
>> >> Is it feasible to access my CRM database from an access frontend with
>> >> linked
>> >> tables?
>> >> I created a new ODBC connection and found a CRM filtered view but that 
>> >> is
>> >> read only.
>> >
>> > You can set up the ODBC connection just as you would to any other SQL
>> > backend.  There are two tables for most objects in CRM.  So the
>> > account table is held in accountbase and accountextensionbase.  The
>> > view you connected to would be called account.
>> >
>> > I agree with KKL in that you should not be using Access to make
>> > updates or inserts into MSCRM.  But for reporting or viewing mass
>> > data, Access could be a useful tool.  I usually prefer just SQL or SRS
>> > though.
>> >
>> > -Owen
>> >
>>
>>
>> 


0
Pat
10/24/2007 12:39:16 PM
Reply:

Similar Artilces:

Closing another database with VBA
Hi, I’m having a problem with the code to do the following… 1- A user opens a file called Core. 2- Upon opening the splash screen (on the Form_Load event), it compares a version number it finds within the database to one that is in another database called Data. 3- If the versions are different, the Core file opens the Updater file. 4- The Updater file closes the Core file. 5- The Updater file copies the latest version of the Core file (located somewhere else) and replaces the first one with the latest version. 6- The Updater opens the newly copied Core file. 7- The Updater closes itself. ...

Interested in Microsoft CRM
My company is a Financial Services company that is interested in implementing MS CRM. This would initially be done for the Investment Advisory portion of our business and later implemented for the rest of the organization. Are there any MS CRM customers/clients in the Investment Advisory business that can comment on their deployment of MS CRM and there overall experience as far as usage of this solution? Also, can you comment on whether the solution was useful without customization or was there customization to the product. If there was customization, then how much (% amount) and wh...

Export from Access
I built an Access database which to export the daliy (new) figures to Excel to the desktop. One of the queries is for pulling all the information for a Credit Manager (CM). I then need to link this data to a summary spreadsheet. So each day I want to go to my database, pick the CM's data, export and replace the old info with the new, and keep the links I have created when I exported the first one. When I exported the first, I linked it to a number of spreadsheets. Any thoughts? I get an error telling me that It cannot expand the named range. If I rename it then I would have to r...

Track in CRM Button
For some users, the track in crm button does not appear when they try to setup calendar appt using outlook. We did install on clean machine for one user experiencing this and the crm button does not appear. Our enviromentL 1. Outlook 2007 2. IE 2007 3. CRM 3.0 with Rollup Update 2 also looked at the following hotfix http://support.microsoft.com/kb/939175/en-us which only applies to Outlook 2003. Are there any other hot fixes that may apply to Outlook 2007? Thanks. ...

Exchange GAL in CRM
Is there an easy way to import the Exchnge Gal into CRM Contacts? ...

Looking up data from Access
I would like to extract data from MS Access into Excel '97 to populate single cell, preferably imitating the 'vlookup' function, i.e. give the value of an Excel cell, the related field name in the externa database query, and the field with the value to return, it wil populate the current cell with this value. Not sure how clear that is. Let me know if it isn't, and thanks fo any help you can give -- Message posted from http://www.ExcelForum.com I've found a solution using the SQL.Request workbook function, whic does what I need. However, I need to populate many cells...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

How to stop tracking a Contact in CRM 4.0
How do I stop tracking an Outlook Contact that has been set to "Track in CRM" Hi Ken, The "Track in CRM" for contacts turns to "View in CRM" when a matching contact record is created in the CRM database. If you no longer want that contact in CRM then you can delete it, then it will say "Track in CRM" again. "Ken Florian" wrote: > How do I stop tracking an Outlook Contact that has been set to "Track in CRM" ...

CRM HTTP Error 401.1 IIS
Hei, I installed CRM on the Windows Server 2003 on three box for 100 users. All users registred in the CRM Server and working on the clients. A: 1 for Active Directory Domain controler (ADSRV) B: 1 for SQL 2000 + CRM Server ver 1.2 (CRMSRV) C: 1 for Exchange Server 2003 (EXSRV) i have one problem that when i type http://crmwebsite on the CRMSRV then i get "HTTP Error 401.1, Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS)". When i try it on the ADSRV then it works well. It is also working well on the client c...

Saving data in Access table with VB ?
I have a form with a button and if you click the button, a list of invoices are generated and saved in the table 'Invoice'. Problem is, the data isnt saved :( Here's my code: Private Sub Knop0_Click() Dim Invoicenr As Long Dim Invoicedate As Date stdocname = "Invoice" DoCmd.OpenTable stdocname, acViewNormal, acAd Invoicenr = 111111 Invoicedate = Now DoCmd.Save acTable, stdocname End Sub What am I doing wrong ? Answered in microsoft.public.access "Bauhaus" <niemandhier@pandora.be> wrote in message news:7Exii.2418$yf6.1885@biebel.telenet-ops.be...

Outlook vs. webaccess crm 4.0
Hi 1) Could someone specify the pros and cons (especially the difference in the "functionality" options) for the end users - if they use the Outlook client or if they use a Webbrowser? 2) What are most common ? thanks for any feedback... It's true you can do everything on the web version or the Outlook version. The biggest feature of CRM is the fact you can everything inside of Outlook and not have to go a different application. Outlook: 1 - Easier concept for end users (especially Sales folk) to grasp. "you don't need to learn a new application - it's ju...

MS CRM 3.0 Customer Case Problem
Dear CRM Experts, I have a problem with MS CRM 3.0: I want to create a web based form in our company website that customers create their Cases (tickets) by it and we (I and my Tech. staff) response to them (Ticketing system). It seems for security reasons we shouldn’t allow CRM browsing outside of our Internal Network (Intranet). Please help me how can present solution for this problem. Best Regards, Mohsen Ahmadi You have a couple of options. There are third-party apps that will let you integrate CRM with your website, but with 3.0 you would probably need to purchase the External C...

Error while import BCM Database into CRM 3.0
I am trying to import BCM Database into CRM 3.0 When I run " Data Migration for Microsoft Outlook Business Contact Manager" wizard I get an unexpected error. Can someone help ? thanks Here is the Log: 8/21/2007 5:15:38 PM------>Transitioning to next screen. From: Welcome screen. To: PreconditionCheck screen. 8/21/2007 5:15:38 PM------>Connecting to Microsoft CRM Shamir_Systems_MSCRM 8/21/2007 5:15:39 PM------>Checking that Microsoft CRM Data Migration Pack is installed: Success 8/21/2007 5:15:39 PM------>Establishing connection to CDF database: Success 8/21/2007 5:1...

CRM 1.2
I am going to move the CRM SQL databases to a new server. We will also be moving Great Plains off the CRM server and on to the new SQL server. We are bouncing around the idea of naming the new SQL server to the name that the current CRM server uses and renaming the current CRM server. The thinking here is that we won't have to touch the Great Plains clients. What kinds of issues can I expect in this scenario? I believe that CRM "might" make us of the SID in which case I would need to use SysPrep to create the new one? I wouldn't think that this is that unusal of a proje...

Web access for Exchange 2003
What do I have to do in order to access my Exchange 2003 via the Web? thanks, Raul Rego rrego@njpies.org On Thu, 6 Jan 2005 14:39:53 -0500, "Raul Rego" <rrego@njpies.org> wrote: >What do I have to do in order to access my Exchange 2003 via the Web? > >thanks, > >Raul Rego >rrego@njpies.org > Check that you can access the server from within the network by going to http://server/exchange and then http://server.fqdn.com/exchange If that works then simply opening TCP80 on the firewall and implementing whatever NAT or port forwarding your specific network ...

Database using Access 2.0
Our church purchased Church Windows software. When we asked if we could import our members from our existing database Greentree CDMS they said we couldn't because CW is an Access 2.0 database and we don't have that software. What would be the benefit of still using Access 2.0 for software development? Thanks, Linda Hi Linda I doubt there are many new programs being written in Access 2. It was a great piece of software, and many databases were writing using it. I don't know Church Windows, but I am guessing that it was written back in the Access 2 days (around 1994), ...

Center Access2007 Form in the middle of access Main Window
Thank You Is there any easy way to center access 2007 forms in the middle of the screen (Monitor) or access main window. and give me good result Hello, I think there is no built in feature to achieve this goal. You may need to create a new form and configure it as the main window. You could enumerate forms and create buttons etc to open forms from this main form. For example, you could use hte following code enumerate forms in your database: Dim db As Database Set db = CurrentDb Set cntContainer = db.Containers("Forms") For Each doc In cntContainer.Documents Str...

CRM and Exchange Problems
Dear All, i have a problem between CRM and exchange, my setup is as follow:- i have a single computer with Windows Server 2003 installed, Exchange 2003, and CRM 1.2, CRM is working just fine, the samething is with Exchange OWA, however, i can't get CRM to send mails throught Exchange, i've installed the Exchange E-Mail Router but with no success, is there is any kind of incompatability between CRM and Windows or Exchange 2003, is there is any guide to follow to solve this? Thanks in advance GQ ...

a program is trying to access e-mail addresses you Stored n Outlook
Hi - After installing Acrobat Standard 6.0, when I try to send an email message using Word or any other program, I get an Outlook message saying "a program is trying to access e-mail addresses you have stored in Outlook". The only choices I get are to allow access for one instance or for a short period of time. What I want to do is to ALWAYS allow Word, PowerPoint, Excel and ACT to access my Outlook e-mail addresses. I went to the Office Service Pack 3 page, which said "To prevent the PDFMaker COM Addin from loading, go to HKEY_LOCAL_MACHINE\Software\Microsoft\Offi...

Moving CRM
Hi All. I am in the process of relocating CRM 1.2 to a new SBS 2003 Server. If I have not created any custom reports with Crytal Reports, is it really necessary for me to back that up and then restore it on the new server? Since I have only been using the default reports, shouldn't everything be fine??? THanks! Jake, You shouldn't need to migrate the reports. But, are you using the redeployment tool for this? If so, then there may be some reasons that this introduces that make it a requirement. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------...

Installing CRM on 2003 Web Edition
Has anyone installed CRM 1.2 on Windows Server 2003 Web Edition. The implementation guide is says it is a supported OS, I am planning on attaching WS03 Web Edition to an SBS 2003 server. I know that the supported configuration has everything on the single SBS box, but I would much rather put some things on other boxes. I am also planning on putting SQL Server on its own box as well. Any comments on this configuration? thanks, Geoff Hi, Beware that SQL Server cannot be installed on a Web Server edition ! -- Olivier Schmitt Microsoft Business Solutions Support Ce message est fourni...

Slow acces CRM 3.0
Hi, We have a problem, our CRM goes slow. There are 10 active users using CRM with web browse, and sometimes Outlook client. Our server is a Compaq Proliant with 2 processor Xeon and 2 Gb of RAM. We installed CRM SBS, exchange, sql server in the same server. We accomplish all recommended requierements says by Microsoft but when I go to open an account it's delay about 10 seconds. Why?? thanks in advance I have recently come accross a client that forgot to turn of the tracing in CRM, and this was causing a lot of delays, and the type of wait time's that you describe. Could it...

Denial of report access
I would like to deny access to specific reports thoughout CRM for a group of users. I have already created Security Group-Domain Local at the Active Directory and assigned some users. Afterwards I opened SQL Server Reporting Services, selected the report I didn't the group to access and assigned the new group with a new role (only task:View folders). At the end I deleted all the other groups except BUILTIN\Administrators and tried to enter as the acess denied user. Unfortunatelly it didn't work. What else should I need to do? Thank you in advance! ...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...

Accessing all questions I have asked in the Office forums
Is there a way I can call up all questions and answers I have ever asked via my login to Microsoft Discussion groups? I sure appreciate your help, John In news:FB996750-8ACF-4806-BDF8-7E66057A0DBB@microsoft.com, John <John@discussions.microsoft.com> typed: > Is there a way I can call up all questions and answers I > have ever asked via my login to Microsoft Discussion groups? > > I sure appreciate your help, John Use Google; look up your own name; finesse from there if you get too many hits. HTH, Twayne` ...