slightlyoff
asked on
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:
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!
The command I use to import the table (by macro) is:
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", FalseThis 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?