QQube data to ADODB recordset | CLEARIFY

Hello

I am a software developer working for an accounting firm

We have developed a project reporting system/set of dashboards that we are hoping to roll out to several of our clients

Several of our clients use QuickBooks – we were hoping to use QQube as the ‘connector’ between our reporting software and quickbooks.

To do this, within Excel VBA - I was hoping to be able create my own queries and retrieve the data from the datafile to a ADODB recordset at runtime instead of reading it to excel sheets.

i.e. I want to write a VBA script that retrieves data from the QQube database to a ADODB recordset and then filters through the recordset at runtime to get the info I need

Do you have any information as to whether this is even possible? And if so is there any information you could provide me with i.e. how to write a connection string to the database. 

If this is possible - is there someway I can obtain a sample copy of the QQube database so that I might get a 3rd party to assist with building the data retrieval script without compromising live client data.

Really appreciate any information you can give me in this regard

Thanks

The Excel Add-In is just front end to Microsoft Query, and as you will notice in our How To Use QQube area, that we interface with many applications out of the box.

All of this is done with the installation of two ubiquitous DSN's - See this guide: https://clearify.com/wiki/view/92/installed-components

Although the primary reason for QQube is so that customers don't have to deal with tables and relationships, we have many customers who are developers like your self, creating manual queries from our schema - rather than have to spend the months and years reengineering QuickBooks - which we have already done for you.

You can connect using the super user QQubeFinancials DSN.  We can send you a private message with the password for that DSN if you wish to pursue this methodology.

You only need to remember a couple of things: You should never have to attempt to tie one FACT table with another, as each subject is complete.  So for instance, if you were doing inventory, you wouldn't attempt to tie purchases, sales, general ledger, as that information is already in the inventory schema.

Secondly don't try to "think" too hard, as we have already done the hard part of reverse engineering for you.  For instance if you see COGS, you don't have to figure out QuickBooks calculated that, or what transaction, items, are included.

Third, all of our data is at the lowest line granular level.  Financial summary being the exception of course.

Just open up Access or Crystal, or PowerPivot, and you will see the schemas for each subject - and you will also see how the tables in each schema are put together.  Since this is a true data warehouse, there is only one way to tie the tables together, and they are all inner joins, in keeping with true data warehouse principles.  An inner join in a data warehouse, never filters from the left table, what the right doesn't have.  All of that transformation, and messy stuff is handled after QQube loads the data.

What you get is clean, usable pieces of data.

With regard to playing with QQube, you can't do anything to the real QB data, as we are read only program.  You connect harmlessly using the QQubeFinancials DSN and you can play.

Hey thanks for the very prompt reply, 

So from your response I gather that it definitely is possible to get the data from the database to an ADODB recordset at runtime.

But I first need have installed the ODBC drivers, and then any connection string needs to include the DSN, a username and password. Could you please send through the UID and PWD details to my email address.

The connection string that I pass to the ADO object will be as follows:

Provider=MSDASQL; DSN=data_source_name; UID=username; PWD=password;

Can you confirm the above. Do I have the correct Provider?

When you install QQube, the DSN's are automatically created.  

You won't use any connection strings - just the QQubeFinancials DSN

This is way easier than you are thinking.  You should install the trial version of QQube so that you can "see" all of the components.

Ok yes I see that both have been installed.

So here is my code below: It would appear that I am able to access the database

Because I am now getting '[Sybase][ODBC Driver][SQL Anywhere]Table 'vd_Account' not found.

My script so far:

Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Open "DSN=QQubeFinancials"
Set adoRecordset = CreateObject("ADODB.Recordset")
adoRecordset.Open "SELECT * FROM vd_Account", adoConnection
Do Until adoRecordset.EOF
adoRecordset.MoveNext
Loop

What I ultimately want to be able to do is return all transactions from the GL filtered by a particular job name

Any advice you can give me to point me in the right direction is appreciated

The easy way to do this, is use the Excel Add-In, and grab the fields you need from the correct subject.  Then just go ot the Data Tab in Excel, and click on the Properties - and viola your SQL is already written.

Totally not used to things being made this easy for me...

Awesome! Thanks

Ok so further to our discussions earlier - I have been experimenting with obtaining the data I want to extract. I basically need to be able to do the equivalent of an Excel SUMIFS formula where criteria could be Account, Job Name or Transaction Date. From the various forums I have looked at this is definitly something that can be done in SQL by adding WHERE criteria to the query.

The query I am able to run at the moment is as follows:

SELECT vd_CalYrTxnDate."CalYr Txn Date", vf_GLDETAIL."GLTxn Line Amount", vd_Account."Account Number", vd_Job."Job Name"
FROM QQubeUser.vd_Account vd_Account, QQubeUser.vd_CalYrTxnDate vd_CalYrTxnDate, QQubeUser.vd_Job vd_Job, QQubeFinancials.vf_GLDETAIL vf_GLDETAIL
WHERE vf_GLDETAIL.LinkToAccountID = vd_Account.LinkForAccountID AND vf_GLDETAIL.LinkToTxnDateID = vd_CalYrTxnDate.LinkForTxnDateID AND vf_GLDETAIL.LinkToJobID = vd_Job.LinkForJobID

I tried appending the 'WHERE' part of the statement with further criteria but I must be stuffing the syntax up. If you help me further by demonstrating how you would append the syntax to filter by 'Transaction Date' I hopefully could figure out the rest.

We are using Sybase SQLAnywhere, so one way to help you is point you to here: https://dcx.sap.com/1201/en/dbreference/select-statement.html

But you already forgot your Excel Add-In friend: Use the Excel Add-In functionality, and filter a date, then look at the SQL in the Data Tab>Properties to see how the query is constructed.  Again, easy street.

Sum is a GROUP BY functionality, which means that you will need to have the fields in your query, also show in your GROUP BY statement. You can also use Window Clauses which gets around the GROUP BY difficulties.

As for the where criteria, stay out of the table relationships, and instead use where x = abc.  However you cannot have a where clause AFTER the GROUP BY clause.

Again, look at your result in Excel.