VB Database Programming
By Anne-Marie Wright
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.