TransferDatabase Access to Access with WHERE condition

[ria.arora]

Dear All,

I'm transfering data from one Access Database to another database using TransferDatabase method using below code:

[COLOR=black][FONT=Verdana]   Set oDB = Application.DBEngine.OpenDatabase(strNNASourceDatabasePathNName, False, False)[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]   Set objApp = New Access.Application[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   objApp.OpenCurrentDatabase strNNASourceDatabasePathNName, False[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]   'Export the needed tables and queries[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   'Parameters[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]   objApp.DoCmd.TransferDatabase Access.AcDataTransferType.acExport, _[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]                                       "Microsoft Access", _[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]                                       strNNADestDatabasePathNName, _[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]                                       Access.AcObjectType.acTable, "NNA data", "tbl_NNA"[/COLOR][/FONT]

Both the databases are password protected.

Above code is working fine. Only issue is I want to import only selected data e.g. last 12 months data only instead of full set of data. Currently I'm importing full set of data and after that I'm deleting the unwanted data, which is not efficient. Because original table contains 4 years data and approx 500K records. For current year I have only 100K records.

Is there any option in the TransferDatabase method which enables to import selected data? I want import all the records before certain date or between date range e.g. WHERE Right([BookDate], 4) >= Year(Now) - 1

Thanks for the help in advance.

 

[boblarson]

There really isn't a way to do this (at least that I know about) short of doing some linking behind the scenes and then running a query on the linked table and then deleting the link.

 

[ria.arora]

You mean that is the only solution we have.

Not sure how much time that will take to insert 100K - 150K records.

Any other solution?

 

[boblarson]

You mean that is the only solution we have.

You asked...

Not sure how much time that will take to insert 100K - 150K records.

If you use well formed SQL which means no Access functions or anything else not applicable to SQL Server then you can rest assured that Access will actually send optimized requests. It may not be as bad as you think. The only way to know is to test, test, test.

Any other solution?

If there were any I could think of, I would have stated them.

 

[ria.arora]

Thanks. I'll try this and let you know.

 

[ria.arora]

Hi Bob,

Thanks. This is working perfectly. Any idea about conditional formatting?

http://www.access-programmers.co.uk/forums/showthread.php?t=232706