This is not about how to create or design a database, it is about how to connect to a database and manipulate a database using VB. It will work (with some minor alterations) in VBA as well.

There are several ways of connecting to a database (for example, Access), via data bound controls, DAO or ADO. On the whole I do not use data bound controls because I like to keep control of what is happening to the data so the rest is about DAO/ADO.

To start with you need to create a few variables of the following types

• Workspace ADODB.Connection - This is required if you are using Transaction Processes (I will explain later)
• Database - This connects to the database Recordset
• ADODB.Recordset - This is the table/query level variable
• Field ADODB.Field - This allows us to get info about the fields

Connecting to a Database

With an Access database it is possible to connect to the database in 2 ways, JET or ODBC. Personally I use ODBC because the file management is easier; to change the filename or path just use the ODBC administrator in the control panel.

Note: These examples are here to show what to do very simply, some of the commands have more options than are shown so please review the help files for more details.

DAO example - JET Connection

Dim ws as Workspace

Dim db as Database



Set ws=DBEngine.Workspaces(0)

set db=ws.OpenDatabase({databasepath and name})

DAO example - ODBC Connection

Dim ws as Workspace

dim db as database

dim strConnection as string



set ws=DBEngine.Workspaces(0)

let strConnection= "ODBC;DSN=" & DatabaseName & ";UID=" & UserName 

& ";PWD=" & UserPassword

set db=ws.OpenDatabase("", False, False, strConnection)

ADO Example

Dim ad as ADODB.Connection



set ad=New ADODB.Connection

Let ad.ConnectionString= "ODBC;DSN=" & DatabaseName & ";UID=" & 

UserName & ";PWD=" & UserPassword

ad.Open

Opening a Table/Query for Viewing

Now we have the database connection established it is time to look at the data. The following example show how to open a table/query and move through it.

DAO Example

Dim rs as recordset



set rs=db.openrecordset({tablename or SQL})

do while not rs.eof

  'Put the code here for what to do with the information.

  'The field information can be access by the field name

  intID=rs!IDField

  'Or by the order number it is in the list (starting at 0)

  intString=rs.Field(1)

  rs.movenext

loop

ADO example

dim ar as ADODB.recordset



set ar=new adodb.recordset

ar.open {SQL Statement}

do while not ar.EOF

  'Put the code here for what to do with the information.

  'The field information can be access by the field name

  intID=ar!IDField

  'Or by the order number it is in the list (starting at 0)

  intString=ar.Field(1).value

  ar.movenext

loop

Change a Record

To edit/add/delete a record we can do it either using SQL or directly. Both DAO and ADO use the execute method for doing updates by SQL.

DAO

 

Dim rs as recordset



set rs=db.openrecordset({tablename or SQL})

rs.execute "INSERT INTO tb(ID,Name) VALUES (10,Anne)"

ADO

dim ar as ADODB.recordset



set ar=new adodb.recordset

ar.open {SQL Statement}

ar.execute "INSERT INTO tb(ID,Name) VALUES (10,Anne)"

These examples add a new record to the database directly.

DAO - Add New Record

Dim rs as recordset



set rs=db.openrecordset({tablename or SQL})

rs.addnew

rs!ID=intID

rs!Name=strName

rs.update

ADO - Add new record

dim ar as ADODB.recordset



set ar=new adodb.recordset

ar.open {SQL Statement}

ar.addnew

ar!ID=intID

ar!Name=strName

ar.update

These examples show how to edit a record directly, after the recordset is open it checks that there is a record meeting the criteria in the open SQL. If not it creates one.

DAO - Edit record

Dim rs as recordset



set rs=db.openrecordset("SELECT * FROM Tb WHERE tdID=10")

if rs.eof then

  rs.addnew

else

  rs.edit

end if

rs!ID=intID

rs!Name=strName

rs.update

ADO - Edit Record

dim ar as ADODB.recordset



set ar=new adodb.recordset

ar.open "SELECT * FROM Tb WHERE tdID=10"

if ar.eof then

  ar.addnew

else

  ar.edit

end if

ar!ID=intID

ar!Name=strName

ar.update

These examples show how to delete a record directly, after the recordset is open it checks that there is a record meeting the criteria in the open SQL. If not it does not do a delete.

DAO - Delete Record

Dim rs as recordset



set rs=db.openrecordset("SELECT * FROM Tb WHERE tdID=10")

if not rs.eof then

  rs.delete

end if

ADO - Delete Record

Dim ar as ADODB.recordset



set ar=new adodb.recordset

ar.open "SELECT * FROM Tb WHERE tdID=10"

if not ar.eof then

  ar.delete

end if

Note: If you open an object when you have finished with it, close it and set it to nothing. For example...

    rs.close

    set rs=nothing

This is good programming practice and clears the memory.