Read Me:Access to any type of DB's possible

[AV Manoharan]

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

[QVeen72]

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

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

[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

[QVeen72]

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

[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

[QVeen72]

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

[QVeen72]

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

[AV Manoharan]

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

[QVeen72]

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

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.

DAO or ADO ?
DAO is an old fashioned programming technique developed by Microsoft before the ADO technique.

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

DAO or ADO ?
To learn new things, you have to unlearn many things.

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

[AV Manoharan]

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

[debasisdas]

After all the fight between both of you ,why not also post some RDO code.
That can help a some users.

[AV Manoharan]

After all the fight between both of you ,why not also post some RDO code.
That can help a some users.

Thanks. Your User name resonate with dbase. What should I call you?

[suneel kar]

hi brother
how r u. i was waitin for ur reply but dont know y u nott replyin to me .i know i am a fresher so u must be thinkin that these r silly things but brother .i was expectin ur response /ur mail id/chat id
regards
suneel kar

[suneel kar]

hi Mohanram
this is me suneel.i am sendin u code for readin data from .dbf files recordwise and storing the data recordwise in txt file.but it is givin me the errr"Could not find installable ISAM' wts that.someone has edited my code but its not workin then also.it is edited code.

Option Explicit

Dim DB As Database

Private Sub Command1_Click()
'
Dim sSQL As String
Dim RST As Recordset
Dim FN As Long
Dim j As Integer
Dim FldCnt As Integer
Dim i As Long
'
Set DB = DBEngine.OpenDatabase(App.Path, False, False, "FoxPro 2.5;")
sSQL = "Select * from RR Order By SNo"
Set RST = DB.OpenRecordset(sSQL, dbOpenSnapshot)
If RST.RecordCount > 0 Then
RST.MoveFirst
If Dir("c:\RecTexts") = "" Then
MkDir "c:\RecTexts"
End If
If Dir("c:\RecTexts\data.txt") <> "" Then
Kill "c:\RecTexts\*.*"
End If
'
i = 0
'
FldCnt = RST.Fields.Count - 1
Do While Not RST.EOF
'
i = i + 1
FN = FreeFile
Open "c:\RecTexts\DATA" & i & ".TXT" For Append As FN
For j = 0 To FldCnt
Print #FN, IIf(IsNull(RST(j)), "", RST(j))
Next
Close #FN
RST.MoveNext
Loop
End If
RST.Close
Set RST = Nothing
DB.Close
Set DB = Nothing
MsgBox "Transfer Over Check Folder C:\RecTexts"
End Sub

reply soon
regards
suneel

[AV Manoharan]

hi Mohanram
this is me suneel.i am sendin u code for readin data from .dbf files recordwise and storing the data recordwise in txt file.but it is givin me the errr"Could not find installable ISAM' wts that.someone has edited my code but its not workin then also.it is edited code.

Option Explicit

Dim DB As Database

Private Sub Command1_Click()
'
Dim sSQL As String
Dim RST As Recordset
Dim FN As Long
Dim j As Integer
Dim FldCnt As Integer
Dim i As Long
'
Set DB = DBEngine.OpenDatabase(App.Path, False, False, "FoxPro 2.5;")
sSQL = "Select * from RR Order By SNo"
Set RST = DB.OpenRecordset(sSQL, dbOpenSnapshot)
If RST.RecordCount > 0 Then
RST.MoveFirst
If Dir("c:\RecTexts") = "" Then
MkDir "c:\RecTexts"
End If
If Dir("c:\RecTexts\data.txt") <> "" Then
Kill "c:\RecTexts\*.*"
End If
'
i = 0
'
FldCnt = RST.Fields.Count - 1
Do While Not RST.EOF
'
i = i + 1
FN = FreeFile
Open "c:\RecTexts\DATA" & i & ".TXT" For Append As FN
For j = 0 To FldCnt
Print #FN, IIf(IsNull(RST(j)), "", RST(j))
Next
Close #FN
RST.MoveNext
Loop
End If
RST.Close
Set RST = Nothing
DB.Close
Set DB = Nothing
MsgBox "Transfer Over Check Folder C:\RecTexts"
End Sub

reply soon
regards
suneel

Get me the full path and dbf file name from which you want to grab records.

regards
manoharan

[suneel kar]

.dbf file name is RR.dbf,
path where project is saved is C:\suneel-development\New folder\record reading.
these things veena told me to do but i am not v clear with this the code which u send me earlier was easy to understand .mazy i have ue gmail id
if u can help me i will be thankful
reply soon
regards
suneel.

[AV Manoharan]

.dbf file name is RR.dbf,
path where project is saved is C:\suneel-development\New folder\record reading.
these things veena told me to do but i am not v clear with this the code which u send me earlier was easy to understand .mazy i have ue gmail id
if u can help me i will be thankful
reply soon
regards
suneel.

I AM NOT INTERESTED IN YOUR PROJECT PATH. I WANT THE PATH OF YOUR DBF FILE RR.DBF (WHERE IN YOUR HARD DISK YOU HAVE STORED RR.DBF

[suneel kar]

hello
when u will reply bro
am waitin
regards
suneel kar

[suneel kar]

its c drive

[suneel kar]

hello bro Manohar
sorry to spell ur name incorrectly.
may i have code in ur style. i like ur style of code generation . plz do the needful as it is simple and easy to understand
regards
suneel kar

[suneel kar]

bro
kindly send code in pvt msg
regards
suneel kar

[suneel kar]

hello manohar
where r u bro.u told me that u gonna send code in 15 mins .but i have not received any code yet.i have cheched both pvt msg ans gmail also.plz tellme where u have send code.
regards
suneel

[AV Manoharan]

Suneel,
I have emailed the complete program to you. Do not ask me like this any more. Because, I am a very busy person. For your requirement I have to stream line the code and after testing I am sending it to you.
Do not give the sourse code to anybody. Keep it to yourself

You have to change the path in the connection string for the dbase file. Your data file will be created in c:\drive. The data file can be imported to Excell very easily by giving the delimiter , (coma)

You have to do some preliminary works.

Follow as bellow, carefully.

Before copying the text code into you project, Open a new exe project, pull down the menu project, then select components. in components control pannel check the following item.

ctv OLE Control Module
Microsoft ADO Data Control 6.0 (sp6)(OLEDB)
Microsoft Datagrid control 6.0) ..

Press OK

Now Go to references in the same Project window
and see the following are checked

The top 4 are defaulted.
That is up to OLE automation
then,
check microsoft jet and replication objects
microsoft ado ext.2.8
microsoft activex data objects
microsoft DAO 3.6 (incase the ADO doesn't work)

Click ok

create a form with one commandbutton
click the code window from the right pan
and select form local

paste my code

change the path of your dbf file in the connection string.

Do not alter the connection string.

run it

You form window will be shown with the commandbutton captioned as successfully Extracted.
Click it
the form unloads.

check in c:\ drive for the data.txt file. every time you run the program it will be recreated afresh. No killing and creating in your program code, OK

Study the code carefully.
I think when you get the connection 75% of your job is done.

Happy programming

AV Manoharan

[jireh]

What makes any difference if you are using VB? What's the advantage and disadvantage? What can do the DAO and the ADO cannot or vice-versa? The question to the programmers is that How I will connect to the database? Which is more convenient to me? Ok granted that the DAO is outdated, but the question is, what makes any difference its just a connection to the database where you will save your data, right?

[AV Manoharan]

Jireh,
Kindly change your BLOB. It has a threatening look!

Now, your Doubt.

DAO or ADO. What is the difference. And all that.
See, I have elsewhere in this thread explained the diiference briefly.

Hear me more.

If you are a DBA or DBD (Database Administrator or Database developer), you require frequent access to so many varities of data souces such as server-based, desktop, spreadshhet, textfiles etc. type of databases. You might have witnessed in this forum people asking the same question of connecton to ACCESS, dBASE etc. data bases.

If you think of developing custom built interfaces for each type of databases, then you can imagine the difficulty of a programer. Here only microsoft's contribution of DAO and ADO becomes so valuable for a developer in Microsoft environment. It relieves much burden from a programmer by giving himm an abstract highlevel interface.
DAO and ADO basically does the same thing. But why ADO instead of DAO is that DAO is the earlier technology and ADO is new one. The technology is ever changing.

Microsoft is known for its dubious reputation in the software industry as a 'developer-locking Giant', means making developers and end users stick to Microsoft environment. By the introduction of ADO, actually, Microsoft restricted developers from going to platform independed environment (eg. Java). Microsoft included ADO drivers for any data sourse you can imagine. Text File database, Spreadsheet databases, oracle databases, IBM DB2, SQL databases... the list is endless.

Still, why, ADO instead of DAO?
Very simple. Microsoft announced, stopping of further updation on DAO. Only ADO will be updated. Means, if a new technology database comes you will have only ADO for it. Not DAO.

Is it clear to you now?

Happy Programming
AV Manoharan