The Issue

When trying to write to a table with a non-default schema, dbWriteTable in the package DBI, writes to default.non-default.tablename rather than writing to non-default.tablename. I know that non-default.tablename exists because it's showing up in my SSMS database.

Reproducible Example/What I've Tried

Create this table in SQL Server with a non-default schema 'guest'. I am placing it in a database called 'SAM':

SQL:

CREATE TABLE guest.MikeTestTable(
[a] [float] NULL,
[b] [float] NULL,
[c] [varchar](255) NULL)

R:

# Create a df to insert into guest.MikeTestTable
df <- data.frame(a = c(10, 20, 30),
                 b = c(20, 40, 60),
                 c = c("oneT", "twoT", "threeT"))

# Create a connection:
con <- DBI::dbConnect(odbc::odbc(),
             .connection_string = "Driver={SQL Server};
                                   server=localhost;
                                   database=SAM;
                                   trustedConnection=true;")

# Try to write contents of df to the table using `dbWriteTable`
DBI::dbWriteTable(conn = con,
                  name = "guest.MikeTestTable",
                  value = df,
                  append = TRUE)

# Create a query to read the data from `"guest.MikeTestTable"`:
q <- "SELECT [a]
  ,[b]
  ,[c]
  FROM guest.MikeTestTable"

# Read the table into R to show that nothing actually got written to the 
#   table but that it recognizes `guest.MikeTestTable` does exist:
DBI::dbGetQuery(con, q)

[1] a b c
<0 rows> (or 0-length row.names)

I thought this was a weird result so I opened up my SSMS and lo and behold, the table dbo.guest.MikeTestTable was created. Any help would be much appreciated.

Please see here for the answer