The Bottom Line
The sample correlation coefficient needed to reject the hypothesis that the true (Pearson) correlation coefficient is zero becomes small quite fast as the sample size increases. So, in general, no, you cannot simultaneously have a large (in magnitude) correlation coefficient and a simultaneously large $p$-value.
The Top Line (Details)
The test used for the Pearson correlation coefficient in the $R$ function cor.test
is a very slightly modified version of the method I discuss below.
Suppose $(X_1,Y_1), (X_2,Y_2),\ldots,(X_n,Y_n)$ are iid bivariate normal random vectors with correlation $\rho$. We want to test the null hypothesis that $\rho = 0$ versus $\rho \neq 0$. Let $r$ be the sample correlation coefficient. Using standard linear-regression theory, it is not hard to show that the test statistic,
$$
T = \frac{r \sqrt{n-2}}{\sqrt{(1-r^2)}}
$$
has a $t_{n-2}$ distribution under the null hypothesis. For large $n$, the $t_{n-2}$ distribution approaches the standard normal. Hence $T^2$ is approximately chi-squared distributed with one degree of freedom. (Under the assumptions we've made, $T^2 \sim F_{1,n-2}$ in actuality, but the $\chi^2_1$ approximation makes clearer what is going on, I think.)
So,
$$
\mathbb P\left(\frac{r^2}{1-r^2} (n-2) \geq q_{1-\alpha} \right) \approx \alpha \>,
$$
where $q_{1-\alpha}$ is the $(1-\alpha)$ quantile of a chi-squared distribution with one degree of freedom.
Now, note that $r^2/(1-r^2)$ is increasing as $r^2$ increases. Rearranging the quantity in the probability statement, we have that for all
$$
|r| \geq \frac{1}{\sqrt{1+(n-2)/q_{1-\alpha}}}
$$
we'll get a rejection of the null hypothesis at level $\alpha$. Clearly the right-hand side decreases with $n$.
A plot
Here is a plot of the rejection region of $|r|$ as a function of the sample size. So, for example, when the sample size exceeds 100, the (absolute) correlation need only be about 0.2 to reject the null at the $\alpha = 0.05$ level.
A simulation
We can do a simple simulation to generate a pair of zero-mean vectors with an exact correlation coefficient. Below is the code. From this we can look at the output of cor.test
.
k <- 100
n <- 4*k
# Correlation that gives an approximate p-value of 0.05
# Change 0.05 to some other desired p-value to get a different curve
pval <- 0.05
qval <- qchisq(pval,1,lower.tail=F)
rho <- 1/sqrt(1+(n-2)/qval)
# Zero-mean orthogonal basis vectors
b1 <- rep(c(1,-1),n/2)
b2 <- rep(c(1,1,-1,-1),n/4)
# Construct x and y vectors with mean zero and an empirical
# correlation of *exactly* rho
x <- b1
y <- rho * b1 + sqrt(1-rho^2) * b2
# Do test
ctst <- cor.test(x,y)
As requested in the comments, here is the code to reproduce the plot, which can be run immediately following the code above (and uses some of the variables defined there).
png("cortest.png", height=600, width=600)
m <- 3:1000
yy <- 1/sqrt(1+(m-2)/qval)
plot(m, yy, type="l", lwd=3, ylim=c(0,1),
xlab="sample size", ylab="correlation")
polygon( c(m[1],m,rev(m)[1]), c(1,yy,1), col="lightblue2", border=NA)
lines(m,yy,lwd=2)
text(500, 0.5, "p < 0.05", cex=1.5 )
dev.off()