Unanswered: what's the best method for making a connection between MSSQL and Access
Can sombody tell me, what is the best (most save) and most fast way to get data from a MS SQL database in an ACCESS form or report
I've got to choose between creating an ACCESS project (ADP), creating an access database and work with SQL Pass Through-query or making an access database and work with a ADODB.Connection.
The easiest and most efficient method is to use an ADP database. This ensures that all the processing is done on the server-side, without transmitting raw data over your network. It is also easier to code, and to switch databases. The downside is that you have to make sure that your security is locked down, because someone can change database tables or procedures through the ADP interface. The best way to prevent this is to utilize SQL Server's security features, taking away all non-essential rights from the login that will be used for the ADP project. This is actually a plus, because SQL Server security is much better than Access security, which in my opinion has always been clunky.
Do you have to use access as the front-end or do you have a choice - if you have a choice, you might want to consider developing in another language like .Net or at least porting your applications from access to another language.
Since Access is the only tool I’m allowed to develop, VB6 or .NET are no alternative for me, although I know this would be better.
Reading the reaction of blindman and the article rnealejr mentioned it’s best I think to develop as an adp project. So I’m going to do this.