See, here I am going to explain for all concerned in this
forum, about the two types of Data access objects which
you can use in your VB (?). There are so many postings
on this subject.

The duo is DAO & ADO. It is not misspelled acronym like
PHP

DAO (Data Access Object)

DAO is a data access technology primarily designed to
use the Jet databases like Microsoft Access or dBase,
FoxPro, etc.

ADO (ActiveX Data Objects)

ADO uses the OLE DB (Object Linking & Embidding- forget
about it, if you don't understand it at this juncture),
technology to gain access to any type of data source.
The ADO object model is based on three types of objects
namely Connection, Command and Record set.

The connection object holds data source connection
information like data source name, its location, the
user id and password, the name of the OLE DB provider,
etc. The command object is used to execute SQL
commands, queries and stored procedures. The Record set
object holds the results returned by the queries and
used for adding records to the tables and updating the
database.

In ADO, "CurrentProject. Connection" determines the connection string.

The connection string is:

Provider=Microsoft.Access.OLEDB.10.0; Persist Security
Info=False; Data Source=Server ip address or name;
UID=sa; PWD=sa;Initial Catalog= Database Name;
Data Provider=SQLOLEDB.1

Opening and Adding Value to a Record Set

Let us now examine how to open and add value to a
recordset using DAO and ADO.

1 st - The DAO code:

Dim db as Database
Dim rs as DAO.Recordset
Set db = CurrentDb( )
Set rs = db.OpenRecordset(“tblPeople”)
rs.Edit
rs(“TextFieldName”) = “New Value”
rs.Update

2 nd - The ADO Code:

Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
rs.AddNew
rs("TextFieldName ") = NewValue
rs.Update
rs.Close

Executing a Query

The DAO way:

db.Execute "SELECT …."

The ADO way:

Dim CommandText As String
CommandText = "SELECT…….."
rs.Open CommandText, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText

These changes are to be made throughout the code in the
application when ever you wish to change the
corresponding databases.

Happy programming

regards
AV Manoharan

Hi Manohar,

In whole of ur post, u did not Mention opeing of DB in DAO method,

Set DB = DBEngine.OpenDatabase("C:\MyDB",False,False,";pwd=Mypwd")


Regards
Veena

Dear Veena,

what is the code SET DB = CurrentDb() denotes ?

thanks for showing interest in my postings.

regards
AV Manoharan

Hi Manohar,

Where is the Path and Where r u setting/Opening Current DB..?
Where r u Setting parameters for Current D?
Simply setting DB to CurrentDb dosent work.

and whatever connection String u have given is only for "ADO" Connection.
Moreover u dont need a ADO Command object to execute a query, u can directly exceute it on ADO Conn object.

Regards
Veena

My dear Veena,

I think you messed up all things. My connection string is for all types of DBs. It is very advanced which you people may not be aware of.
When you provide a connection string with all the details, it is not necessary for giving path name etc. Just table name is enough. Why don't you try it before passing corrections/ judgements ? And for your information my CurrentDB() will look after all those thing. Please just try the code and get back to me.

regards
AV Manoharan

Hi Manohar,

I tried, it gives me error "Sub Or Function Not defined"

Why dont u open a new project and paste that code and try it..?

May be there is some reference u need to add to the project and u r missing it..?
or u might be using ur own .dll..?

REgards
Veena

Hello Manohar,

I Mean Check the DAO code in ur new project.
and u r talking abt ADO connection String.. look who is messing up the things.

Regards
Veena

DAO or ADO ?

Veena, Again see, you have to do some preliminary work for setting up Data Source for an Access Database (auto). for DAO as follows:

Open the control pannel and double click the ODBC Data Source from the Administrative Tools, Click the Add button on the ODBC Data Source Administrator Window and select Microsoft Access Driver. In the ODBC Microsoft Access Setup window,specify a data source name and a description. Click Select and you will be prompted to select the MDB file in a File Open dialogbox. Locate your "MyDb.Mdb" file on your disk and click OK. After returning to your ODBC Microsoft Access Setup window, click OK, and you are through for that database on all your project without a connection string (Cnn). Otherwuse you have to give the pathname,filename after the data source, string identifier. eg.

Data Source = C:\Program Files\VB98\NWind.mdb

and you have to declare and open the connection cnn.

DAO is an old fashioned programming technique developed by Microsoft before the ADO technique. ADO hides the pecularities of each database and gives developers a simple conceptual veiw of the underlying database Many of you may be familier with DAO and RDO which uses the ODBC drivers. In someway DAO and ADO are equevalent but further updation of DAO was stopped by Microsoft long back. So my advice is that when a more versatile ADO is available why to use the old DAO, other than for academic iterest ?. Ofcourse, still, the books written for VB deal with DAO at length, which confuses many students.

A dictum is there in programming.

To learn new things, you have to unlearn many things.

Happy Programming

regards
AV Manoharan

Dear,

Where in ur Original Post did u mention Setting up of an "ODBC" connection....?
Simply telling Users To Set DB= CurrentDb()..
does not hold any meaning.
And If u r telling users to use "ODBC" for jet Access databases, its
high time, u take a course on DAO.


Old or New, If VB6 has it, people are going to use it. Or else if u dont know simply dont post such msgs

Using ODBC for accessing DAO components is the worst way one can have. Real time programmers and Real time Projects nobody uses ODBC to Save Data, It is all done using DAO Objects.
But ODBC is used To Show the Reports.

And If i tell u some thing, dosent mean u have to reply to it harshly..


Regards
Veena

Again, Veena, I think, you are yet to understand what is ODBC and what is DAO. For your kind information, let me tell you that DAO access databases through ODBC not the opposite way you have understood and said this "Using ODBC for accessing DAO components is the worst way one can have."
and also "If u r telling users to use "ODBC" for jet Access databases, its high time, u take a course on DAO."

I will make it little more clear to you (more messing up ?).

When you set the connection string manually, you are building a so-called DSN-less connection. DSN is the Data Source Name by which a data source is known to your computer. Windows allows you to create a DSN for a database and then use the DSN name to connect to the database eg.

DSN=MYDSNDB

Here MYDSNDB represents your Databaese, say MyDB, provided you have created it like that.

If you are not clear, please get back to me.

And Still I stick on to the point that DAO is an outdated technology,comparing to the more universal ADO. If you don't believe, please post a message to Dani or put it to the observers.

Happy Programming

AV Manoharan