06-07-2010 06:50 AM
06-07-2010 09:02 AM
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.
06-07-2010 09:10 AM
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....
06-07-2010 09:42 AM - edited 06-07-2010 10:12 AM
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.
06-07-2010 10:17 AM
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?
06-07-2010 10:26 AM
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?
06-07-2010 11:32 AM
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
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.
06-07-2010 12:30 PM - edited 06-08-2010 05:59 AM
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.
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
'* Execute the SQL and fill a dataset table with query results
'* Bind query results to combo box
cboODBC.DataSource = dsAct.Tables("Companies")
cboODBC.DisplayMember = "Name"
cboODBC.ValueMember = "CompanyID"
'* Close the connection
MessageBox.Show("We got this far")
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.
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.
06-08-2010 06:09 AM
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.
06-10-2010 12:07 PM
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.