07-12-2011 07:59 AM
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=U
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,
Solved! Go to Solution.
07-12-2011 10:41 AM
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.
07-12-2011 12:27 PM
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;Conn
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.
07-12-2011 12:32 PM
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.)
07-12-2011 12:40 PM - edited 07-12-2011 12:45 PM
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.
07-12-2011 02:11 PM - edited 07-12-2011 02:26 PM
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.18
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");
07-13-2011 06:13 AM
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.
07-13-2011 07:19 AM
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.