Re: Database Stuff

As Laurie said, you don't need MS Access to use MDB files. MDAC gets
installed with Windows update and even IE. Windows 98 comes with ADO 1.5.
Windows 2000 comes with ADO 2.5, and XP with 2.6 or 2.7 depending on the
service pack. So you can access MDB files without even installing MDAC, but
it's preferable to install the latest version.

One reason that many use Access files is because there is no need for a
server, it's file based access with locking used in multi-user environment.
Another reason is that you can use a Query Builder or Analyzer to create the
SQL statement, then copy it to your VB6 app. In MS Access, create the query
that you like and in the View menu, click SQL View. This will show the SQL
statement. Just copy it and paste it so you don't have to type it.

If your application grows and later need a faster database server, then you
only need to change the connection string to use the new server. MySQL is
free and can be used in Windows.

VB6 have a wizard to build a form based on a database table or query. This
allows you to create a form quickly. In the Add-ins menu, add "VB 6 Data
Form Wizard" to your project and use it. This creates a form with controls
bound to the database. If the user edited the contents in the form, they are
saved into the database. The user can navigate to other records using the
ADO Data Control, however, you can make it hidden and provide your own
Command buttons if needed.

From code you can get a reference to the data in the ADO Control and use it
in code. Here is an example:

Dim rs As ADODB.Recordset
Set rs = Adodc1.Recordset
rs.MoveFirst
Debug.Print "Full name: " & rs![FullName]

Note that when you use rs.MoveNext to move to the next record, the form
display the next record. If you have a Grid in the form, the visual
indicator will move as well. To avoid this, use a cloned record set. Here is
an example:

Dim rsForUseInCode As ADODB.Recordset
Set rsForUseInCode = Adodc1.Recordset.Clone
rsForUseInCode.MoveFirst
For i = 1 To rsForUseInCode.RecordCount
Debug.Print "Full name: " & rs![FullName]
rsForUseInCode.MoveNext
Next
rsForUseInCode.Close
Set rsForUseInCode = Nothing

This will not move the current record in the GUI/Form.

Finally, it's possible to work with databases with code only. Here is the
minimal code to display records from a table:

Private Sub Command1_Click()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ConStr As String

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyDB.mdb"
Set con = CreateObject("Adodb.Connection")
Debug.Print con.Version
con.CursorLocation = adUseClient
con.Open ConStr
Set rs = CreateObject("Adodb.Recordset")
rs.CursorLocation = adUseClient

rs.Open "SELECT * FROM Customers ORDER BY CompanyName", con,
adOpenStatic, adLockOptimistic
Debug.Print "Records count = " & rs.RecordCount & "<BR>"

Do While Not rs.EOF
Debug.Print "Full name: " & rs![FullName]
rs.MoveNext
Loop

rs.Close
con.Close

Set rs = Nothing
Set con = Nothing
End Sub

You can add records to the above table by using the following statements:

rs.AddNew
rs![FullName] = "ABC"
rs![Phone] = "12345678790"
rs.Update





.

Quantcast