Task Manager Lists cidaemon.exe - Windows Indexing Service


Slow computers are a drag, especially when trying to work or get out of the office quickly.

If you use defrag and other utilities to improve speed without satisfactory results, have you tried turning off the indexing service?

The indexing service runs in the background, indexing files for quick retrieval. However, it uses CPU and that can be frustrating when you are trying to work.


You can create a Desktop shortcut to quickly and painlessly turn the indexing service on and off.

This post about Windows® Search 4.0 Interfering with VBA contains step-by-step instructions on VBQuick:

      Creating a Shortcut to the Indexing Service
      http://www.vbquick.com/2011/09/windows-search-40-interferes-with.html#ShortcutToIndexingService

See Microsoft Support for more information on this issue:

Let me know how this works for you.

How to Place Hidden Quickbooks Table Fields into Microsoft Access

What Lies Beneath

Did you ever wonder what lies beneath your QuickBooks® file? The columns and rows of QuickBooks® tables may surprise you. QODBC makes viewing this hidden information a snap.

Even if you never plan to exchange information between QuickBooks® and Microsoft® Access using QODBC, seeing what lies beneath gives you a better understanding of your data file.

Once you see how quickly QODBC works and how easy it is to use, you may decide to step outside your comfort zone a wee bit and write your own QuickBooks® add-ons.

This post provides quick results with minimum effort on your part. Are you ready? Let's go!



What You Need

  1. QuickBooks®
  2. QODBC read or write version (or free trial version)
  3. Microsoft® Access
This code was tested with:


  1. QODBC Desktop Read Write 2009 Version 9.00.00.253
  2. QuickBooks® Premier Contractor 2009
  3. Microsoft® Access 2002 SP3

Purpose of Code


You do not need to know the purpose of this code to benefit from it. You may prefer to get right to the good stuff and play with QODBC. For the diligant and curious:

The code below retrieves QuickBooks® field names for given tables. You need field names to create SQL strings to extract data from QuickBooks®.

You type the SQL string into the SQL Design View window in Microsoft® Access or in your code. Do not worry if you do not understand this or know how to do it. Just keep reading and you will get there.



Talking to QuickBooks®

SQL stands for Structured Query Language. This is a fancy way of saying that you must speak to computers in a specific way so they understand what you want. Naturally, you cannot type: get all my checks from QuickBooks® into your query. You must format questions correctly. For example:

Select * from customer
This is a simple query that asks Microsoft® Access to retrieve all the data from the QuickBooks® customer table. You absolutey may use this query. Simply replace customer with other table names. You need never become any more complicated than the above query. However, all the data means names, addresses, phone numbers including data you most likely do not care about.




Using Fields Makes Queries Run Faster

Retrieving fields you do not want wastes time, especially with a long customer list. Knowing what fields you want lets you limit results and save time.

For example, perhaps you need a list of customers for use in your Microsoft® Access database. The field names QuickBooks® uses internally often differ from the field names displayed to you. For example, Name, FullName, CompanyName and ParentRefFullName are all customer fields. You need to know which field you want.



Unique Identifiers

QuickBooks® also assigns each account a unique identifier known as ListIDs. These make excellent Microsoft® Access unique identifiers because:
  1. QuickBooks® assigns them
  2. they are unique
  3. no one can change them

If you plan to link database records to QuickBooks® records, you need to know the field names for the ListIDs in each table. The customer table, for example, contains: ListID,  ParentRefListID, CustomerTypeRefListID, TermsRefListID, etc. Do not dismay at the odd sounding field names. After you play with the tables for a while, you will notice a logical pattern to their construction and no longer need to look them up.

For an example of the difference between the fields available from the QuickBooks® application and the fields available when tables are accessed using QODBC see:

      Import QuickBooks Tables: IIF vs. QODBC
      http://www.vbquick.com/2011/10/import-quickbooks-table-iif-or-qodbc.html



How to Use this Procedure



Create a new module and select these references:

  1. Visual Basic for Applications
  2. Microsoft® Access 10 Object Library
  3. Microsoft® DAO Object Library



If you do not know how to do this, see:
  
      The Generic Database:

            Chapter 01: Part 2: How to Create a New Module and Select References
            www.thegenericdatabase.com/2011/09/03-references.html




  1. Copy and paste the code below into the Visual Basic editor of the new module you created.
  2. Type: GetQuickBooksTable into the Immediate Window.
  3. Supply the name of the table you want. QuickBooks® contains over 100 tables. Some examples:
    1. Account
    2. Bill
    3. Check
    4. Class
    5. Company
    6. CreditCardCharge
    7. CreditCardCredit
    8. CreditMemo
    9. Customer
    10. Deposit
    11. Employee
    12. Entity
    13. Estimate
    14. Invoice
    15. Item
    16. JournalEntry
    17. PriceLevel
    18. PurchaseOrder
    19. SalesOrder
    20. SalesReceipt
    21. TimeTracking
    22. Vendor
  4. Allow ample time for QODBC to retrieve records from QuickBooks®
  5. You may need to close the Visual Basic Editor to view the query results in Microsoft® Access.


Additional Codes

If this is your first time using code from VBQuick, you need two functions. These functions let me shorten codes for you by performing simple tasks like deleting temporary tables and queries and creating the QODBC connection string. You should place them into one module so you can import them into new databases for use with other VBQuick codes.

      fncDeleteQuery:
            VBA Access Function fncDeleteQuery Code Only
            www.vbquick.com/2011/10/microsoft-access-delete-query-function.html     

     QODBCConnect:

           VBA Access Function fncQODBCConnect Code Only
            www.vbquick.com/2011/10/vba-microsoft-access-function-qodbc.html



Code

Sub GetQuickBooksTable()   
'Declare variables for database, querydef, query, and table
Dim db As DAO.Database, qDef As DAO.QueryDef, qName As String, strTable As String
'ask the user for the name of the table by using an input box
'or pass the name of the table to strTable as an argument like this:
'Sub GetQuickBooksTable(strTable as string)
'user input below
strTable = InputBox("Enter table name:", "Table Name", "type here") 
'give the query a name
qName = "temp"  
'function deletes query if it already exists
Call fncDeleteQuery(qName)    
'set the database to work in as the current database
Set db = CurrentDb
'create the QueryDef and give the name you decided earlier: "temp"
Set qDef = db.CreateQueryDef(qName)
'use this function to set the connection string
qDef.Connect = QODBCConnect
'set the query to one that return records   
qDef.ReturnsRecords = True
'place an sql string in the query
qDef.SQL = "sp_columns " & strTable    
'now you have a query that will retrieve the QuickBooks table
'you can put that information into a table or use it in a report or another query
'you are finished with qDef and db so set them to nothing   
Set qDef = Nothing
Set db = Nothing
'open the query to view the results if desired   
DoCmd.OpenQuery qName
End Sub



Creating a Table

You may create a table from the results. Add this code immediately before End Sub:
DoCmd.Close acQuery, qName

DoCmd.RunSQL "select * into tbl" & strTable & " from " & qName

The above lines:
  1. close the query
  2. create a new table from the results
The name of the table will contain the prefix: tbl. For example, if you import the customer table, the new table's name will be: tbl_customer.

To view the new table:

  1. Close the Visual Basic Editor.
  2. Select Tables from the Objects list of the database window
  3. Look for tables beginning with the prefix: tbl_
  4. Double-click the table to open and view the results.
Enjoy making your tables and please let me know how this worked for you.

QuickBooks PriceLevelPerItem Part 3: Import into Microsoft Access Table Using QODBC

This is part of a tutorial on how to transfer the Per Item Price Level table from QuickBooks® into Microsoft® Access using QODBC without IIF files or spreadsheets. Click here to start at the beginning.



Review

The previous post:
  1. Explained the relationship between QuickBooks®' items, prices and jobs.
  2. Offered ideas for altering the function.
  3. Examined reasons for importing the PriceLevelPerItem table.
  4. Provided real world applications for using the table.
  5. Supplied copy and paste code to:
    1. connect to QuickBooks® using QODBC
    2. import the custom prices from the Per Item Price Level table
    3. put the data into a Microsoft® Access table
This Post

We will now examine the table and learn how to import one single Per Item Price Level.



Run the Function

Run the function by typing:
ReloadPriceLevelPerItemPrices
...into the Immediate Window of the Visual Basic Editor.

Give QODBC ample time to connect to QuickBooks® and for your function to create the table.


Opening the Table

Open the table in Design View to see the fields.

  1. Select Tables from the Objects list of the Database Window
  2. Click Design from the menubar of the Database Window







Design View lets you see the fields you selected.


Do not change this table. Refreshing the table using the code you wrote deletes any changes you make.



Acceptable Ways to Change Custom Prices

  1. Change custom prices from Microsoft® Access using VBA and QODBC to change the data in QuickBooks®. This differs from changing the data in the table. If you change the data in the table, the changes will not take effect in QuickBooks®.
  2. From within QuickBooks® 
  3. Copy the table


1. From Microsoft® Access Using VBA and QODBC

You can change custom prices from within Microsoft® Access using the QODBC read/write version. See QODBC documentation for how to accomplish this.

Request posts on subjects by filling out my Feedback Form (http://www.vbquick.com/p/feedback.html). I may post on subjects if enough requests come in. I have also helped others when issues intrigued me. The code for QODBC ProfitAndLossStandard Exclude Class (http://www.vbquick.com/2011/10/qodbc-quickbooks-report-profit-loss-vba.html) resulted from a how-to request on Experts Exchange. Occasionally, someone requests code I already have or that requires negligible alteration to qualify.

2. From Within QuickBooks®

QuickBooks® contains an interface for changing custom prices. This interface requires no special VBA programming or external connection.


3. Copy the Table

Contractors may desire to change custom prices temporarily for use in bid creation. Copy the table and work on the copy. This prevents overwriting bid work if someone inadvertently calls the table function.



How to Import a Single Price Level

Import a single price level using criteria on line 80 of the function. For example, QuickBooks® assigns each Per Item Price Level a unique and permanent ListID. You can use the ListID as criteria like this:

where ListID='80000001-1316369099' "



Then line 80 would look something like this:

80 qDef.sql = "select listid,name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName,PriceLevelPerItemCustomPrice,isactive from pricelevelperitem where ListID='80000001-1316369099' "




You may then incorporate a listbox into your form that contains PriceLevels and their corresponding ListIDs. Users would then select a PriceLevel and the ListID would be sent to the Function in a string like below. This would then replace the first line of the function.
Function ReloadPriceLevels(strListID as string)


Then line 80 would look like this:
80 qDef.sql = "select listid,name,PriceLevelPerItemItemRefListID,PriceLevelPerItemItemRefFullName,PriceLevelPerItemCustomPrice,isactive from pricelevelperitem where ListID='" & strListID & "'"

Note: the apostrophes (enlarged and in red above) must surround strListID. The two apostrophes appear:
  1. immediately following the = sign
  2. after the last ampersand (&) and surrounded by quotation marks

These apostrophes appear in red above but will not appear in red when copied into your function.

Below they appear enlarged for your convenience. It is imperative that you do not include any spaces preceding or following these two apostrophes or your query will not work.


ListID='" & strListID & "'"



Summary

Contractors generally use different price levels for different contracts. QuickBooks® Per Item Price Levels let you enter different prices for each item manually.

Many uses exist for importing QuickBooks® Per Item Price Level custom prices into a Microsoft® Access database as outlined previously.

QODBC lets you read and write QuickBooks® Per Item prices from Microsoft® Access using VBA (Visual Basic).

The copy and paste code in this tutorial is only one example of how to use QODBC to exchange information with QuickBooks® through Microsoft® Access.

If you use this function, please let me know how it works for you. If you have any questions, leave a comment and I will do my best to help you.