How to Update ODBC Records in VB6

Store important data in a database when proper security is in place.

Open Database Connectivity is Microsoft's interface for allowing applications to access many different SQL databases. For example, data might be stored in a SQL Server database, an Oracle database or a DB2 database. ODBC allows programmers to connect to any one of these database engines through the ODBC interface. Within the Visual Basic application, a connection is made to the database and then records can be read and updated.

Instructions

1. Open a new Standard EXE Visual Basic project. From the menu, select "Project" and then "References." Click the check box next to "Microsoft ActiveX Data Objects 2.x" where "x" is the highest number available on your machine. Click the "OK" button to save and close.

2. Add a new button to the default "Form1" that was created by default. Name the button "cmdUpdate" and set the caption equal to "Update." This button will save any updates directly to the appropriate table(s).

3. Declare variables to store the ADODB objects and a string variable for the SQL statement itself.

   Dim oConn As ADODB.Connection

   Dim strSQL As String

   Dim oCmd as ADODB.Command

4. Connect to the database in the "Form_Load" event of "Form1."

   Set oConn = New ADODB.Connection

   oConn.ConnectionString = "DSN=MyDBName;Uid=admin;Pwd=password;"

   oConn.Open

5. Code the SQL for the "update" statement in the click event of "cmdUpdate."

   strSQL = "UPDATE myTable SET columnName = 'NewName' WHERE ID = 3;"

   oCmd.CommandText = strSQL

   oCmd.Execute

References

• VB6: Visual Basic & ADO Tutorial
• MSDN: Recordset Object (ADO)
• Microsoft: ODBC--Open Database Connectivity Overview