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
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub