MS ACCESS connection and perform basic CRUD in R

****************************

Author: DataAnayltic

Connecting and doing basic CRUD activities with MS ACCESS using R

****************************

#Watch the video on YouTube to see the explanation

YouTube Video link

https://youtu.be/8xfhg3gJaL4

library(odbc)      # For ODBC connection
library(DBI)       # For database commands
library(tibble)    # For creating our sample dataset
library(flextable) # For displaying the data on screen

You ACCESS database file

# You must have an existing ms access database.
mydbFile <- "D:/temp/MyMSAccessDB.accdb"

# Create a sample dataset
df <- tibble::tribble(~ GENDER , ~AGE
                      , 'Male' , 20
                      , 'Female', 30)
flextable(df)

GENDER	AGE
Male	20
Female	30

# If you are unsure about the odbc drivers then the following command will help
odbc::odbcListDrivers()

con <- odbc::dbConnect(odbc()
                       , Driver = "Microsoft Access Driver (*.mdb, *.accdb)" 
                       , Dbq = mydbFile )

Now perform the basic CRUD activities with your database.

Always add the following line in your script

options(odbc.batch_rows = 1)

YouTube Video link https://youtu.be/8xfhg3gJaL4

# CRUD ----

## Simple CRUD ----
###  CREATE ----
# Important to add this line
options(odbc.batch_rows = 1)

DBI::dbWriteTable(con, 'df', df, overwrite = TRUE)
DBI::dbExecute(con, "insert into df (GENDER , AGE)  values ('Other', 50)")

FALSE [1] 1

### READ ----
mydata <- DBI::dbReadTable(con, 'df')
flextable(mydata)

GENDER	AGE
Male	20
Female	30
Other	50

mydata2 <- DBI::dbGetQuery(con, "Select * from df")
flextable(mydata2)

GENDER	AGE
Male	20
Female	30
Other	50

mydata3 <- DBI::dbGetQuery(con, "Select * from df where AGE = 20")
flextable(mydata3)

GENDER	AGE
Male	20

mydata4 <- DBI::dbGetQuery(con, "Select * from df where AGE = 20 and GENDER = 'Male'")
flextable(mydata4)

GENDER	AGE
Male	20

### Update ----
# How to update an existing record
sql <- "Update df set AGE = 120 where GENDER = 'Other'"
sql

FALSE [1] "Update df set AGE = 120 where GENDER = 'Other'"

DBI::dbExecute(con, sql)

FALSE [1] 1

mydata5 <- DBI::dbReadTable(con, 'df')
flextable(mydata5)

GENDER	AGE
Male	20
Female	30
Other	120

###  Delete ----
# data ( just removes the data but not the table structure)
DBI::dbExecute(con, "Delete from df where AGE = 20")

FALSE [1] 1

mydata6 <- DBI::dbReadTable(con, 'df')
flextable(mydata6)

GENDER	AGE
Female	30
Other	120

# Delete all records (but not the table structure)
DBI::dbExecute(con, "Delete *  from df")

FALSE [1] 2

mydata7 <- DBI::dbReadTable(con, 'df')
mydata7

# DROP Tables ----
# Drop table completely (data as well as the table structure)
DBI::dbExecute(con, "drop table df")

FALSE [1] 0

# Disconnect from the connection ----
DBI::dbDisconnect(con)

# In the next video we will talk about
# safe and parameterised queries
# which are safe against SQL injections.
# when you use your data over WWW or networks.

Watch this script in action on YouTube YouTube Video link https://youtu.be/8xfhg3gJaL4