Fetch Data From MS-Access

[kashif.special2005]

Hi,

I have a student database in MS-Access, And I want to fetch some part of data from ms-access to excel.There is a macro in access where 20 to 25 queries in that macro, I want to hit macro first so macro will do all the calculation in ms-access, then at end final query will store result data in "Student_Final_Result" table, After that I want to fetch all data from "Student_Final_Result" to excel in Sheet("Student").range("a1"), But I am not sure how will I do this.....

Database Name Student_Data.mdb
Macro_Name Stu_Calc
Temprary_Table Student_Final_Result
Excel_Workbook Student Result
Result_Sheet Student
Paste_Range Sheet("Student").range("a1")

Please help me to resolve this problem.

Thanks & Regards,
Kashif.

 

[kashif.special2005]

Please help me to solve this problem............

 

[Trevor G]

look at this thread it will guide you.

The only extra thing is to get it to run the macro.

You can add something along the lines of
DoCmd.RunMacro "Macro Name goes here"


http://www.mrexcel.com/forum/showpost.php?p=2832452&postcount=6

 

[Norie]

Kashif

Kind of off topic, but what do these 25 queries actually do?

That sounds like a lot of queries just to get some calculations done and produce the results.

 

[kashif.special2005]

Hi,
Thanks for reply......

These 25 queries actually doing unnecessary data deleting, updating, applying calculation fields, data appending like wise and at finally at last final data appending in a temporary table............

Thanks,
Kashif.

 

[kashif.special2005]

Hi,

I am using below code for fetching the data from MS-Access to Excel, But I am not able to find solution that how can I run a macro in MS-Access through Excel VBA.

'Global Variable

Public db As DAO.Database
Public dbpath As String
Public rs As DAO.Recordset

'DB Connection Procedure

Public Sub connection()
On erro GoTo err:
dbpath = "C:\Users\Kashif\Desktop\Excel & Access Files\Access Database & File\Student_Data.mdb" 'Database path

Set db = OpenDatabase(dbpath, ture, True) 'Open Database
Exit Sub
err:
Select Case err.Number
Case 3024
MsgBox "Database not found. Please save the database at the location of current folder"
Application.Calculation = xlCalculationAutomatic
End
Case Else
End Select
End Sub

'Fetching the data from Access in Excel

Public Sub fetch_data()

Dim sql As String

Call connection 'Open Connection

'Here I want that after open connection first hit the macro "Stu_Calc", After that below code should run.

sql = "select * from Student_Final_Result" 'Fetching Query

Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
Sheets("Student").Cells(2, 1).CopyFromRecordset rs 'Pasting Data
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

Please help.

Thanks,
Kashif.