Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Stored Procedures and Access 2007Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Vista and Office 2007 Forum

 New Topic 
 
 Post Reply 
[1]  
 Stored Procedures and Access 2007 
 Author   Message 
  Pamela 
  
 Group: Members 
 Posts: 11 
 Joined: 2007-06-20 
 Profile
 Posted : 2007-10-17 04:30:52
In Access 2003 I have built a stored procedure as follows

sp_report IncomeByCustomerDetail show Name, Blank, TxnType, Date, RefNumber, Memo, Account,Amount parameters DateMacro = 'ThisYear'

 

This returns the data set I'm looking for.  However when we run it in Access 2007 (with QODBC 7) it freezes the QBES (2007) and Access. No data set is returned.

Am I doing something wrong? Is there another way to run the stored procedures in Access 2007?  All other queries developed in Access 2003 have run in Access 2007.

Thanks, 

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-10-17 09:52:29

I just tried creating a pass-though query using MS Access 2007 and apart from Microsoft making it harder to do than MS Access 2003, your sp_report worked fine for me.

To Create a pass-through query
On the Create tab, in the Other group, click Query Design.
Close the Show Table dialog box.
On the Design tab, in the Query Type group, click Pass-Through.
Access hides the query design grid and displays the SQL View object tab.
If the query property sheet does not appear, press F4 to display it.
On the property sheet, click the ODBC Connect Str property box, and then click Build .
The Select Data Source dialog box appears.
Click the Machine Data Source tab.
Under Data Source Name, click the name "QuickBooks Data", and then click OK.

This will create a connection string like this:

ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=%UserProfile%\QODBC Driver for QuickBooks\Optimizer;OptimizerCurrency=O;OptimizerAllowDirtyReads=D;SyncFromOtherTables=N

Change it to just:

ODBC;DSN=QuickBooks Data

like here:

Once a connection string has been created, you can just click on the report and the datasheet view will be displayed:



 

  Top 
  Bryce 
  
 Group: Members 
 Posts: 3 
 Joined: 2007-10-19 
 Profile
 Posted : 2007-10-19 03:31:39

Dear Tom,

     Thanks for your help on this Access 2007 issue (I am working with Pamela). I followed your directions exactly and got the same problem - QBE froze; had to end task on it and Access 2007. I then went to that site where the stored procedure came from ( http://doc.qodbc.com/qodbc/20/reports/sp_report_detail.html ) and tried a few others. The one for Open Invoices worked fine, while a couple others caused other errors like "too many columns". Is there some option in the QODBC configuration or a tweak in Access 2007 that makes the difference?

Bryson

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-10-19 08:26:39

Nothing within the doc your link goes to is tested. Please use: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? instead. I suggest you use VB Demo to test the syntax of your stored proccedures first before executing them in MS Access 2007.

By the sounds of things you have a large company file, so please be sure to limit reports by using DateMacro = 'ThisYear' .

All the predefined DateMacro options available to you are:
|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter|ThisQuarterToDate
|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth|LastMonthToDate|LastQuarter
|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|

 

  Top 
  Bryce 
  
 Group: Members 
 Posts: 3 
 Joined: 2007-10-19 
 Profile
 Posted : 2007-10-26 08:51:09

Dear Tom, Pamela, and any other interested readers,

     Thanks again for your help with the pass-through queries and Access 2007 issues. I seem to be having good success now after I put our Quickbooks Enterprise Program into single user mode. At this point, I cannot give details as to why our particular configuration results in hanging both QBE and also Access on most of the stored procedures if we are in multi-user mode. But, hallelujah, Once I change into single-user mode, those same procedures work fine (quite slowly, but without hanging the programs). So, I am glad that I can utilize those procedures now with the constraint of doing so in single-user mode.

Bryson

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-10-26 08:59:25
That's interesting, but I always run my company file in Multi-user mode because MS Access will execute each pass-through query as another connection to QODBC, meaning that the company file will need to be in Multi-user mode for it to work. So be careful to only run them one at a time...... 

  Top 
  Bryce 
  
 Group: Members 
 Posts: 3 
 Joined: 2007-10-19 
 Profile
 Posted : 2007-12-20 08:45:36

Tom - although it is quite a bit later than our last dialog, I want to thank you for getting me started and functioning with pass-through queries. I have gotten the hang-of-it and It has been going well, although it is still a slow process whenever there is more than a few records involved.

Can you please tell me if you have a stored procedure for the Quickbooks "Employee Contact List" or how I could create my own?

Thanks,

Bryson

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-12-20 14:39:01

Just do:

Select * from Employee

or:

SELECT Name, SSN, HiredDate as "Hired Date", ReleasedDate as "Termination Date", IsActive as "Active" FROM Employee

and see: How do I run a Time by Name Report? for more.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to