#Accessing databases in Windows - Excel and Access

# You can, of course, do all the things we learned about how to access
# remote databases in unix.
# But, you can also access local databases on your machine.

# This uses the ODBC interface, which eventually should also work
# on PostgreSQL and Oracle, so that accessng all databases should
# use the scheme below...

library(RODBC)

#First, Excel

#connect:
con=odbcConnectExcel("c:/Book1.xls")

#list tables:
sqlTables(con)

#get a table
a=sqlFetch(con,"Sheet1")

a

#query
a=sqlQuery(con,"select * from [Sheet1$] where F2 < 30")

a

# But sadly, you can not write to an excel sheet.

#close connection
odbcClose(con)

#Access databases
con=odbcConnectAccess("c:/test.mdb")

#list tables
sqlTables(con)

data(ToothGrowth)

#write table
sqlSave(con,ToothGrowth,tablename="hamster")

sqlTables(con)

#read table
a=sqlFetch(con,"hamster")

a

#query
sqlQuery(con,"select * from hamster where dose > 0.5")

#delete table
sqlDrop(con,"hamster")

#close connection
sqlTables(con)