Combining Sugarcrm with Microsoft Access

[adechent]

Hi,

I'm very new to the topic of crm, and i have no experience in php or mysql, but the solution, sugarcrm offers to me is almost perfect.

In fact, what I can is working quite extensively in Access.

The most important feature I miss with sugarcrm is generating mail merges (not e-mail) based on the contact list of sugarcrm.

Therefore I installed a MYSQL ODBC driver and now I can access the databse using Access.

I also managed to extract a list of contacts that match certain criteria of their respective Account or of contact details.


Now I would like to add a note to all contacts that where in the result list of the query mentioned above.

This note should contain the word document that was used for the mail merge.


SO NOW MY QUESTION:

1. Is there any way to generate new lines in the "Notes" table in the mysql database from Microsoft Access?

2. Is there a way to integrate the word document into sugarcrm using Microsoft Access?


Thank you very much for your help.

Once I have completed my work on the access database, I can, if requested, publish it to everyone...

Bye bye

[pedroa]

Hi,

"1. Is there any way to generate new lines in the "Notes" table in the mysql database from Microsoft Access?"

Have you any problem with inserting new records, if you could find the odbc solution for linking mysql to access inserting rows seems not a big problem to me, any errors? or do you mean bulk insertions, like to all accounts?

"2. Is there a way to integrate the word document into sugarcrm using Microsoft Access?"

Has you may know files are not fisically in the database, they reside in a folder of files and it's their reference that is saved in the database and linked trough an identifier to each subject of the file (say and account). or your problem is bulk operations? like send every account a reference to the file.

I am partitularly interested in this thread so feel free to post, i will be watching.

Pedro A.

[adechent]

Hi pedroa,

first of all thank you very much for your reply!

The Problem with inserting lines is the primary key. I don't know how to automatically generate a valid key and insert it into the respective field.

The problem with the file reference is to find a way to automatically generate such a link using access (to append the mail merge word document to all notes that I want to generate for every person who is among the adressees of the mail merge).


So, I think I could solve the other problems using macros, if i knew how to generate valid Primary keys...


Thank you very much for your help!
cu

[pedroa]

Hi adechent,

Sorry for only posting today but i guess it will be worth it.

The primary key i believe is some sort of an GUID so the best thing to have at hand is an GUID generator.
In Access go to the modules and create a new module.
In the code pane paste de following


' modCreateGUID - Create a Globally Unique Identifier (GUID)
' 1997/07/11 Copyright © 1997, Larry Rebich, The Bridge, Inc.
' 1999/12/13 Use API StringFromGUID2 to format the GUID and return it as a string, _
From: http://vbthunder.com/, Ben Baird
'
' Samples:
' {3201047B-FA1C-11D0-B3F9-004445535400}
' {0547C3D5-FA24-11D0-B3F9-004445535400}

Option Explicit
DefLng A-Z

' The following is from Topic: Windows Conferencing API, GUID, MSDN April 1997
' typedef struct _GUID {
' unsigned long Data1;
' unsigned short Data2;
' unsigned short Data3;
' unsigned char Data4[8];
'} GUID;
'
'Holds a globally unique identifier (GUID), which identifies a particular _
object class and interface. This identifier is a 128-bit value.
'
'For more information about GUIDs, see the Remote Procedure Call (RPC) _
documentation or the OLE Programmer's Reference.
'
'Use the guidgen.exe utility to generate new values.
'See also CONFDEST, CONFGUID, CONFNOTIFY
'© 1997 Microsoft Corporation

Private Type GUID
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(0 To 7) As String * 1
End Type

Private Declare Function CoCreateGuid Lib "ole32.dll" (tGUIDStructure As GUID) As Long
Private Declare Function StringFromGUID2 Lib "ole32.dll" (rguid As Any, ByVal lpstrClsId As Long, ByVal cbMax As Long) As Long
'

Public Function CreateGUID() As String
Dim sGUID As String 'store result here
Dim tGUID As GUID 'get into this structure
Dim bGuid() As Byte 'get formatted string here
Dim lRtn As Long
Const clLen As Long = 50

If CoCreateGuid(tGUID) = 0 Then 'use API to get the GUID
bGuid = String(clLen, 0)
lRtn = StringFromGUID2(tGUID, VarPtr(bGuid(0)), clLen) 'use API to format it
If lRtn > 0 Then 'truncate nulls
sGUID = Mid$(bGuid, 1, lRtn - 1)
End If
CreateGUID = sGUID
End If
End Function

Compile it, save it as mdlGUID (or whatever you like just as long as you save it)
OK, now to have a freshnew GUID invoke the CreateGUID() function (it has no parameters),
in code you can do this with a simple: myVar = CreateGUID()
Or you can invoke it as the default value for a field in your database.

I am just guessing here so it is now up to you to make it right, any questions just keep posting and i will try to help you.

Pedro A.

[hubertus]

Hi,
Now I would like to add a note to all contacts that where in the result list of the query mentioned above.
This note should contain the word document that was used for the mail merge.

Hi,
maybe you will find this module useful http://sugar.go-mobile.at ,
greetings,
hubertus

[john]

Hubertus,
Nice module! Can you create a project on www.sugarforge.org and point it towards your site. I think a lot of folks will find your docs module very useful.
Regards,
John
SugarCRM