If your model is correctly specified and the appropriate conditions for your inference method are satisfied (e.g. i.i.d. Gaussian errors if you want to use a t-test), then you should be able to achieve your nominal type I error rate, regardless of n and regardless of $R^2$. (Though as a separate issue, a large sample size will bring down your Type II error rate by increasing power, so it may be worthwhile reducing your significance level $\alpha$ to bring down your Type I error rate too; the cost of an increased Type II error rate may be worth paying now you have more power to play with. If you were to do this, your p-value may no longer look quite so impressive!)
In other words: there's no need to be more suspicious of a significant result just because the $R^2$ is low, and it isn't true that "any variable" will be significant just because the sample size is large. If the variable does not actually influence your response variable once other variables are taken into account, then if we take the 5% level as significant, the variable will only have a 5% chance of being (incorrectly) deemed significant even if your sample size is in the trillions. But remember that's subject to the conditions I mentioned earlier. Moreover, a variable which only has a very weak relationship with the dependent variable (the true slope $\beta$ is close to, but not exactly, zero) is much more likely to be detected as statistically significant in a large sample because of the increased power. This is where the difference between "statistical significance" and "practical significance" is important. Looking at the confidence interval for the slope you may find the variable will only have a negligible impact on predictions, even if it's on the side of the confidence interval furthest from zero. This is a feature of large sample sizes, not a bug - the larger the sample size, the better you understand the relationships of your variables, even the hard-to-detect weak relationships.
On the other hand, having a high $R^2$ does not mean you are safe from detecting a spurious relationship that results in poor out-of-sample performance. A situation like omitted-variable bias can strike regardless of whether your $R^2$ is high or low: if you misspecify your model, and one of the variables you include in the model is correlated with an omitted variable (one that you may not even have measured) then its coefficient estimate will be biased. It might be that it should have no influence on your dependent variable (the true $\beta$ is zero) but you may find it appears as significantly different from zero. If its correlation with the omitted variable is very weak, then this spurious significance is unlikely to occur unless your sample size is quite large. But this isn't a reason to prefer smaller sample sizes, and there's nothing special to worry about in the context of a low $R^2$. A quick demonstration by simulation in R that you can find a spurious relationship even with high $R^2$:
require(MASS) # for multivariate normal simulation
set.seed(123)
n <- 10000
X <- mvrnorm(n=n, mu=c(10, 10), Sigma=matrix(c(1,0.9,0.9,1), nrow=2))
xomitted <- X[,1]
xspurious <- X[,2] # correlated with xomitted, rho=0.9
y <- 3*xomitted + rnorm(n=n, mean=0, sd=1) # true model with noise sd=1
ovb.lm <- lm(y ~ xspurious)
summary(ovb.lm) # xspurious should have coefficient 0 but is highly sig
The output from the regression shows a significant coefficient on xomitted even though which the true slope is zero. The high $R^2$ was no guarantor of a non-spurious relationship.
Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept) 2.90353 0.16600 17.49 <2e-16 ***
xspurious 2.71003 0.01652 164.00 <2e-16 ***
---
Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
Residual standard error: 1.653 on 9998 degrees of freedom
Multiple R-squared: 0.729, Adjusted R-squared: 0.729
F-statistic: 2.689e+04 on 1 and 9998 DF, p-value: < 2.2e-16
If you are dealing with an experimental situation where all relevant variables are measured or controlled and you may have clear theoretical grounds for the structure of your model, then this might all fade as a concern somewhat. In an experiment we may be able to hold unmeasured variables constant, or randomize them (e.g. allocations in a clinical trial) - this will eliminate the correlation between the omitted and observed variables. The problem can be more acute in observational data, where there can be a tangle of correlations between the things we can measure and - possibly more important - unobservable ones, and in fields like social sciences it may be impossible to justify a particular model specification a priori from theory (particularly things like which power a variable should appear to).
Finally, a more general statement on whether your model is "useless". Obviously with an $R^2$ below 1% you are not going to get good predictive performance. But if we are modelling a noisy process, or one with many factors but few we can measure, then good predictive performance is too much to hope for. It can still be useful to know that two variables aren't particularly related - in general we want the 95% confidence interval for our regression coefficients to be very narrow (indicating less uncertainty about the slope, for which purpose we desire a large sample size), and if that happens to be close to zero then we have learned the useful fact that we don't expect changes to that variable to have much influence on our response variable. But if the response variable is important to us (Frank Harrell's medical example is a good one, another might be the "marginal gains" theory in sport) then even ways to weakly influence it might be important to us. If your main concern is out-of-sample performance, then you should probably be paying close attention to the model specification.
