Excel VBA - Access MDB database processing

[lb1]

I would like to regroup some processes and do the work from one Excel spreadsheet. The processes would do the following:
1 – read a text file and fill a table in an Access database
2 – run a query that will merge 2 tables and create a new table in the Access db.
3 – read the merged table and extract the data from the Access Db to the Excel spreadsheet
4 – Delete all the tables created and used in the Access Db.

I do have problem to do the following for each section (1,2,3, and 4):

1 – opens the text file and import the data in an Access table.
2 – What is the code to run an Access query from Excel VBA?
3 – I will know how to update the required cells in Excel, but what is the code to open and read data in an access table.
4 – I believe that if I know how to open and connect to the access db, a delete query will do.

I will appreciate if anybody has some pieces of code that could direct me.
Thanks.
Louis

 

[SkipVought]

lb1,

Since you are only using Access as a temporary data repository, is there any reason why you could not use some other adequate temporary data repository -- Like Excel?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lb1]

I usually do this, but this time I have to merge two differents tables (or text files). This is why I thought of the Access Db. Also I need to do some sorting on some fields. I do believe that I can do the sorting in Excel.

 

[SkipVought]

Import the text tables into 2 separate sheets.

Name each table data range -- I use something like tblNames, tblOrders

Save

On a separate sheet use Data/Get external data - ExcelFiles to set up a query on your 2 tables



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lb1]

Clever, but is there a way to create the query as you say and save this query in the VBA code?

 

[SkipVought]

Sure.

You can insert the query and then macro record the process of editing the query.

The code that is generated will have to be cleaned up, but it can be done pretty quick.

You will be able to identify the Connect string, the SQL string.

So it will reduce to this...

with [Query_from_Excel_Files].QueryTables(1)
        .Connection = sConnectString
        .CommandText = sQueryString
        .Refresh BackgroundQuery:=False
end with

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lb1]

Thanks - I will start to work on this