Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Rotterdam
    Posts
    17

    Question 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.
    Peter Dorsman

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    blindman

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    This article will answer your question:

    article

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    At least look at VB6 and see if it's not too challenging. I just can't see a serious business solution being developed in something like Access, or even it's "new and improved" ADP offspring!

  6. #6
    Join Date
    Oct 2003
    Location
    Rotterdam
    Posts
    17
    Thank you all for your reactions.

    Since Access is the only tool Im 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 its best I think to develop as an adp project. So Im going to do this.

    Thanks for all and

    MERRY Christmas to you all

    Peter1970
    Last edited by peter1970; 12-22-03 at 04:15.
    Peter Dorsman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •