3 Open a Recordset
1 Create a Connection String to define the Data Set Name
2 Open a Connection
- ADO
ActiveX Data Objects - Designed as "the" Microsoft standard for data access, it is Microsoft's newest high-level programming interface for data objects. First used with Internet Information Server, ADO is a set of COM objects that provides an interface to OLE DB. The three primary objects are Connection, Command and Recordset. The Connection object establishes a connection with a particular database management system (DBMS) or other data source. It can also send a query to the database. The Command object is an alternate way of sending a query to the database, and the Recordset object contains the resulting answer, which is a group of records.
ADO provides several object libraries for processing data. ADODB (ADO DataBase) is used to establish connections and retrieve recordsets. ADOX (ADO eXtension for Data Definition Language and Security) is used to create a database schema and create tables as well as deal with security issues. JRO (Jet and Replication Objects) is used to replicate and compact Microsoft Jet databases.
ADO is designed to eventually replace Data Access Objects (DAO) and Remote Data Objects (RDO). Unlike RDO and DAO, which are designed only for accessing relational databases, ADO is more general and can be used to access all sorts of different types of data, including web pages, spreadsheets, and other types of documents.
Together with OLE DB and ODBC, ADO is one of the main components of Microsoft's Universal Data Access (UDA) specification, which is designed to provide a consistent way of accessing data regardless of how the data are structured.
ADO.NET is the .NET version of ADO, which is substantially different from ADO. It supports XML documents and relies on .NET Data Providers as an interface layer between the application and the databases.
ADO and ODBC InterfacesThese are the various ways applications can access data sources using ADO, ODBC and other Microsoft interfaces.
- ODBC
Open DataBase Connectivity - A widely accepted application programming interface (API) for database access developed by the SQL Access group in 1992. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language to access databases on a network.
ODBC is designed for maximum interoperability—that is, the ability of a single application to access any data from any application, regardless of which database management system (DBMS) is handling the data, with the same source code. Database applications call functions in the ODBC interface insert a middle layer between an application and the DBMS, which are implemented in database-specific modules called database drivers, which are dynamic-link libraries (DLLs) on Windows and shared objects on UNIX.
The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them. Since version 2.0, the standard supports SAG SQL.
The use of drivers isolates applications from database-specific calls in the same way that printer drivers isolate word processing programs from printer-specific commands. Because drivers are loaded at run time, a user only has to add a new driver to access a new DBMS; it is not necessary to recompile or relink the application. Each database program (such as Microsoft Access or dBase) or database management system (such as SQL Server) requires a different driver.
For client/server database systems such as Oracle and SQL Server, the ODBC driver provides links to their database engines to access the database. For desktop database systems such as dBASE and FoxPro, the ODBC drivers actually manipulate the data. ODBC supports SQL and non-SQL databases. Although the application always uses SQL to communicate with ODBC, ODBC will communicate with non-SQL databases in its native language.
Many misconceptions about ODBC exist in the computing world. To the end user, it is an icon in the Microsoft® Windows® Control Panel. To the application programmer, it is a library containing data access routines. To many others, it is the answer to all database access problems ever imagined.
First and foremost, ODBC is a specification for a database API. This API is independent of any one DBMS or operating system; although some manuals use C, the ODBC API is language-independent. For example, instead of including code for every possible database, a program could use ODBC to connect to whichever database your system is using. The ODBC API is based on the CLI specifications from X/Open and ISO/IEC. ODBC 3.x fully implements both of these specifications—earlier versions of ODBC were based on preliminary versions of these specifications but did not fully implement them—and adds features commonly needed by developers of screen-based database applications, such as scrollable cursors.
The functions in the ODBC API are implemented by developers of DBMS-specific drivers. Applications call the functions in these drivers to access data in a DBMS-independent manner. A Driver Manager manages communication between applications and drivers.
Although Microsoft provides a driver manager for computers running Microsoft Windows® 95 and later, has written several ODBC drivers, and calls ODBC functions from some of its applications, anyone can write ODBC applications and drivers. In fact, the vast majority of ODBC applications and drivers available today are written by programmers at companies other than Microsoft. Furthermore, ODBC drivers and applications exist on the Macintosh® and a variety of UNIX platforms.
To help application and driver developers, Microsoft offers an ODBC Software Development Kit (SDK) for computers running Windows 95 and later that provides the driver manager, installer DLL, test tools, and sample applications. Microsoft has teamed with Visigenic Software to port these SDKs to the Macintosh and a variety of UNIX platforms.
It is important to understand that ODBC is designed to expose database capabilities, not supplement them. Thus, application writers should not expect that using ODBC will suddenly transform a simple database into a fully featured relational database engine. Nor are driver writers expected to implement functionality not found in the underlying database. An exception to this is that developers who write drivers that directly access file data (such as data in an Xbase file) are required to write a database engine that supports at least minimal SQL functionality. Another exception is that the ODBC component of the Microsoft® Data Access Components (MDAC) SDK provides a cursor library that simulates scrollable cursors for drivers that implement a certain level of functionality.
Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.
- SQL
Structured Query Language (SQL), pronounced as either "sequel" or "SQL", is a standard interactive special-purpose, nonprocedural database access programming language that provides an interface for accessing, querying, updating, and managing data by sending queries to a relational, ODBC, DRDA, or non-relational compliant database systems. Some superset of ANSI SQL is built into every RDBMS. Although SQL is a de facto standard, as well as an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. SQL supports the definition, manipulation, and control of data in RDBMSs.
It was developed by IBM in the 1974 and 1975 for use in System R and was called SEQUEL for "Structured English QUEry Language." Oracle Corporation first introduced SQL as a commercial database system in 1979. Since then, it has undergone a number of changes, with a lot of influence from Oracle Corporation. SQL originated on mainframes and minicomputers, and is now popular on PCs.
Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. There is also a programming interface. Most industrial-strength and many smaller database applications can be addressed using SQL. Each specific application will have its own slightly different version of SQL implementing features unique to that application, but all SQL-capable databases support a common subset of SQL. It is compatible with HTML and XML.
An example of an SQL statement is:
SELECT name,email FROM people_table WHERE country='uk'SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Programming extensions to SQL have turned it into a full-blown database programming language.
Microsoft Access 2000.
All ASP scripts are server-side. You can create your ASP scripts on a client computer, but eventually, you have to upload the scripts (usually through FTP -- "File Transfer Protocol") to a Web server running ASP. An example of a client-side script is JavaScript which can display data based on the Operating System (OS), browser, and installed plugins of a client computer.