Solved

Access Data Import using SQL from another Database Table

Posted on 2013-12-10
5
629 Views
Last Modified: 2013-12-12
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!
0
Comment
Question by:slightlyoff
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
Comment Utility
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
 
LVL 1

Author Comment

by:slightlyoff
Comment Utility
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
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
That is the answer I suggested in your other thread.
0
 
LVL 1

Author Comment

by:slightlyoff
Comment Utility
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now