Wednesday, April 23, 2008

Query Access from Excel

Sub checkup_backup()
Dim cn As Object, rs As Object, Status As Range
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "c:\Shweta\EFT.mdb"
Set Status = ActiveSheet.Range("A2") 'SQL Variable
MySql = "Select * from Countrymaster"
'Status = Empty 'Clear SQL variable string

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection

Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets(1).Range(Cells(1, 1), Cells(myCnt, 2)).CopyFromRecordset rs
End If
End With
Set rs = Nothing: Set cn = Nothing
End Sub

1 comment:

  1. I have an Excel application from which I want to open an Access DB and run a query, and then copy the results of that query and paste it onto an Excel worksheet. I am using Excel 2003 and Access 2003.

    My workbook is WB1.xls
    The sheet is qry1
    The db is Trip 1.mdb
    The query is "query3"
    The field is "Market"

    I appreciate any help

    Thank you...