After telling Microsoft® Access that this will be a Pass-Through query, you must designate the source from the properties window. If you forget, Microsoft® Access will simply ask you for the source by displaying the Select Data Source window illustrated below. This makes it easy to choose QuickBooks® for your query without writing or remembering anything.
Click the Machine Data Source tab, select QuickBooks® Data from the list and click OK.
This tool is handy when you can't remember the data source string. However, this window will then appear every time you run the query. I will provide you with a better alternative.
For now, you will type the data source string in the properties window of the query. To do that, select View from the menubar and Properties from the View menu.
on the ODBC Connect Str line of the Query Properties window and close the window (highlighted text below).
Do not worry that you will have to remember this odd string of words because we will automate this with a function later so you no longer need to type it or remember it.
Choose a QuickBooks® Customer
Go to your QuickBooks® customer center and copy the name of one of the customers. We will create your first query to select all the data from the customer table for that customer.
select * from customer where name='customer name'
into the Query window. Do not type customer name inside the apostrophes. Replace customer name with the name of your customer. Keep the apostrophes.
Important: Copy the name from the Customer Name text box of the Edit Customer window as illustrated below because that is the field designated as Name in the customer table. This is imperative when you enter different names in the Company Name text box and Customer Name text box in QuickBooks®. This is why you need to know the names of the QuickBooks® fields and how they correspond to forms and reports in QuickBooks®.
I have a database for you that displays the tables and their corresponding field names but for now just make sure to copy the Customer Name for use in your first query.
Be sure to keep the apostrophes. To keep things simple this first time, do not use a customer whose name contains an apostrophe to avoid the ODBC call failed error.
Run the query either by clicking the exclamation point on the Query Design menu at the top of the Microsoft® Access window or...
...select Run from the Query menu of the menubar.
The first time you connect during a session or after a long time of inactivity, expect a slight delay while QODBC establishes a connection. Frequent connections thereafter are faster.
Your query should display all the data for all the fields of the customer you chose.
If your query returns results, congratulations! If not:
- Make sure the name between the apostrophes is the same as the string in the text box of the Customer Name in the Edit Customer window
- Make sure you closed the Edit Customer window before running your query.
- Make sure you did not include a space after the first apostrophe or before the last apostrophe
If your query still does not produce results, retype it as follows:
select * from customer where name like '%customer name%'
That means keep the apostrophes and percent signs and replace customer name with the name of the customer.
Next you will copy and paste code that places query information into a table for you and then you will create a button so all you do is click the button to create or refresh the customer table. Are you excited? Did you ever think it would be this easy? Let's go!
Do you like this post? Please link back to this article by copying one of the codes below.URL: HTML link code: BB (forum) link code: