Доступ к базам данных с помощью технологий DAO.

ПРИМЕР DAO

Вызов отчетов, сформированных в базе данных

Технология ADO предоставляет объекты и методы работы с данными, т.е. с таблицами, запросами. Отчеты – это показ данных. Поэтому ADO не дает возможности это сделать. У COM объекта Access.Application есть соответствующий метод. Но этот подход возможен только, если на компьютере установлен MS Access.

Dim abd As Access.Application

Set abd = CreateObject("Access.Application")

abd.OpenCurrentDatabase "i:\BD\БД_Книж_издат_V98.mdb"

abd.DoCmd.OpenReport "Книга", acViewPreview

Технология ADO предоставляет доступ к данным. Интерфейсная часть, т.е. входные и выходные формы вы должны составить в клиентском (интерфейсном) приложении.

Dim bd As DAO.Database, rs As DAO.Recordset, td As DAO.TableDefs

Private Sub CommandButton1_Click() ' DAO

UserForm1.ListBox1.Clear

UserForm1.ComboBox1.Clear

Set bd = _

OpenDatabase(ActiveWorkbook.Path & "\БД_Книж_издат_V98.mdb")

Set td = bd.TableDefs

UserForm1.ComboBox1.SetFocus

For i = 0 To td.Count - 1

If td(i).Attributes = 0 Then ' пользовательские таблицы

UserForm1.ComboBox1.AddItem td(i).Name

If UserForm1.ComboBox1.ListCount = 1 Then UserForm1.ComboBox1.Value = td(i).Name

End If

Next

UserForm1.ComboBox1.DropDown

End Sub

Sub zapoln_sp(rs)

Dim kfld As Integer, tb As ADOX.Table

UserForm1.ListBox1.Clear

Set tb = adocat.Tables(UserForm1.ComboBox1.Value)

UserForm1.ListBox1.AddItem

UserForm1.ListBox1.ColumnCount = rs.Fields.Count

For j = 0 To tb.Columns.Count - 1

UserForm1.ListBox1.List(0, j) = tb.Columns(j).Name

Next

ns = 1

Do While Not rs.EOF

UserForm1.ListBox1.AddItem

For nk = 0 To rs.Fields.Count - 1

If rs.Fields(nk) <> "" Then UserForm1.ListBox1.List(ns, nk) = rs.Fields(nk)

Next

ns = ns + 1

rs.MoveNext

Loop

End Sub

Private Sub CommandButton2_Click() ' DAO

' Set rs = bd.OpenRecordset("select * from [" & ComboBox1.Value & "]")

Set rs = bd.OpenRecordset(UserForm1.ComboBox1.Value)

zapoln_sp rs

End Sub

Sub adoadd()

adors.AddNew

adors.Fields(0) = "123"

adors.Update

End Sub

Private Sub CommandButton3_Click()

'добавление новой записи

adoadd

rs.AddNew

rs.Fields(0) = "111111"

rs.Fields(1) = "Иванов И.И."

'сохранение данных в базе

rs.Update

' Set rs = bd.OpenRecordset("select * from flow")

' rs.MoveFirst

' Do While Not rs.EOF

' MsgBox rs.Fields(1)

' rs.MoveNext

' Loop

bd.Close

End Sub

Private Sub CommandButton5_Click()

Set rs = bd.OpenRecordset(UserForm1.TextBox1.Text)

zapoln_sp rs

End Sub

Dim bd As Database, rs As Recordset, td As DAO.TableDefs

Private Sub CommandButton1_Click()

UserForm1.ListBox1.Clear

UserForm1.ComboBox1.Clear

Set bd = OpenDatabase(ActiveWorkbook.Path & "\ÁÄ_Êíèæ_èçäàò_V98.mdb")

Set td = bd.TableDefs

UserForm1.ComboBox1.SetFocus

For i = 0 To td.Count - 1

If td(i).Attributes = 0 Then ' ïîëüçîâàòåëüñêèå òàáëèöû

UserForm1.ComboBox1.AddItem td(i).Name

If UserForm1.ComboBox1.ListCount = 1 Then UserForm1.ComboBox1.Value = td(i).Name

End If

Next

UserForm1.ComboBox1.DropDown

End Sub

Sub zapoln_sp(ByRef rs As DAO.Recordset)

Dim kfld As Integer

UserForm1.ListBox1.Clear

UserForm1.ListBox1.ColumnCount = rs.Fields.Count

ns = 0

Do While Not rs.EOF

UserForm1.ListBox1.AddItem

For nk = 0 To rs.Fields.Count - 1

If rs.Fields(nk) <> "" Then UserForm1.ListBox1.List(ns, nk) = rs.Fields(nk)

Next

ns = ns + 1

rs.MoveNext

Loop

End Sub

Private Sub CommandButton2_Click()

' Set rs = bd.OpenRecordset("select * from [" & ComboBox1.Value & "]")

Set rs = bd.OpenRecordset(UserForm1.ComboBox1.Value)

zapoln_sp rs

End Sub

Private Sub CommandButton3_Click()

'äîáàâëåíèå íîâîé çàïèñè

rs.AddNew

rs.Fields(0) = "111111"

rs.Fields(1) = "Èâàíîâ È.È."

'ñîõðàíåíèå äàííûõ â áàçå

rs.Update

' Set rs = bd.OpenRecordset("select * from flow")

' rs.MoveFirst

' Do While Not rs.EOF

' MsgBox rs.Fields(1)

' rs.MoveNext

' Loop

bd.Close

End Sub

Private Sub CommandButton5_Click()

Set rs = bd.OpenRecordset(UserForm1.TextBox1.Text)

zapoln_sp rs

End Sub

Dim bd As Database, rs As Recordset, td As DAO.TableDefs

Private Sub CommandButton1_Click()

UserForm1.ListBox1.Clear

UserForm1.ComboBox1.Clear

Set bd = OpenDatabase(ActiveWorkbook.Path & "\БД_Книж_издат_V98.mdb")

Set td = bd.TableDefs

UserForm1.ComboBox1.SetFocus

For i = 0 To td.Count - 1

If td(i).Attributes = 0 Then ' пользовательские таблицы

UserForm1.ComboBox1.AddItem td(i).Name

If UserForm1.ComboBox1.ListCount = 1 Then UserForm1.ComboBox1.Value = td(i).Name

End If

Next

UserForm1.ComboBox1.DropDown

End Sub

Sub zapoln_sp(ByRef rs As DAO.Recordset)

Dim kfld As Integer

UserForm1.ListBox1.Clear

UserForm1.ListBox1.ColumnCount = rs.Fields.Count

ns = 0

Do While Not rs.EOF

UserForm1.ListBox1.AddItem

For nk = 0 To rs.Fields.Count - 1

If rs.Fields(nk) <> "" Then UserForm1.ListBox1.List(ns, nk) = rs.Fields(nk)

Next

ns = ns + 1

rs.MoveNext

Loop

End Sub

Private Sub CommandButton2_Click()

' Set rs = bd.OpenRecordset("select * from [" & ComboBox1.Value & "]")

Set rs = bd.OpenRecordset(UserForm1.ComboBox1.Value)

zapoln_sp rs

End Sub

Private Sub CommandButton3_Click()

'добавление новой записи

rs.AddNew

rs.Fields(0) = "111111"

rs.Fields(1) = "Иванов И.И."

'сохранение данных в базе

rs.Update

' Set rs = bd.OpenRecordset("select * from flow")

' rs.MoveFirst

' Do While Not rs.EOF

' MsgBox rs.Fields(1)

' rs.MoveNext

' Loop

bd.Close

End Sub

Private Sub CommandButton5_Click()

Set rs = bd.OpenRecordset(UserForm1.TextBox1.Text)

zapoln_sp rs

End Sub

Private Sub UserForm_Click()

End Sub