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 myCurrentDB() 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 msgsDAO 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