Sub test2()
  Dim cn As New ADODB.Connection
  cn.Open ("DSN=kool1")
  cn.Execute ("CREATE TABLE inimesed(" & _
           "eesnimi VARCHAR(20), perenimi VARCHAR(20))")
  cn.Close
End Sub

Sub test3()
  Dim cn As New ADODB.Connection
  cn.Open ("DSN=kool1")
  cn.Execute ("INSERT INTO inimesed VALUES ('Juku', 'Juurikas')")
  cn.Close
End Sub

Sub test3a()
  Dim cn As New ADODB.Connection
  cn.Open ("DSN=kool1")
  eesnimi = InputBox("Palun eesnimi")
  perekonnanimi = InputBox("Palun perekonnanimi")
  lause = "INSERT INTO inimesed VALUES (' " & eesnimi & _
    " ', ' " & perekonnanimi & " ')"
  MsgBox lause
  cn.Execute (lause)
  cn.Close
End Sub

Sub test4()
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  cn.Open ("DSN=kool1")
  perekonnanimi = InputBox("Palun perekonnanimi")
  Set rs = cn.Execute("select eesnimi, perenimi from inimesed " & _
       "where perenimi=' " & perekonnanimi & " ' ")
   'Näidake vaid küsitud perekonnanimega inimeste andmeid
  While Not rs.EOF
    MsgBox rs("eesnimi") & " " & rs("perenimi")
    rs.MoveNext
  Wend
  cn.Close
End Sub


Sub kopeerimine1()
  Cells(4, 1) = 7
End Sub


Sub kopeerimine2()
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim reanr As Integer
  cn.Open ("DSN=kool1")
  Set rs = cn.Execute("SELECT DISTINCT perenimi FROM inimesed")
  reanr = 1
  While Not rs.EOF
    Cells(reanr, 2) = rs("perenimi")
    rs.MoveNext
    reanr = reanr + 1
  Wend
  cn.Close
End Sub


Sub kopeerimine3()
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim reanr As Integer
  cn.Open ("DSN=kool1")
  reanr = 1
  While Len(Sheets("nimed").Cells(reanr, 1)) > 0
    lause = "INSERT INTO inimesed VALUES('" & _
      Sheets("nimed").Cells(reanr, 1) & "', '" & _
      Sheets("nimed").Cells(reanr, 2) & "')"
    cn.Execute (lause)
    reanr = reanr + 1
  Wend
  cn.Close
End Sub





'Form 


 Private Sub CommandButton1_Click()
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  cn.Open ("DSN=kool1")
  Set rs = cn.Execute("SELECT DISTINCT perenimi FROM inimesed")
  While Not rs.EOF
    ListBox1.AddItem rs("perenimi")
    rs.MoveNext
  Wend
  cn.Close
End Sub

Private Sub CommandButton2_Click()
  UserForm1.Show
End Sub

Private Sub ListBox1_Change()
  Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  cn.Open ("DSN=kool1")
  Set rs = cn.Execute("SELECT eesnimi FROM inimesed " & _
    "where perenimi='" & ListBox1.Text & "' ")
  ListBox2.Clear
  While Not rs.EOF
     ListBox2.AddItem rs("eesnimi")
     rs.MoveNext
  Wend
  cn.Close
End Sub


' Lisamisvorm

Private Sub CommandButton1_Click()
  Dim cn As New ADODB.Connection
  cn.Open ("DSN=kool1")
  lause = "INSERT INTO inimesed VALUES (' " & TextBox1 & _
    " ', ' " & TextBox2 & " ')"
  cn.Execute (lause)
  cn.Close
  TextBox1 = ""
  TextBox2 = ""
End Sub