Community
Showing results for 
Search instead for 
Do you mean 
Reply

Contact Ole DB Grid - Show External SQL Data in ACT!

Nickel Elite Contributor
Posts: 508
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Allen -

 

Thanks, those were the missing files, I did a build, and it's working fine.  Thanks for your help...

 

 p.s. I'm not a VB developer, so I have *no* idea why it works now.  All I know is the new dll is 33% smaller than the original.  Other than that, I would not know where to look to see details of what happened on my machine with the new build vs. the original.

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
New Member
Posts: 3
Country: United States

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

I am in exactly the same boat. I have downloaded the source and have tried putting the dll in Tools and I get absolutely nothing (no outline, etc). I have taken the source and compiled it under VS2008 -- at this point when I deploy the dll I get a record bar showing 1 record and a blank screen (no matter what db or table I go against). I am trying to connect to the Nwind db.

 

I could really use a little assistance.

Nickel Elite Contributor
Posts: 508
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Michael -

 

I had the same issue when I finally got the dll working - the empty "grid" with record "1 of 1" showing.  After messing with the settings, I did get it to work.  What I'll do is post my settings, and you can double check with what you have and see if it helps.  Also, remember to change a users field (Company, or Contact, or other field) to match something in the database.  In the Northwind example, I followed the example, and changed a user to "Island Trading".  In my test DB, I defined a filed called StudenID.  Here's what I have:

 

1. Databases (I have the Northwind and one I made) are located at "C:\Program Files\ACT\Act for Web".  I am running ACT! by Sage Premium 2009 (11.0) Version 11.0.1.500

2. My two connection strings are:

  - Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ACT\ACT for Web\TestingData.mdb;User Id=admin;Password=;

  - Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ACT\Act for Web\Northwind.mdb;User Id=admin;Password=;

 

3.  SQL statements are (one for my test DB, and one for the Invoice tab of the Northwind DB):

  - SELECT Test_Results.Test_Date, Test_Results.Test_Score, Test_Results.Test_Type

    FROM Test_Results

    WHERE Test_Results.StudentID={Contact.StudentID};

  - SELECT Customers.CompanyName, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia WHERE (((Customers.CompanyName)="{Contact.Company}")) ORDER BY Orders.OrderID

 

 

Feel free to post your settings, and maybe it'll trip an idea...

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
New Member
Posts: 4
Country: United States

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

We have a 3rd party ODBC driver (QODBC that we use to pull SQL data from our accounting software. We can use this driver in Excel, Crystal Reports, Access, etc. I would LOVE to be able to pull our data directly into ACT using this plugin. So the question is: 

 

Does the Contact Ole DB Grid allow this sort of thing?

We can't figure out what the SQLConnectionString would be for this. I'm really hoping there is one.

Do we have to use a DSN to gain access?


They listed a DSNLess String:

 

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\Program Files\QODBC Driver for QuickBooks\sample04.qbw;OpenMode=M;OLE DB Services=-2;"

 

Is there code in that string that would need to be modified in order for it to work? 

Nickel Elite Contributor
Posts: 508
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Patrick -

 

Not sure if I'm missing something, but why not use the ACT! Link for Quickbooks?

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com
New Member
Posts: 4
Country: United States

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Rich -

 

Thanks for the response.  We have QB Enterprise Edition and they don't make an ACT link that supports that.  The standard ACT link works great but we had to upgrade to Enterprise and we've been out of luck ever since.  We've talked to Sage about it and they don't even plan on releasing an ACT link that's compatible. So...we are trying anything we can.

 

Patrick 

 

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

This control executes a SQL statement over a given ADODB supported SQL connection string and displays this data in a data grid in ACT!.  The data itself is not physically in ACT! nor is any ACT! based function (like search ro mail merge) applicable to the data.

 

ADODB supportes DSN-less ODBC connection strings so you should be able ot suse this if your provider supports this.  A note though - you'll need to make sure your provider is installed in the path indicated on the same machine where your ACT! client is (i.e. your Quickbooks install can be anywhere on the network but you need to have an instance of your provider on the same machine as ACT!).

 

As to specific connection strings -  I'm not familiar with the one you have specified...

 

You may have documentation on your provider which might help, also sites like:

http://www.connectionstrings.com/ 

are good reference points. 

 

New Member
Posts: 4
Country: United States

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Allen,

 

This is a big help.  We just didn't know what kind of connection string would be valid since we aren't pulling the data from a SQL or Access DB directly. We will probably have to contact the provider to get some more technical help since they list connection strings on their website that we couldn't get to work.

 

Example of a DSNless connection string (specific to our QB database location):
sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=X:\Quickbooks Ent\American Consumer Shows.qbw;OpenMode=M;OLE DB Services=-2;"
 
Example using current ADO syntax:

sConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=QuickBooks Data;OLE DB Services=-2;"

 

I'm not sure what you mean by the provider being installed on the local client machine.  I have the QODBC driver installed on the client machine and I can successfully pull data into Excel and Access by "getting external data from an ODBC Database" - referencing the machine data source "Quickbooks Data" that was created when the QODBC driver was installed.

 

Anyways thanks for your help.  If you have any other tips, they would be appreciated.  Otherwise we will take this up with the provider.

 

Patrick 

 

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

This is a complete guess here but..

 

Looks like you'd need to pass identity info:

If you do the following - you'd need to pass security info (as you elect to not use the persisted security info):

 

This is what you wrote:

sConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=QuickBooks Data;OLE DB Services=-2;" 

 

So you'd need something like:

"Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=QuickBooks Data....."

 

Again this is a total guess as I don't know that provider.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Contact Ole DB Grid - Show External SQL Data in ACT!

Also, I didn't want my previous post to imply you can't do a DSN connection.  ADODB supports both DSN and DSNless connections.  If you already have a machine connection - might be easier to test it out against that source.