Using dbReadTable in_schema

I'm trying to use dbReadTable , however my tables sit under a schema inside the database.

For example using the code below I can connect:

  db_ANZSCO <- tbl(con, in_schema("BGVIEW" ,"ANZSCO"))

But when I try to use dbReadTable I get the following error;

  dbReadTable(con, "ANZSCO"))  

   Error: <SQL> 'SELECT * FROM "ANZSCO"'
     nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server      Driver][SQL Server]The SELECT permission was denied on the object 'ANZSCO', database 'BurningGlass', schema 'dbo'.

I understand the table is sitting under a schema but I have no idea how to access using dbReadTable, I did try to look into documentation but I have been so far unsuccessful.

Tks

Solution

Try the new-ish DBI::Id() function, that accepts the schema name and table name as separate arguments.

con <- DBI::dbConnect(drv = odbc::odbc(), dsn = "qqqq") # Replace `qqqq`.
a <- DBI::Id(
  schema  = "BGVIEW",
  table   = "ANZSCO"
)
ds <- DBI::dbReadTable(con, a)
DBI::dbDisconnect(con)

If that doesn't work, please paste the table definition in your question, and the code that creates the connection. You've tagged your question with [rodbc], but it looks likes you're using the newer & DBI-compliant odbc package.