2 Replies Latest reply on Jan 14, 2011 8:45 AM by TimMartin

    Problem setting up ODBC import using SQL Query.



      Problem setting up ODBC import using SQL Query.


      Last year I created an inventory system that hooks up with quickbooks. I used QODBC plug in for Quickbooks as the means to create the ODBC Data source and then bring in and update data form quickbooks. This Data source is still active when I look at the import records odbc data source. The script that I created that has the sql query in it is still working fine. 

      The problem is that I want to create a new import from the same data source. When I use the import record odbc command and select the ODBC data source from quickbooks nothing shows up in FileMakers SQL Query Builder. There are no Tables or Columns. I suspect that I could just build the SQL Query Manually but that just isn't as much fun. Any ideas on what may have happened. Or how to fix it. 


        • 1. Re: Problem setting up ODBC import using SQL Query.

          I am going to hook Quickbooks with FMP 10.  I have QODBC on my system.  May you share some of your codes which setup the ODBC link, and bring in data from Quickbooks?  Very appreciated.

          • 2. Re: Problem setting up ODBC import using SQL Query.

            Here is the script that I use. I have it run when the FM database opens. I used a separate script for each section in qb. I don't remember exactly how I did It but I think that when you select the import Records script step it sends you to a dialogue that helps you select the field matching.

            Show Custom Dialog ["Import"; "This May Take A Few Moments Click to Continue."]

            Go to Layout ["Customers_QB"(Customers)]

            Import Records [No dialog; DSN; R Rentals; SQL Text: Select "Customer"."AccountNumber"............

            Go to Layout [original layout]

            Show Custom Dialog ["Import Complete";"The Import has completed']

            So in this example I have created tables that are copies of the tables in QB and then in my FM solution I reference those copies. I think that you can do a live connection but I was chicken to try that so I just make a copy of the records that I need each time my database opens. To send info back to QB I use FMbooks connect I gather that it is possible to send info back using QBODBC but I haven't had an opportunity to try that yet.

            Hope that Helps!