Click here to Skip to main content
11,641,971 members (60,121 online)
Click here to Skip to main content

Accessing Microsoft Access databases in ASP using ADO

, 26 Apr 2000 CPOL 392K 4.6K 61
Rate this:
Please Sign up or sign in to vote.
A simple introduction to using Access .mdb databases in your ASP pages
  • Download source files - 29 Kb
  • Introduction

    Windows DNA provides a means to provide your user interface, business logic and data sources as separate services working together in harmony over a distributed environment. The browser has become an extremely powerful, yet simple method of providing the user interface, since it handles the network considerations and allows you to create rich user interfaces through simple scripting, HTML and style sheets.

    Your database considerations can be taken care of simply through the use of SQLServer or the Microsoft Jet Engine, and your business logic - the guts of your application that processes the data from the database and sends it to the browser - can be simple ASP pages (enhanced with ActiveX controls if the fancy takes you).

    Once you have the basics of ASP, HTML and VBScript the business logic and user interface are taken care of quickly and simply - but how do you use ASP to access your database and hence complete your 3-tier application? Read on...

    Simple database Access using ADO and ASP

    For this example we'll use Access .mdb databases - but we could just as easily use SQLServer by changing a single line (and of course, configuring the databases correctly). We'll be assuming your application is ASP based running on Microsoft's IIS Webserver.

    We use ADO since it is portable, widespread, and very, very simple.

    The Connection

    To access a database we first need to open a connection to it, which involves creating an ADO Connection object. We then specify the connection string and call the Connection object's Open method.

    To open an Access database our string would look like the following:

    Dim ConnectionString
    ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
                       "DBQ=C:\MyDatabases\database.mdb;DefaultDir=;UID=;PWD=;"

    where the database we are concerned with is located at C:\MyDatabases\database.mdb, and has no username or password requirements. If we wanted to use a different database driver (such as SQLServer) then we simply provide a different connection string.

    To create the ADO Connection object simply Dim a variable and get the server to do the work.

    Dim Connection
    Set Connection = Server.CreateObject("ADODB.Connection")

    Then to open the database we (optionally) set some of the properties of the Connection and call Open

    Connection.ConnectionTimeout = 30
    Connection.CommandTimeout = 80
    Connection.Open ConnectionString
    

    Check for errors and if everything is OK then we are on our way.

    The Records

    Next we probably want to access some records in the database. This is achieved via the ADO RecordSet object. Using this objects Open method we can pass in any SQL string that our database driver supports and receive back a set of records (assuming your are SELECTing records, and not DELETEing).

    ' Create a RecordSet Object
    Dim rs
    set rs = Server.CreateObject("ADODB.RecordSet")
    
    ' Retrieve the records
    rs.Open "SELECT * FROM MyTable", Connection, adOpenForwardOnly, adLockOptimistic
    

    adOpenForwardOnly is defined as 0 and specifies that we only wish to traverse the records from first to last. adLockOptimistic is defined as 3 and allows records to be modified.

    If there were no errors we now have access to all records in the table "MyTable" in our database.

    The final step is doing something with this information. We'll simply list it.

    ' This will list all Column headings in the table
    Dim item
    For each item in rs.Fields
    	Response.Write item.Name & "<br>"
    next
    			
    ' This will list each field in each record
    while not rs.EOF
    		
    	For each item in rs.Fields
    		Response.Write item.Value & "<br>"
    	next
    		
    	rs.MoveNext
    wend
    	
    End Sub
    

    If we know the field names of the records we can access them using rs("field1") where field1 is the name of a field in the table.

    Always remember to close your recordsets and Connections and free any resources associated with them

    rs.Close
    set rs = nothing
    
    Connection.Close
    Set Connection = nothing
    

    Conclusion

    This has been an extremely simple demonstration without serious error checking or even legible formatting of the output, but it's a base to start with.

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Share

    About the Author

    Chris Maunder
    Founder CodeProject
    Canada Canada
    Chris is the Co-founder, Administrator, Architect, Chief Editor and Shameless Hack who wrote and runs The Code Project. He's been programming since 1988 while pretending to be, in various guises, an astrophysicist, mathematician, physicist, hydrologist, geomorphologist, defence intelligence researcher and then, when all that got a bit rough on the nerves, a web developer. He is a Microsoft Visual C++ MVP both globally and for Canada locally.

    His programming experience includes C/C++, C#, SQL, MFC, ASP, ASP.NET, and far, far too much FORTRAN. He has worked on PocketPCs, AIX mainframes, Sun workstations, and a CRAY YMP C90 behemoth but finds notebooks take up less desk space.

    He dodges, he weaves, and he never gets enough sleep. He is kind to small animals.

    Chris was born and bred in Australia but splits his time between Toronto and Melbourne, depending on the weather. For relaxation he is into road cycling, snowboarding, rock climbing, and storm chasing.

    You may also be interested in...

    Comments and Discussions

     
    Questionerror Pin
    Panzer204-Dec-12 4:36
    memberPanzer204-Dec-12 4:36 
    GeneralMy vote of 3 Pin
    Member 427879126-Jan-11 17:34
    memberMember 427879126-Jan-11 17:34 
    Generalplease be clear Pin
    dogan_sezgin2-Jun-06 5:59
    memberdogan_sezgin2-Jun-06 5:59 
    GeneralRe: please be clear Pin
    Nabeel Anwer11-Mar-07 23:46
    memberNabeel Anwer11-Mar-07 23:46 
    QuestionTwo systems with one database! Pin
    Petrax24-May-06 23:00
    memberPetrax24-May-06 23:00 
    GeneralWorked! first time!! How do I arrange the data Pin
    Skippy_melb27-Feb-06 18:34
    memberSkippy_melb27-Feb-06 18:34 
    QuestionError?? Pin
    Anonymous26-Oct-05 13:04
    sussAnonymous26-Oct-05 13:04 
    QuestionPlease give me a way for display pictures which store in DB Access 2003 by &quot;OLE object&quot;? Pin
    tuyenhnp25-Sep-05 16:47
    membertuyenhnp25-Sep-05 16:47 
    Questionhow can i access Oracle DBMS using asp Pin
    Ali Idrees Bhuttah21-Sep-05 19:20
    sussAli Idrees Bhuttah21-Sep-05 19:20 
    GeneralAccess Tutorials Pin
    Batchwood13-Jul-05 7:51
    memberBatchwood13-Jul-05 7:51 
    GeneralUsing Ms-Access in .net Pin
    eyalgr120-Nov-04 3:28
    susseyalgr120-Nov-04 3:28 
    QuestionHow I detect connection to MS Access file error? Pin
    ATC7-Sep-04 10:42
    memberATC7-Sep-04 10:42 
    Generalurgent Pin
    pavancode21-Jul-04 22:31
    memberpavancode21-Jul-04 22:31 
    Generalcan't access database Pin
    JDG3-Mar-03 20:20
    memberJDG3-Mar-03 20:20 
    GeneralRe: can't access database Pin
    verma_aditya22-Feb-04 22:18
    memberverma_aditya22-Feb-04 22:18 
    GeneralRe: can't access database Pin
    sanjaygarg18-Aug-04 1:14
    membersanjaygarg18-Aug-04 1:14 
    GeneralRecord set is empty Pin
    msokol3-Dec-02 4:37
    membermsokol3-Dec-02 4:37 
    GeneralAccess db convertion fail Pin
    Anonymous11-Jul-02 18:30
    sussAnonymous11-Jul-02 18:30 
    GeneralView Jpg/ Gif stored in my database Pin
    2IT6-May-02 10:31
    member2IT6-May-02 10:31 
    GeneralRe: View Jpg/ Gif stored in my database Pin
    Anonymous4-Aug-02 16:37
    sussAnonymous4-Aug-02 16:37 
    GeneralRemote Data Service,help! Pin
    Jz17-Mar-02 7:22
    memberJz17-Mar-02 7:22 
    QuestionHow to Connect from ASP to MS-Access(Database) Pin
    Mann3-Dec-01 17:31
    memberMann3-Dec-01 17:31 
    GeneralAccess Macros Pin
    hhayes18-Sep-01 3:41
    memberhhayes18-Sep-01 3:41 
    GeneralRe: Access Macros Pin
    Matt Gullett6-May-02 11:10
    memberMatt Gullett6-May-02 11:10 
    GeneralIIS application in Visual Basic 6.0 Pin
    Selcuk Yazar5-Sep-01 6:57
    memberSelcuk Yazar5-Sep-01 6:57 
    GeneralSingle Qoutes Pin
    Hugo13-Jul-01 1:26
    memberHugo13-Jul-01 1:26 
    GeneralRe: Single Qoutes Pin
    bluesky8-Nov-01 14:07
    memberbluesky8-Nov-01 14:07 
    GeneralOLE Through Linux Pin
    Alex K.13-Apr-01 18:25
    memberAlex K.13-Apr-01 18:25 
    GeneralRe: OLE Through Linux Pin
    svante_ahmad8-Sep-02 23:08
    susssvante_ahmad8-Sep-02 23:08 
    QuestionAccessing MS Access through a firewall using ASP? Pin
    Anonymous24-Jan-01 3:47
    memberAnonymous24-Jan-01 3:47 
    AnswerRe: Accessing MS Access through a firewall using ASP? Pin
    Anonymous21-Jul-01 15:17
    memberAnonymous21-Jul-01 15:17 
    GeneralADO and ASP/IIS Pin
    Dennis16-Aug-00 3:06
    sussDennis16-Aug-00 3:06 
    GeneralRe: ADO and ASP/IIS Pin
    blueksky8-Nov-01 14:11
    memberblueksky8-Nov-01 14:11 
    GeneralSQL Search syntax Pin
    David Gallagher15-Jun-00 11:49
    sussDavid Gallagher15-Jun-00 11:49 
    GeneralRe: SQL Search syntax Pin
    Saul Toohey24-Apr-03 5:36
    sussSaul Toohey24-Apr-03 5:36 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

    | Advertise | Privacy | Terms of Use | Mobile
    Web01 | 2.8.150731.1 | Last Updated 27 Apr 2000
    Article Copyright 2000 by Chris Maunder
    Everything else Copyright © CodeProject, 1999-2015
    Layout: fixed | fluid