Access Data Import using SQL from another Database Table

I'm importing data into Access on a daily basis.  The data in one of the tables ("InvoiceLine") is too big.

The command I use to import the table (by macro) is:
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", False

Open in new window


This grabs/imports all 270,000 rows from the source database table.  This causes the Access database to balloon to over 2.0gb and I end up having issues compacting.  Additionally, there are sometimes issues during the import.

I'm wondering if it would be possible to run a similar command with a Query attached so that only transactions with a (date > (today - 180 days)) - or do I have to import everything and then trim the database?

Thanks for your help!
LVL 1
slightlyoffAsked:
Who is Participating?
 
Daniel WilsonConnect With a Mentor Commented:
The first way I could see to do it would be to create a named view on the source database that would filter to the rows you want.  Then instead of the table name, you would specify the name of that view.

http://msdn.microsoft.com/en-us/library/office/ff196455.aspx

Now, since I know from the previous question that your source DB is Quickbooks ... I really doubt creating that view is possible.

I would have a look at the Linked Table option at http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2471/57/using-quickbooks-data-with-access-2013import-tables-32-bit

You could then load your real Access table (if you even need to) using a SQL Statement that included a WHERE clause.
0
 
slightlyoffAuthor Commented:
Thanks for the quick reply!  I forgot to mention the source database was QuickBooks, thanks for mentioning that.

So in other words, create a Linked Access Table, then using something like an append query grab just the data I want into a different table?
0
 
Daniel WilsonCommented:
So in other words, create a Linked Access Table, then using something like an append query grab just the data I want into a different table?

That's what I would try.  I haven't done it ... but ... it seems more promising than some of the alternatives.
0
 
PatHartmanConnect With a Mentor Commented:
That is the answer I suggested in your other thread.
0
 
slightlyoffAuthor Commented:
Sorry Pat, I misunderstood your answer.  Creating a local SQL Server Express database seemed to be more complicated than I needed it to be - so I wondered if it could be done all within Access without a linked table.

The reason I didn't want to link the table is I didn't want any risk of changing the source database (quickbooks).

Thank you both for your help - I'm going to give that a try.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.