The code below can be pasted into an Access module and run. It is the fastest way that I have seen to copy the data over. The DSN is in the source of the CopyTable function if it needs to be different from the default of "QuickBooks Data".

Option Compare Database
Option Explicit

Sub Main()
    CopyTable "Customer"
End Sub

Sub CopyTable(sTableName As String)
    Dim sTmpTableName   As String
    Dim oTmpTableDef    As TableDef
    Dim oIndex          As Index
    Dim iCnt            As Integer
   
    CurrentDb.TableDefs.Refresh
    '*************************************************************************
    'WARNING: The Existing Table Data will be destroyed with out any warnings.
    '*************************************************************************
    sTmpTableName = "xxTmp_" & sTableName
    'Find if the tables already exists remove them
    On Error Resume Next
    DoCmd.DeleteObject acTable, sTableName
    DoCmd.DeleteObject acTable, sTmpTableName
    On Error GoTo lblErrorMessage
   
    'Create a temporary table linked to QODBC
    Set oTmpTableDef = New TableDef
    oTmpTableDef.Name = sTmpTableName
    oTmpTableDef.SourceTableName = sTableName
    oTmpTableDef.Connect = "ODBC;DSN=QuickBooks Data;DFQ=.;SERVER=QODBC;TABLE=" & sTableName
    CurrentDb.TableDefs.Append oTmpTableDef
    Set oTmpTableDef = Nothing
   
    'Create the Table from our linked table
    CurrentDb.Execute "SELECT * INTO " & sTableName & " FROM " & sTmpTableName
   
    'Add Index information
    CurrentDb.TableDefs.Refresh
    For iCnt = 0 To (CurrentDb.TableDefs(sTmpTableName).Indexes.Count - 1)
        Set oIndex = New Index
        oIndex.Clustered = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Clustered
        oIndex.Fields = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Fields
        oIndex.IgnoreNulls = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).IgnoreNulls
        oIndex.Name = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Name
        oIndex.Primary = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Primary
        oIndex.Required = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Required
        oIndex.Unique = CurrentDb.TableDefs(sTmpTableName).Indexes(iCnt).Unique
        CurrentDb.TableDefs(sTableName).Indexes.Append oIndex
        Set oIndex = Nothing
    Next iCnt
   
    'Remove our temporary link
    DoCmd.DeleteObject acTable, sTmpTableName
    CurrentDb.TableDefs.Refresh
    Exit Sub
lblErrorMessage:
    MsgBox Err.Number & "-" & Err.Description
    Exit Sub
End Sub

I am working with QOBDC and Crystal to create reports on the company performance as a whole as opposed to specific transactions (like an invoice, or specific customer etc). Because of this the only option is to use the sp_reports which can take a long time to run if transactional, or for summary reports such as P/L by class, the results are not fun to work with.  Since it only takes a couple of hours to reload the entire database, I am beginning to think having an access file with all my tables copied in it, then letting Crystal connect with Access would be more functional.

I ran this code for a number of my tables so far and it appears to work great.  But my questions are:

-Although I can't write code, I can essentially understand what the code is doing. Since there is the warning about an existing table being removed, I was curious if it would also remove links and tested it.  When i try to rerun, I get an error that the table already exists and stops.  Is this code for initial load only?

-since this post was written in 2006, and there have been improvements to the driver,  is this still your recommended method to copy the database?

-what would be your recommendation to update each table after initial load?

Thanks for any help.

The code is an example of what can be done using MS Access and ins't any knind of recommendation. QODBC already optimizes all the QODBC tables in a SQLite Server that runs on your computer, so there's no need to copy table data. The optimized data is obtained directly by simply adding a NOSYNC tag like this:

Select Name from Customer NOSYNC

I have used the NOSYNC and it works very fast. But unfortunately, few of my needs are for related tables of a specific transaction.  I am looking at groups of transactions for specific events.  Essentially I need the transaction table but with more details such as class.

Since UNION does not work with QODBC, I need to be able to process the UNION statement in Access first and this is how I thought I would go about doing that.  Any thoughts on a better way would be appreciated.

Thanks

The Transaction table doesn't show the Class but it can be seen using a CustomTxnDetail report instead like this:-

sp_report CustomTxnDetail show Text, Blank, TxnType, Date, RefNumber, Name, Memo,
Account, ClearedStatus, SplitAccount, Class, Debit, Credit, RunningBalance
parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly',
OpenBalanceAsOf = 'Today'

This report can be also broken into date range chunks like this:

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2008-01-01'}, DateTo = {d'2008-06-30'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2008-07-01'}, DateTo = {d'2008-08-31'},
SummarizeRowsBy = 'Account'

See: How do I run a CustomTxnDetail Report? for more information.