One best way is to use the Microsoft data access object. Make your forms in vb 6.0 and make sure the display is attractive. Design a database and make sure you put all necessary tables. Select the form you want to connect to the database, lets say the form have three fields namely: name, country and telephone. Uploading and Downloading BLOBs to Microsoft Access. There are many code snippets on the web for uploading and downloading BLOBs to SQL Server, but I could not find anything for Microsoft Access. Microsoft Access stores BLOB data in the a field with an OLE Object data type (if you are creating the table from Design View). You need a printdocument object/control to use the printing methods in vb. This object controls what is sent to the printer and can be used for the dialogs eg print preview. The user simply needs to select a pdf printer for it to be converted to PDF then.

When it comes to implementing a data access solution in your VB applications, you currently have three choices: Data Access Objects (DAO), Remote Data Objects (RDO), and ActiveX Data Objects (ADO). In this series of articles, we will examine each of these options, noting their similarities and differences. We'll also look at some cases where one is better suited for a specific task than another. Let's start with a look at DAO.
DAO basics
DAO, which was created before RDO and ADO, is a set of objects that enables client applications to programmatically access data. But DAO doesn't just let you access data—it also lets you control and manage local and remote databases in various formats. Using DAO, you can create and modify the database structure; create tables, queries, relationships, and indexes; retrieve, add, update, and remove data; implement security; work with different file formats; and link tables to other tables.
To understand DAO better, let's look at the DAO objects in Figure A.

Figure A

Names and descriptions of common DAO objects

The DBEngine is the highest-level object in the DAO object model. It contains all other objects and collections. The Database object is the member of the Databases collection of the default Workspace object, which is a member of the Workspaces collection of the DBEngine object.
With DAO, objects you use to work with the data in the database are generally not saved but are created every time you need them. Objects that represent the structure of the database are saved with the database. When you create a new DAO object to be saved in the database, you have to append it to the appropriate collection using the collection's Append method. Keep in mind that all DAO objects are indexed beginning with zero.
DAO lets you work with three database formats:

• · Microsoft Jet (all databases that are created with the Microsoft Jet database engine)
• · Installable ISAM format
• · ODBC data sources

Jet and ISAM data use the Microsoft Jet object model; however, with ODBC data, you can use either Microsoft Jet or ODBCDirect. There are some limitations in accessing ODBC data using Jet, although you can use it if you need to take advantage of a particular Jet feature. But ODBCDirect is more flexible, allowing you to run queries or stored procedures against the backend server and perform batch updates and asynchronous queries. It also makes your application run faster because it allows you to bypass Microsoft Jet's middle layer.
Let's take a look at the Jet and ODBCDirect object models.
DAO with Jet
Microsoft Jet objects include TableDef, QueryDef, Field, Index, Parameter, Relation, Recordset, User, Group, Container, and Document. (See Figure B.)

Figure B

DAO with Microsoft Jet object model (Source: Microsoft)

The DBEngine object contains two collections: Workspaces and Errors. The Workspaces collection is the default collection of the DBEngine, so you don't have to refer to it explicitly. When you don't specifically create a new Workspace object, DAO will create one for you. The setting of the DefaultType property of DBEngine determines what type of workspace is created for Microsoft Jet or ODBCDirect. The default value of this property is dbUseJet, but you can explicitly set it to dbUseODBC as the type argument of the CreateWorkspace method.
The Workspace object defines a session for a user based on users' permissions and allows managing of the current session. It also contains open databases and offers mechanisms for simultaneous transactions and for securing the application. The Fields collection is the default collection for TableDef, QueryDef, Index, Relation, and Recordset objects. Recordset objects can be of the following types: Table, Dynaset, Snapshot, or Forward-Only.
DAO with ODBCDirect
The DAO ODBCDirect object model includes a subset of the objects in a Microsoft Jet workspace and the Connection object, as shown in Figure C.

Figure C

DAO with ODBCDirect object model (Source: Microsoft)

To establish a connection using ODBCDirect, you have to use the OpenConnection method on a new Connection object or the OpenDatabase method to open a new Database object. A Connection object represents a connection to an ODBC database in an ODBCDirect workspace. The Connections collection contains all currently open Connection objects. When you open a Connection object, it is automatically appended to the Connections collection of the Workspace object. When you close a Connection object with the Close method, the Connections object is removed from the Connections collection.
In addition to the Table

Data Access Object In Java

, Dynaset, Snapshot, and Forward-Only types of Recordsets, ODBCDirect offers the Dynamic type.
Advantages and disadvantages of using DAO
On the plus side, DAO is fairly easy to use. And since DAO has been around longer than RDO or ADO and has been used in more projects, it pays to know how DAO works. Furthermore, if your application is running in a 16-bit environment, DAO is your only choice.
But DAO is older technology, and it doesn't offer as much functionality as RDO and ADO. For instance, ADO can provide an interface to e-mail and file systems and custom business objects, as well as other sources. Microsoft is now focusing most of its improvements and advances on ADO, as well.
Generally, it's better to use DAO for accessing local databases where the speed is not the top priority and the number of users is limited, and to use either RDO or ADO for accessing remote databases and for larger scale projects.
Let's code
To demonstrate how you might put DAO to work, let’s create a simple VB project to access the data stored in Microsoft's sample Northwind database.

Data Access Object In Vb 6.0 Pdf Pdf

1. 1. Fire up VB and start a new project.
2. 2. Go to Project References and select Microsoft DAO 3.6 Object Library, as shown in Figure D. (Depending on the version of VB you are using, you will have a corresponding DAO Object Library version, so if you don't have DAO 3.6, use an earlier version instead.)

Figure D

DAO object library selected in Project References

1. 3. Add two combo boxes (cboLastNameJet and cboLastNameODBCDirect) and two command buttons (cmdGetDataJet and cmdGetDataODBCDirect) to the form.
2. 4. Your screen should resemble the form shown in Figure E.

1. 5. Add the code in Listing A to the cmdGetDataJet_Click() event.
2. 6. Add the code shown in Listing B to the Private SubcmdGetDataODBCDirect_Click() event.
3. 7. Modify strLocation to reflect the location of the Northwind database on your machine or use another .mdb database and modify Set dbJet = wrkJet.OpenDatabase(strLocation & 'Northwind.mdb') to reflect the name of the database.
4. 8. Modify strConn to reflect the DSN name, password, and UID of a remote database.
5. 9. Modify the query in Set rsODBCDirect = conODBCDirect.OpenRecordset('SELECT LastName FROM Employees', dbOpenDynamic) to reflect the query you'd like to run.
6. 10. Press [Ctrl][F5] to run the project.
7. 11. Click the Get Data Jet button and the Get Data ODBC Direct button to obtain data using Microsoft Jet and ODBCDirect, respectively.
8. 12. You should see a screen like the one shown in Figure F.

Figure F

Results of clicking a Get Data button

Summary
In this article, we've examined DAO objects and the object models of Microsoft Jet and ODBCDirect. We also created a simple VB project that showed how you might use DAO with Jet and ODBCDirect to access data.
In our next installment, we'll turn our attention to RDO. We'll look at its object model and discuss a few advantages and disadvantages, and then we'll work through some code samples illustrating RDO's use.

This is not a question about which is better, but rather a question regarding why they differ functionally. The problem I was running into has been handled, but I am curious as to why this behavior is happening.

Background - using Excel vba to pull data from an Access database. When user clicks a button, a recordset is pulled from Access, and it populates various data to the spreadsheet. Then, another recordset is pulled from a different query to populate another part of the spreadsheet.

What ADO does - ADO works great for my first recordset. However, my second recordset goes to the query in Access, runs, and returns no rows. If I run this query in Access, it does open up (after about 3 to 4 seconds). This query has multiple joins, computed items, limits, and possibly Union queries (I tried it many different ways, with/without union,etc.). I tried closing and reopening the ado connection. I tried changing timeout values, and I even tested using an ADO command to run Make table queries for this data, and then pull from the table instead (this worked by the way, but is not the best-case, since the data changes continually, and I do not want to have to run the make table query everytime someone uses this tool).

So, I changed the second data pull to DAO, and lo and behold, it works. The first data pull is still ADO (which I generally prefer to use), but am now considering changing it to DAO, because I would rather have one data access method in the code.

So, can someone explain to me why ADO will not pull the data in one case, but DAO will? Again, this is purely for informational purposes.

AProughAPrough

3 Answers

DAO is the native data access method for the Jet (Ms-Access) data tables. ADO 'Active X Data Objects' is an industry friendly connection to almost all types of database.

With a standard query there is no reason in this case why ADO should return no records where DAO does, I suspect it's that the query must also contain parameters relating to items within the Access database. If this is the case then ADO will not work as it will not have the option of using said parameters as it is just an external reference to Excel, using the DAO method will trigger Access to run the query rather than Excel and as such it will be able to access it's own parameters/references.

Matt DonnanMatt Donnan

I think this question was answered very well already but I want to add another excellent (although somewhat hard to find) resource for comparing DAO and ADO. It's targeted primarily at Access applications with Access forms and reports, but much of the information here would apply to a solution written in Excel.

HK1HK1

In Visual Basic, three data access interfaces are available to you: ActiveX Data Objects (ADO), Remote Data Objects (RDO), and Data Access Objects (DAO). A data access interface is an object model that represents various facets of accessing data. Using Visual Basic, you can programmatically control the connection, statement builders, and returned data for use in any application.

Why are there three data access interfaces in Visual Basic? Data access technology is constantly evolving, and each of the three interfaces represent a different state of the art. The latest is ADO, which features a simpler — yet more flexible — object model than either RDO or DAO. For new projects, you should use ADO as your data access interface.

zero323

Not the answer you're looking for? Browse other questions tagged excelms-accessvbaadodao or ask your own question.