Fairly new to ACT! - Software Developer looking to access database directly

[someguy05]

Hey all,

Let me start by saying I'm new to ACT!.  I'm currently working on an application for our customer service department that will add follow-up notes to contacts in a variety of our applications, with ACT! being the one I'm having issues with at the moment.  In testing, I have been using the ACT2012Demo database on my local machine, and I believe I've found the tables I need to add to, and it seems to be working great.  I'm trying to connect to the production server, and it looks like the only SQL users listed on the instance are sa and BUILTIN\users.  My IT people aren't sure if they've ever had the sa password, so my question is, if this is the case is there a way to reset that password and add other dbreader/dbwriter users to that database?

If so, could someone guide me in that direction so I can start down that path tomorrow?

Thanks so much!

Mike

[GLComputing]

You can't write directly to the SQL tables ... it will break it.

You need to use the SDK. See:
http://blog.glcomputing.com.au/2009/07/connecting-to-act-by-sage-data.html

[someguy05]

Hi Mike,

Thanks for the reply.  My question isn't whether or not it should be done, but how to access the data our company owns.  If I wanted to get access to that SQL instance with a new user, is it possible?  If so, how would I get that new user created with read/write access to the live database?

Thanks!

Mike

[GLComputing]

Have a look at the link I posted ... has a number of ways to read the data and a couple to write it

[EUROPOWERGenerators]

How can writing to an existing field in an existing record break it.
I can understand if you're trying to add new records and screwing up the GUID's ACT uses to link history to contacts and such, but writing to an existing field in an existing record breaking anything?

I would love an explanation on how that could happen, and don't be sparse with the technical facts, I can take it.

[GLComputing]

Because it doesn't write to sync logs and other areas.

It's also not legally permitted in the EULA

[EUROPOWERGenerators]

Not writing in the sync logs is a non issue in an environment without syncing.

Not being able to legally have direct access to your own data is also something that sage should throw out of their EULA if they have at least a bit of intelligence.

Why should anyone be stopped from making a quick and dirty tool that does what they need, when they need it.

[GLComputing]

To read the data directly, you have full access via ODBC (using the ACT Reader) or OLEDB (much better)

Writing is done via the SDK to ensure that users don't damage the system

[jimdurkin]

I encountered the same feeling when I started with the ACT SDK a few years back. “Why can’t I write to the existing DB?” Seemed very logical for any programmer to read/write to the DB.

Answer: By entering values directly into the DB you are bypassing the business logic layer including: Security Model,  Data Integrity, Field Triggers, Smart Notes Triggers,  Stored procedures, Sync Logs,  Field linking,  ect

What would happen if you;  enter the end date of an activity which is before the start date? Or entered a value not allowed in a pick list - Only allow “apples, oranges or peaches” and you enter bananas?  These data integrity issues could cause the ACT database to break. It’s for this reason ACT created a robust SDK FrameWork.

I know you stated you need a quick and dirty program but the SDK allows you do do just that!

SOLUTION:

Using these five lines of code you change a value in the ACT DB!

                '  Create the framework

                Dim ACTFramework As New Act.Framework.ActFramework

                'Log into the framework – NOTE takes 10-20 seconds so but this outside a loop

                ACTFramework.LogOn("LastPadFile", "Lastusr", "LastPw") 

                ' Create a Contact List

                Dim ContactList As Act.Framework.Contacts.ContactList = ACTFramework.Contacts.GetContactsByID(Nothing, New System.Guid() {Guid})

                ' Since we only passed one GUID then the list should only return one contact record

                ' Let’s change the value of that record

                ContactList(0).ContactFields("Table_Contact.JOBTITLE", False) = "Test Title"

                ' Now update the record

                ContactList(0).Update()

For those who are VB deprived, here is the c# code

          // Create the framework

          Act.Framework.ActFramework ACTFramework= new Act.Framework.ActFramework();

          //Log into the framework – NOTE takes 10-20 seconds so but this outside a loop

          ACTFramework.LogOn("LastPadFile", "Lastusr", "LastPw");

          // Create a Contact List

          Act.Framework.Contacts.ContactList ContactList = ACTFramework.Contacts.GetContactsByID(null, new System.Guid[] { Guid });

          // Since we only passed one GUID then the list should only return one contact record

          // Lets change the value of that record

          ContactList(0).ContactFields("Table_Contact.JOBTITLE", false) = "Test Title";

          // Now update the record

          ContactList(0).Update();

If you are looking to create a note then its four lines of code:

                '  Create the framework

                Dim ACTFramework As New Act.Framework.ActFramework

                'Log into the framework – NOTE takes 10-20 seconds so but this outside a loop

                ACTFramework.LogOn("LastPadFile", "Lastusr", "LastPw") 

                ' Create a Contact List

                Dim ContactList As Act.Framework.Contacts.ContactList = ACTFramework.Contacts.GetContactsByID(Nothing, New System.Guid() {Guid})

          ‘ Create the note

          ACTFramework.Notes.CreateNote(New Act.Framework.Notes.NoteType(Act.Framework.Notes.SystemNoteType.Note), "notes text string", Now.Date, False, ContactList(0).)

Hope this helps

-- Jim Durkin

[someguy05]

Ok, thanks for the help everyone.  While I still don't agree with locking customers out of their own data, it's apparently what I need to work with.

I'm trying to get the SDK installed into my custom application (C#), and I downloaded the SDK zip for 2012, that doesn't seem to be what I need at all.  Since I'm not trying to make a plugin for the existing ACT! application, but rather modify ACT! data from within my custom app, I'm assuming I need to grab the DLLs from the ACT! install directory and reference them within my app.  I am playing with your code to start Jim, but while I can get most things to work, Visual Studio doesn't seem to recognize HostFramework, even if I add every single DLL from the main install folder...

Does anyone have an idea on how to get started with adding references to a completely custom app that modifies ACT! data?