Can not run a Access Query through excel macro

[MayFlowerNew]

Hi All,

I am a new to Excel, VB and using Access for excel through VBA.
I am trying to run a query in a VB macro and return the result to the excel sheet.
I dont get any compiler error but I dont get any result too..
please help..


Here is the code
Public Const DEFAULT_PATH As String = "C:\AccessDatabase"
Public Const DEFAULT_DB As String = "db1.mdb"

Public Sub Autpen()
On Error Resume Next
Dim rs As Recordset
Dim qy As QueryDef
Dim Row As Integer
Dim Row As Integer

db_name = DEFAULT_PATH & "\" & DEFAULT_DB

If (Len(db_name) > 0) Then
Set g_db = OpenDatabase(db_name)
Else
MsgBox "No DB open!"
End
End If

Set qy = g_db.CreateQueryDef("", "Select * from Table1")
Set rs = qy.OpenRecordset

Row = 1

With rs
Do While Not .EOF
Worksheets("Sheet1").Cells(Row, 1) = rs.Fields(0).Name
Worksheets("Sheet1").Cells(Row, 1) = rs.Fields(1).Name
Row = Row + 1
.MoveNext
End If
Loop
End With
rs.Close

ThisWorkbook.Save
g_db.Close
Set g_db = Nothing

End Sub

 

[PHV]

- First, comment out the On Error Resume Next instruction to see wich portion of code is faulty.
- I strongly recommend to use the Option Explicit instruction.
- Take a look at the Range.CopyFromRecordset method.
- You may also simply consider a QueryTable automatically refreshed on workbook open: menu Data -> External data ->

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MayFlowerNew]

Thanks PHP,

I commented out the On Error Resume code and found out that there was error in connecting to database. I had DAO 3.51 instead of 3.6. When I made necessary changes it worked fine.

Thanks a lot