Transfer Data from Excel to Access

[Vishesh78]

I have around 300,000 rows of data in Excel that I need to export to Access.
One way to do that is to loop through the data and add records one by one.
Another, manually open Access and use Get External Data option to import into Access. However, I want to automate this latter option from Excel...In Excel I want to write a function that takes excel range, access db and table as parameter and uses Access's Docmd.transferspreadsheet command...all this from Excel without having to open Access. I want to avoid having to loop record by record.
Still another method that I used with lesser no. of records (upto 60000 rows) is to create a sql query on excel range and insert into access at one go. It doesn't work with large no. of data though.

 

[Andrzejek]

...and the question is...?

If there IS a question, what have you tried so far?
And where are you stuck?

Have fun.

---- Andy

 

[Vishesh78]

This is what I do...manually open Access and use Get External Data option to import into Access. However, I want to automate this from Excel...In Excel I want to write a function that takes excel range, access db and table as parameter and uses Access's Docmd.transferspreadsheet command...all this from Excel without having to open Access. I want to avoid having to loop record by record.

I am not able to use Docmd.transferspreadsheet from Excel and don't know how/where to set database path.

 

[randy700]

Under Tools-->References.
Have you selected Microsoft Access 12.0 Object Library?

Randy

 

[Vishesh78]

Yes I did...but don't know how to put this all together.