QuickBooks Tables in Access w/ QODBC Form Part 3

This is a THREE part post continuation.

To complete the form, you must have completed the previous steps beginning here: Form Puts QuickBooks Tables into Access Tables Using QODBC Part 1


Event Procedures With the code entered for the form, you need to set the command buttons and listbox to [event procedure] or they will not work. To set the Reload Tables command button, switch to Design View. If you do not know how to do this and the form is currently in Form View, right click anywhere on the form and select Form Design from the menu that appears.




With the form in Design View:
  1. Double-click the Reload Tables button to open the Properties Window.
  2. Select the Event tab.
  3. Find the On Click row and place the cursor in the text box next to it
  4. Click the down arrow that appears and select [Event Procedure]
  5. Make sure [Event Procedure] appears in the text box next to the On Click event
  6. Repeat these steps for the Reload Columns Table button and lstTables ListBox.


Other Functions

Before testing the form, you will need some other common functions used throughout the VBQuick website.

You may already have these functions in your database. If you already have them and you paste them again, you will produce the "Ambiguous Name Detected" error followed by the name of the function so you can find and delete it.

If you don't have any of these functions, paste them into a Module or Modules:

QODBCConnect:


fExistQuery:


fncDeleteQuery:


fncDeleteTable:


QODBCConnect:


fExistTable:


fncWriteError:



Microsoft Access References

If you do not know how to select references:
  1. Navigate to the Visual Basic Editor
  2. Select References from the Tools Menu on the Visual Basic Editor menu bar

Place a check mark next to the following references. You may have to scroll down to find the ones you need. The code will not work without them.


Testing the Form

If you need your QuickBooks® file open to connect via QODBC, then make sure you open it. You should have QODBC installed and allowed a connection to your QuickBooks® file.

The first time you connect to your QuickBooks® file after opening a database, you may notice a slight delay in querying records. However, thereafter query speed will remain much faster. A slight delay may occur again after an extended period of inactivity even with the database open.

If you attempt to query the columns table first, QODBC will fill it with information from the Account Table.

To fill the listbox on the left, the Tables listbox, click the Reload QuickBooks Tables button and wait. How long QODBC takes to connect depends on your file size, QuickBooks® version, QODBC version and your processor. Watch as the listbox fills with a list of QuickBooks® tables.

Remember, the form on this page is narrow. You may design a wider form with wider listboxes. You also have the freedom to choose any layout, colors, fonts, etc. that you desire.




Select any table in the Table Listbox to see the Columns (field names, properties) for that table. The code will fill the listbox on the right.

Remember, you copied and pasted the column widths, so now that you can see the columns and contents, you may play with widths until satisified with the layout.

Notice that the label below the Reload QuickBooks Tables button contains information about the table you selected. This helps you gain an understanding of the QuickBooks® - QODBC table schema.



To adjust the column widths:
  1. switch to Design View
  2. Double-click the lstFields listbox to open the Properties Window
  3. select the Format Tab on the Properties Window
  4. change the numbers in the Column Widths textbox (note the format: 0.5";)


Reload Columns Table Button

The first time you select a table from the listbox on the left, the code creates a new Columns table in your database from the query results. Thereafter, the form uses the existing table to fill the Columns listbox to save time. Do not alter these tables. You will recognize them by their prefix: tblQODBC_.

These tables may be overwritten and refreshed using the Reload Columns Table button in the event you inadvertantly alter them or any time you wish. If you alter these tables and later overwrite them, your edits will be lost. To edit a Columns table, copy it and then edit the copy as desired.

Now you will be able to see QuickBooks®  tables, columns and properties whenever you wish without opening the VBDemo or writing separate SQL statements.
 
Have fun playing with your new form!

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:

No comments:

Post a Comment