Bra Utbildning AB

 

<-- Back
VBA & Object models
Work with text
Bookmarks
Ranges
Fields
Form fields
Autotext
Doc var & Doc Prop
Built-in commands
Built-in dialogs
Automation
External databases

Work with external databases


This is an example describing how to get data from a database using Microsoft DAO (Data Access Object). A reference to the object library is set in Tools-References. Starting with Office 2000 Microsoft has chosen ADO as default for data access.
Dim db As DAO.Database
Dim dbrs As DAO.Recordset
Dim strSQL As String
Dim strFile As String

strFile = "c:\Data\Personal.mdb"
strSQL = "SELECT * FROM tblExtras;"

Set db = OpenDatabase(strFile)
Set dbrs = db.OpenRecordset(strSQL)

'do something with the retrieved data
Do While Not dbrs.EOF
      Debug.Print dbrs.Fields(1).Value
      Debug.Print dbrs!Firstname
      dbrs.MoveNext
Loop

dbrs.Close
db.Close

Set dbrs = Nothing
Set db = Nothing

This is an example describing how to retrieve data from a database using Microsoft ADO (ActiveX Data Object). A reference to the object library is set in Tools-References. The same task can be achieve a number of ways when working with ADO. These are two similar examples how to approach the task. 

Example 1 - Access-database, file and path in the procedure

Dim
cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConnect = strConnect & "Data Source=c:\Data\Personal.mdb;"
strConnect = strConnect & "Persist Security Info=False"

Set cnn = New ADODB.Connection
cnn.Open strConnect
Set rst = cnn.Execute("SELECT * FROM tblExtras")

'do something with the retrieved data, for instance
Do Until rst.EOF
      Debug.Print rst.Fields(1).Value
      Debug.Print rst!FirstName
      rst.MoveNext
Loop

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Example 2 - Database with a DSN
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblExtras"
Set cnn = New ADODB.Connection
cnn.Open "DSN=SQL_HR_DB;UID=sa;pwd=;"
Set rst = conADO.Execute(strSQL)

'do something with the retrieved data

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing