Import data from multiple access databases (URGENT help needed)

[src_src]

Hello There,

I have a query -- I have multiple access files in a partticilar folder and looking for a way to get some data in an excel file...
I tried excel import query and created a .dqy file but it seems to work with just one file

XLODBC
1
DBQ=C:\Documents and Settings\sc20293\My Documents\Dfma\data\samples\machined part.mdb;DefaultDir=C:\Documents and Settings\sc20293\My Documents\Dfma\data\samples;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;
SELECT Analysis.AnalysisName, Entry.ParentID, Entry.Name, Entry.CurrentMaterialCost, Entry.CurrentSetUpCost, Entry.CurrentProcessCost, Entry.CurrentPiecePartCost, Entry.CurrentToolingCost, Entry.CurrentTotalCost FROM Analysis Analysis, Entry Entry WHERE Analysis.ID = Entry.ID AND ((Entry.ParentID=0))

AnalysisName ParentID Name CurrentMaterialCost CurrentSetUpCost CurrentProcessCost CurrentPiecePartCost CurrentToolingCost CurrentTotalCost
Out put

But I am looking for a code which can extract data from all files in a folder and push in an excel file....

I am open for any VBA code also if that seems to work.....

Thank you In Advance....


SRC

 

[src_src]

Any help you excel gurus.....m still stuck ...

 

[Trevor G]

Are you wanting Queries from the database or tables?

Please list there names and also the database names.

 

[src_src]

Hello there I am attached sample databases with seperate query, I am wondering if I can make a VB code which can extract similar excel output for all mdb files inside a folder...

Any help is hugely apprecited and thank you for stopping by.....

Link

 

[src_src]

Anybody any insight to the issue....plz plz

 

[Trevor G]

The following code will open a single database in a folder and then look to get the query and import to a new workbook. Please note the Instructions to Add the References to MS Access and MS DAO, Use Alt + F11 and then go to Tools Menu > References > Search down the list.

Adapt this to get the first query across from the first database then it will be a case of setting a loop to the folder.

Note you will have to add each field name to match what goes into the spreadsheet

Sub DBOpen()
'Add the reference for MS Access and Microsoft DAO
'GoTo Tools References search down the list for Microsoft Access XX.Object Library
'Tick the box and do the same for Microsoft DAO XX.Object Library
Dim db As DAO.Database
Dim ws As Excel.Application
Dim rst As DAO.Recordset
Dim lngCount As Long
Dim i As Long
Set db = OpenDatabase("M:\Access Files\Test ME Today.mdb")
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Add
.Visible = True
End With
Set rst = db.OpenRecordset("qryEmployees")
lngCount = 1
ws.Sheets("Sheet1").Select

For i = 0 To rst.Fields.Count - 1
ws.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
Next
Do Until rst.EOF
With ws
.Cells(lngCount + 1, 1).Value = rst!ID
.Cells(lngCount + 1, 2).Value = rst!EmployeeName
.Cells(lngCount + 1, 3).Value = rst!AreaOfWork
.Cells(lngCount + 1, 4).Value = rst!PhoneNumber
.Cells(lngCount + 1, 5).Value = rst!EmployeeImage
.Cells(lngCount + 1, 6).Value = rst!Salary
.Cells(lngCount + 1, 7).Value = rst!Comments
.Cells(lngCount + 1, 8).Value = rst!Training
.Cells(lngCount + 1, 9).Value = rst!emailaddress
End With
lngCount = lngCount + 1
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub