Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
dbenglund
Posts: 10
Country: USA
Message 1 of 9 (711 Views)
Accepted Solution

Help with ACT! OLE DB connection string

I am attempting to use VB.NET & OLE DB to connect to our ACT database running on a server, and I believe something is wrong with my connection string. I've read the other posts here, and the suggestions haven't worked thus far.

 

Imports System.Data.OleDb

Dim ActOleDbConnection = New OleDbConnection("Provider=ACTOLEDB;Data Source=ServerName\ACT7;Database=DatabaseName;Uid=UserId;Pwd=Password;"



 

The exception being thrown:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

"Multiple step OLE DB operation generated errors."

 

I've registered the ACTOLEDB service on my computer; ACT! OLE DB Provider for Reporting is available on both my computer and the server; and "Enable OLE/DB Reporting" is set to True for the database.

 

I'm fine with switching to ODBC and using ACT! Reader, but I couldn't get a connection string that worked for that either; I just need something (anything) that will let me pull from the database using a script. Setting up ACT!'s SDK is a possibility, too, but that doesn't seem cost-effective for me if all I need is read-only access to the database for some scripts.

 

Thanks for the help,

David 

Please use plain text.
Employee
mwood
Posts: 1,163
Country: USA
Message 2 of 9 (698 Views)

Re: Help with ACT! OLE DB connection string

Hello David,
Here is the connetion string autogenerated by Act via my UI application.


Provider=ACTOLEDB2.1;Data Source=ServerName\\ACT7;Initial Catalog=NewTest;User Id=userID;Password=pword;Connect Timeout=30


If your creating this as a plugin you can simply grab the ActApplication.ActFramework.CurrentACTOLEDB2 object, it will establish a connection and enforce security based on the logged in user.


We can verify whether it's a issue with the connection string or the provider by creating a .udl test file. Simply change the extension on a new text file to udl and use it to test the connection. 

 

You may also find this post helpful.

 

HTH

Matthew Wood
Act! SDK Support
Community Moderator
Please use plain text.
Copper Contributor
dbenglund
Posts: 10
Country: USA
Message 3 of 9 (693 Views)

Re: Help with ACT! OLE DB connection string

That connection string (my information substituted) did not work either. Updated connection string:

 

Provider=ACTOLEDB;Data Source=NameOfServerHostingDatabase\\ACT7;Initial Catalog=OurDatabaseName;User Id=AdminUserName;Password=CorrectAdminPasword;Connect Timeout=30

 

The Data Link test (*.udl file) completed successfully. I selected the ACT! OLE DB Provider for Reporting, clicked Next, browsed to the database file on our server, entered the username and password. No problem. It's the connection string in my (very simple) program that doesn't work.

 

You'll note that the resolution to the thread you referenced was to use SQLOLEDB as the provider. That's just a workaround. In that same thread, Sage's moderator, alduet, said regarding such a workaround, "The entire schema is hard to understand (OLEDB2 abstracts it nice and tidy for you), also any queries ignore user level security." The user never got the connection to work through ACTOLEDB.

 

The connection string you gave me came from the ACT! in a plug-in context. You're right; I could use the SDK and create a plugin, but I don't have that yet. I've been waiting to get it from Sage. I'm stuck, and I've been at this for days.

 

What format is ACTOLEDB looking for? I can't find the documentation that says, "The ACTOLEDB provider requires the following connection string format: ......" That's what I need.

Please use plain text.
Copper Contributor
dbenglund
Posts: 10
Country: USA
Message 4 of 9 (691 Views)

Re: Help with ACT! OLE DB connection string

If the SDK is the best way to achieve external database access, I'm fine with doing that. If I can't resolve this the way I want to here, I'll just have to wait until I hear back from Sage. (I filled out this form.)

Please use plain text.
Employee
mwood
Posts: 1,163
Country: USA
Message 5 of 9 (690 Views)

Re: Help with ACT! OLE DB connection string

[ Edited ]

There shouldn't be any waiting time to receive the SDK, it's free and you already have all the necessary references. The only thing in the dowload is the documentation which can be found here. Once you fill out the form the download should start immediately. 

 

Outside of the SDK and applications that interact directly with the provider (Crystal) I've not attempted to connect to it, additionally I don't have any documentation that spells out clearly the connection string format it's looking for. My connection string didn't have a plain text password and I think that may be the issue. Every instance I can find on this board of someone seeking this same information the solution was to go through the SDK.

 

@Edit: I see that you've already filled out the form and you didn't get redirected, would you mind trying it in another browswer, after submitting the form I was redirected to another page to choose the version to download. If it continues to be an issue contact me via PM and we'll see if we can get it to you some other way.

Matthew Wood
Act! SDK Support
Community Moderator
Please use plain text.
Copper Contributor
dbenglund
Posts: 10
Country: USA
Message 6 of 9 (681 Views)

Re: Help with ACT! OLE DB connection string

[ Edited ]

Thanks, Matthew. I have the SDK, and I think I have it working.

 

In my project, I've added as a reference the DLL, C:\WINDOWS\assembly\GAC_MSIL\Act.Framework\11.1.183.0.......\Act.Framework.dll and Act.Shared.Collections.dll in a similar folder. Is this where I should get these libraries?

 

I am able to log onto the server database. At least, it's not throwing exceptions now... Does this all look right? Am I doing this like I should?

 

Imports Act.Framework

Dim fw As New ActFramework
fw.LogOn("\\\\server\\drive$\\path\\dbfile.pad", "Username", "Password")

 

 

Or the equivalent C# code:

 

using Act.Framework;

ActFramework fw = new ActFramework(); 

fw.LogOn("\\\\server\\drive$\\path\\dbfile.pad", "Username", "Password");

 

Thanks,

David

Please use plain text.
Employee
mwood
Posts: 1,163
Country: USA
Message 7 of 9 (668 Views)

Re: Help with ACT! OLE DB connection string

As for my references, I typically create a folder somewhere called Act11.xGAC or something similar and copy all the contents of the GAC folder to it, this is only so that the references I'm currently using don't get removed with an upgrade.

 

The code looks spot on.

Matthew Wood
Act! SDK Support
Community Moderator
Please use plain text.
Copper Contributor
dbenglund
Posts: 10
Country: USA
Message 8 of 9 (666 Views)

Re: Help with ACT! OLE DB connection string

Thanks a lot! I'm excited about moving forward with this. Great first experience with ACT! support.
Please use plain text.
Employee
mwood
Posts: 1,163
Country: USA
Message 9 of 9 (664 Views)

Re: Help with ACT! OLE DB connection string

Glad I could help, if you have future questions related to the SDK don't hesitate to post them in the Sage Act! Development Forum.

Matthew Wood
Act! SDK Support
Community Moderator
Please use plain text.