Community
Showing results for 
Search instead for 
Do you mean 
Reply
Accepted Solution

OLEDB Connection String

Employee
Posts: 1,163
Country: USA

Re: OLEDB Connection String

Are you able to connect to the datasource with anything other than VS? There defintely appears to be some permission issue going on so let's see if we an connect to the OLE via excel or crystal or however you like, just want to narrow the scope of this down some because your code looks fine.
Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

We have tried creating a Windows ODBC data source using OLEDB which uses almost identical procedures as creating a data source from within Visual Studio.  We don't have Excel on the development server but I think that may be the next step as we have seen the FAQ lessons on the community site illustrating connecting Excel. 

 

We have tried the ODBC connectivity on both the development server and the production server.  We have not yet tried Visual Studio connectivity to the production server.  My client's IT manager assures me that both servers were standard installs of ACT 2010.  You have seen and responded to other posts indicating that we are able to connect and read and write using the Framework but the OLEDB is just absolutely essential for one critical data migration task (looking up ACT IDs based on other criteria to populate a field in the 'other' DB I am implementing (we will be using Microsoft's data warehouse loading capabilities for this) and then doing targeted queries to find whether a new contact appearing via the web interface already exists in the ACT DB.

 

I'll get with the IT manager and see if we can get Excel put on the development server.

 

Thanks.

 

Larry

Nickel Elite Contributor
Posts: 937
Country: USA

Re: OLEDB Connection String

Just create a .udl file on the dev server and attempt to connect to the ACT! OLEDB Provider through that interface.  That UI has a test connection button so you can see if it's your code, or an error in establishing the connection/channel.

 

Here's a quick how to:

 

Create a new text doc (use note pad for example).  Rename file and save as test.udl.

 

You'll see the icon change - double click the file.

 

Now you're in the datalink Properties UI - go to the Provider tab - select ACT! OLE DB Provider....

Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

[ Edited ]

OK.  I won't be able to do this until tomorrow morning but I'll give it a try and post the results.

 

I was able to test.  Please see message below.

Message Edited by lwestatbus on 06-07-2010 10:12 AM
Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

OK, THIS is interesting.  I was able to VPN into the server and followed Allan's steps.  But we had no ACT! OLEDB Provider available!!!  I'm guessing that this is significant. 

 

I created the ACTTest.udl and opened it for editing.  I switched to the Providers tab and only found the following:

 

- Microsoft OLEDB Provider for Analysis Services 10.0

- Microsoft OLEDB Provider for Indexing Service

- Microsoft OLEDB Provider for ODBC Drivers

- Microsoft OLEDB Provider for SQL Server

- Microsoft OLEDB Simple Provider

- MS DataShape

- OLEDB Provider for Microsoft Directory Services

- SQL Native Client

- SQL Server Native Client 10.0

 

Should we see exactly "ACT! OLE DB Provider" in this list?  If so, any ideas why it may not be there?

Nickel Elite Contributor
Posts: 937
Country: USA

Re: OLEDB Connection String

Yes, the first 2 providers on that list should be:

ACT! OLE DB Provider for Reporting and

ACT! OLE DB Provider for Reporting 2.0

 

If I had to guess I'd say it looks like there's an install issue on that machine - can you reinstall ACT! on that server?

Copper Contributor
Posts: 51
Country: United States

Re: OLEDB Connection String

Here is where I can see the two ACT OLE DB choices:

 

If I am within Visual Studio and select Data | Add New Data Source... and then select Add New Connection... I can choose Data Source as "<other>" and Data Provider as ".Net Data Provider for OLE DB".  When I select Continue... I then see a dialog and at the top is a list of OLE DB Providers and both ACT Providers are at the top of the list.  I can select either.  I then, however, have no idea what to to with the rest of the entries on this dialog as they don't match any screen I have seen in the Community site or the documentation.  The remaining entries on the dialog are:

 

Enter a Server or File Name frame: 

  Server or File Name text box

  Location text box

 

Log on to the server frame:

  Use Windows NT Integrated Security radio button (grayed out)

  Use a specific user name and password radio button (available)

    User name text box

    Password text box

    Blank password check box

    Allow saving password check box

 

Initial Catalog text box

Advanced... button

Test Connection button

OK and Cancel buttons

 

So I AM seeing the ACT choices here.  I'd previously played around with this approach (still within Visual Studio) but have been unable to come up with any combination of values in the dialog that would let Test Connection work.

 

And, again, when working with the .udl file the two ACT providers are not listed in the dialog available.

 

 

Copper Contributor
Posts: 51
Country: United States

FALSE ALARM

[ Edited ]

FALSE ALARM: What I had originally titled, "Free at Last, Free at Last" was a false alarm.  See post immediately following for an explanation.

 

It WORKS! ! !  I pounded it into submission but I don't know how as the working solution bears only the slightest resemblance to anything we've been discussing here.

 

The general approach I took was to add a new Data Source from within Visual Studio and then making a series of choices (see my immediately prior post) that were really not very intuitive.  (I was in fact fishing but landed the big one.)  When creating a DS this way you have the option of saving the connection string in the project's settings which I did and I was able to see exactly what the dialog came up with (see below).

 

I am going to list the working code that loads a combo box from a query and then describe the steps I took to get it to work. 

 

One startling aspect of the working approach is that no user credentials are passed to the DB.  It uses Windows Authentication which surprises me a great deal.  I actually strongly suspect that this may be a hole in the ACT DB security.  Edit: It turns out that the IT manager had been experimenting with some settings on the machine and had added the server administrator account (under which the test program was running) to the SQL Server MSSQL ACT7 USER group.

 

Code:

 

    Private Sub btnODBC_Click(ByVal sender As System.Object, _ 

      ByVal e As System.EventArgs) Handles btnODBC.Click

        '*********************************************************

        '* Connect to ACT DB using ODBC and load combo box

        '*********************************************************

 

         '* Declare database objects needed for task

        Dim conACT As New OleDbConnection

        Dim comACT As New OleDbCommand

        Dim dsAct As New DataSet

        Dim daAct As New OleDbDataAdapter

 

        '* Configure connection object

        ' conACT.ConnectionString = ACTTest20100525.My.Settings.ConnectionString

        conACT.ConnectionString = _ 

         "Provider=SQLOLEDB;Data Source=REDDEV\ACT7;Integrated Security=SSPI;Initial Catalog=ACT2010Demo"

 

        '* Configure command object

        comACT.Connection = conACT

        comACT.CommandType = CommandType.Text

        comACT.CommandText = _ 

         "SELECT CompanyID, Name " _ 

         & "FROM TBL_COMPANY " _ 

         & "ORDER BY Name"

 

        '* Configure data adapter

        daAct.SelectCommand = comACT

 

        '* Open the connection

        conACT.Open()

 

        '* Execute the SQL and fill a dataset table with query results

        daAct.Fill(dsAct, "Companies")

 

        '* Bind query results to combo box

        cboODBC.DataSource = dsAct.Tables("Companies")

        cboODBC.DisplayMember = "Name"

        cboODBC.ValueMember = "CompanyID"

 

        '* Close the connection

        conACT.Close()

        MessageBox.Show("We got this far")

     End Sub

 

I was also able to write a simple SelectedIndexChanged event for the combo box which would correctly display the selected company's ID in a message box.

 

Steps:

From within Visual Studio 2008 choose the Data menu and select Add New Data Source...

 

Choose Database and Next >

You will be prompted for a data source and provider

    In the Data Source list choose Microsoft SQL Server

    In the Data Provider choose .Net data provider for OLE DB

Choose New Connection... to bring up the Add Connection Dialog

Server Name = MachineName\ACT7
  In our case the entry was
REDDEV\ACT7

Use Windows Authentication

Connect to Database combo box: Select ACT DB (ACT2010Demo)

When prompted during the process save the connection string.  You can then view it in the project where it is available in the Project Properties menu and select the Settings Tab.

 

 

Message Edited by lwestatbus on 06-07-2010 12:34 PM
Message Edited by lwestatbus on 06-07-2010 12:39 PM
Message Edited by lwestatbus on 06-07-2010 01:36 PM
Message Edited by lwestatbus on 06-08-2010 05:59 AM
Copper Contributor
Posts: 51
Country: United States

Re: FALSE ALARM

As noted in the edit post above, the client's IT manager had been experimenting with permissions management with the server administrator login and basically back-doored (unintentionally) administrative permissions on the ACT Database for the server administrator account.  My code was basically connecting using Windows Authentication.

 

When I removed the administrator login from the ACT7 users group on the server my code quit working with a login failure on the conACT.Open statement.

 

What does this tell us?  It tells us that the connection string is essentially correct.  I can see the DB but just can't login to it.  I don't have a valid UserID and Password combination that will talk to the DB. 

 

So I'm now looking for anyone who can give me a connection string that will enable the OLE DB connectivity to the ACT 2010 sample DB (and hopefully to the production DB as well).  If you review the thread history you will see a broken trail of failed attempts.

Solution
Accepted by topic author lwestatbus
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 51
Country: United States

Free at Last (At Last)

OK.  I've finally got it working and it is for real.  If you see the code in the message two before this it is working completely with a modified connection string (see below) and a configuration step.

 

The connection string we are now using is:

 

  conACT.ConnectionString = _

    "Provider=SQLOLEDB;Data Source=REDDEV\ACT7;Password=xxxx;User ID=actreader;Initial Catalog=ACT2010Demo"

 

We were never able to get the ACT OLEDB connectivity to work but stumbled into a hybred of this connection string and then put together some information from Allen (aduet) that got us working in this direction.  In order for this connection string to work we had to use the ACTReader.exe utility found in the ACT installation folder on the server.  This brought up a simple dialog that let us specify a new (and known) password for this account.  It is important to realize that actreader is actually a built in account in the ACT SQL Server DB with read only permissions on the DB table.  This means that using the account requires knowledge of the DB structure (look for Bill's post elsewhere on the site with a link to the DB documentation) and the connection does not have access to the business objects abstraction layer built into the ACT OLEDB accounts.  This is OK for us.  We'll be writing straight SQL anyway and also need the flexibility to write our SQL to take advantage of indices built into the ACT DB (also detailed in the DB documentation). 

 

People wishing to connect to ACT from external programs need to recognize the fundamental difference between using the ACT OLEDB capability and the ACTReader capability and also that both of these approaches are Read Only.  Data manipulation requires the Framework with its own set of capabilities.  The Framework also allows queries but I'm an old SQL hand and am much more confident at my ability to get the data I need with SQL that I know the approach here is what we will be doing for all retrievel. 

 

We now have the ability to create records in ACT via program interface using the Framework (see my post entitled "It's Alive") and now we can write queries in SQL using an OLE DB connection (but not the ACT OLE DB).  I'm pretty confident we can do anything we need with this capability.  The rest is just learning syntax.

 

Many thanks to aduet and mwood for their help in this process.