How to run MS Access Query from ASP?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: How to run MS Access Query from ASP?

  1. #1
    Hema Guest

    How to run MS Access Query from ASP?


    My MS Access database has some queries. How can I run it from ASP?

  2. #2
    JS Guest

    Re: How to run MS Access Query from ASP?


    'ADODB connection string
    Set Conn = Server.CreateObject("ADODB.Connection")
    'Open connection to Microsoft Access Database
    Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("_private/survey.mdb")
    'this is a relative path to your database

    'SQL Statement searching for a value in the database
    strsql = "select * from your_table"
    'creating the ADODB recordset variable
    set objrec = server.CreateObject("ADODB.recordset")
    'opening recordset, sql, and connection
    objrec.Open strsql,conn

    'setting variables if recordset exists
    while not objrec.eof
    response.write objrec("YOUR_ATTRIBUTE")
    objrec.Movenext
    wend


    "Hema" <hemakumar65@hotmail.com> wrote:
    >
    >My MS Access database has some queries. How can I run it from ASP?



  3. #3
    Nathan Guest

    Re: How to run MS Access Query from ASP?


    The quickest and easiest way to connect to a database that I've found is:
    <OBJECT id=db_con RUNAT=server PROGID=ADODB.connection > </OBJECT>
    <%
    if db_con.state = adStateClosed then
    db_Con.Open "dsn=MBAS_2001;"
    end if
    These are the first couple of lines in most all of my asp pages. I needed
    a sessionless website so it would not time out on people if they had a page
    open for an extended period of time. I'm sure someone will want to slap
    my hand for doing it this way and they will claim it is either wrong or it
    doesn't work. I haven't had any problems. The '<OBJECT>' stuff at the top
    is the same stuff you would find in your global.asa file. I did away with
    that and just wrote that line at the top of all my pages. This tests my
    database to see if there is a connection to it. If there is not, it establishes
    a connection. db_con is my database connection and 'adStateClosed' is a
    VB function to see if there is a connection. If the connection is closed
    then it opens a connection to my dsn. About running scripts from Access,
    you need this:

    Const adCmdStoredProc = &H0004

    It is a constant and is crucial to running scripts in Access. Also you will
    have to dim a variable as follows:

    Dim objCommand

    Set objCommand = Server.CreateObject ("ADODB.Command")

    This creates a command, obviously and after this you can do:

    objCommand.ActiveConnection = db_con
    objCommand.CommandText = "delete_board_data"
    objCommand.CommandType = adCmdStoredProc
    objCommand.Execute

    Again db_con is my "dsn variable". "delete_board_data" is my query in Access.
    "adCmdStoredProc" is the constant you gave earlier. Then execute you query.
    So a clean example would be:

    <OBJECT id=db_con RUNAT=server PROGID=ADODB.connection > </OBJECT>
    <%
    if db_con.state = adStateClosed then
    db_Con.Open "dsn=MBAS_2001;"
    end if

    Const adCmdStoredProc = &H0004

    Dim objCommand

    Set objCommand = Server.CreateObject ("ADODB.Command")

    objCommand.ActiveConnection = db_con
    objCommand.CommandText = "delete_board_data"
    objCommand.CommandType = adCmdStoredProc
    objCommand.Execute
    %>

    In your case, this could still apply. Make sure you have the constant, the
    objCommand(or whatever you want to name it) and change 'db_con' to your variable
    name for your dsn.

    "Hema" <hemakumar65@hotmail.com> wrote:
    >
    >My MS Access database has some queries. How can I run it from ASP?



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center