Import Text File to Access Table using Excel VBA

Hi

I have an access database, where I import some text files (by saving excel worksheet as txt) to Predefined tables in Access. Some queries are then run, which use these tables. I need to automate this process.

So what I basically need to do is, use an excel macro, which makes the Access Db to import the text file to a table and then run the access queries.

I am completely new to Access database and have no idea where to begin. I am not aware if exporting excel worksheets instead of text files will work as I have no backend knowledge of the database. I have basic knowledge of Excel VBA.

Any help will be greatly appreciated!!

TIA

bumping!! Please help!

Still no reply!! Somebody please help!!

instead of exporting excel worksheet as text files you can directly upload data from excel to access

Hey mohanvijay

Thanks for your reply!

The access database I am using is controlled by another team and accordingly the queries/table use import text file and cannot be edited. Hence, I must export a text file only.

In any case, can you please let me know how to export excel sheet to access?

I found this code online: if you can explain the same, would be really grateful!

Please Login or Register  to view this content.

are you able to open that access database?

if yes we can create the simple VBA to add data from excel to access

Yes I can open the Access database. I have read only access to it.

Currently, I open it, for the already created tables, import data as text and then run the queries.

I think if you have only a read only access you can not do ot via vba

for import excel sheet in access see "External Data" tab