Prikazi cijelu temu 14.07.2013 11:33
Gjoreski Van mreze
Administrator
Registrovan od:02.02.2009
Lokacija:Ohrid Makedonija


Predmet:Re: DLookup ,DCount ,DMax ,DMin funkcii
Konekcija so baza vo slucajov MSACCESS baza . Tip na konekcija ADO
PreuzmiIzvorni kôd (Visual Basic):
  1. Public cn As ADODB.Connection
  2. '--------------------------  MsAccess Baza ---------------------------------------------
  3. cn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
  4.                      "Dbq=VasaBaza.mdb;" & _
  5.                      "DefaultDir=" & App.Path & ";" & _
  6.                      "Uid=Admin;Pwd=;"
  7. '---------------------------------------------------------------------------------------


PreuzmiIzvorni kôd (Visual Basic):
  1. Public Function DLookup(Pole As String, Tabela As String, Optional ByVal Uslov = "") As Variant
  2.    On Error Resume Next
  3.    Dim rsdl As ADODB.Recordset
  4.    Dim StrSQL As String
  5.    
  6.    If IsNull(Uslov) Or Uslov = "" Then
  7.      StrSQL = "SELECT " & Tabela & "." & Pole & " FROM " & Tabela
  8.    Else
  9.      StrSQL = "SELECT " & Tabela & "." & Pole & " FROM " & Tabela & " WHERE ((" & Tabela & "." & Uslov & "))"
  10.    End If
  11.    Set rsdl = cn.Execute(StrSQL)
  12.    
  13.    If Not (rsdl.EOF And rsdl.BOF) Then
  14.       rsdl.MoveFirst
  15.      'DLookup = rsdl(Pole).Value
  16.      DLookup = rsdl(0).Value
  17.    Else
  18.       DLookup = ""
  19.    End If
  20.    
  21. End Function
  22.  
  23. Public Function DCount(ByVal Pole As String, ByVal Tabela As String, Optional ByVal Uslov = "")
  24.    
  25.    Dim rsdc As ADODB.Recordset
  26.    Dim StrSQL As String
  27.    Dim I As Integer
  28.        I = 0
  29.    If IsNull(Uslov) Or Uslov = "" Then
  30.       StrSQL = "SELECT " & Tabela & "." & Pole & " FROM " & Tabela
  31.    Else
  32.       StrSQL = "SELECT " & Tabela & "." & Pole & " FROM " & Tabela & " WHERE ((" & Tabela & "." & Uslov & "))"
  33.    End If
  34.    Set rsdc = cn.Execute(StrSQL)
  35.    
  36.    If Not (rsdc.EOF And rsdc.BOF) Then
  37.           rsdc.MoveFirst
  38.       Do While Not rsdc.EOF
  39.          I = I + 1
  40.          rsdc.MoveNext
  41.           If rsdc.EOF Then
  42.              Exit Do
  43.           End If
  44.       Loop
  45.   DCount = I
  46.       Exit Function
  47.    Else
  48.      DCount = ""
  49.      Exit Function
  50.    End If
  51.  
  52. End Function
  53.  
  54. Public Function DMax(ByVal Pole As String, ByVal Tabela As String, Optional ByVal Uslov = "")
  55.    ' On Error Resume Next
  56.      Dim rcMax As Recordset
  57.       Dim strMax As String
  58.            
  59.               If IsNull(Uslov) Or Uslov = "" Then
  60.                  strMax = "SELECT Max(" & Tabela & "." & Pole & ") AS MaxVrednost " & " FROM " & Tabela & ""
  61.               Else
  62.                  strMax = "SELECT Max(" & Tabela & "." & Pole & ") AS MaxVrednost " & " FROM " & Tabela & " WHERE (" & Tabela & "." & Uslov & ")"
  63.               End If
  64.        
  65.       Set rcMax = cn.Execute(strMax)
  66.       If IsNull(rcMax!MaxVrednost) Then
  67.          DMax = ""
  68.       Else
  69.          DMax = rcMax!MaxVrednost
  70.       End If
  71.      rcMax.Close
  72.  
  73.  
  74. End Function
  75.  
  76. Public Function DMin(ByVal Pole As String, ByVal Tabela As String, Optional ByVal Uslov = "")
  77.      
  78.       Dim rcMin As ADODB.Recordset
  79.       Dim strMin As String
  80.            
  81.               If IsNull(Uslov) Or Uslov = "" Then
  82.                  strMin = "SELECT Min(" & Tabela & "." & Pole & ") AS MinVrednost " & " FROM " & Tabela & ""
  83.               Else
  84.                  strMin = "SELECT Min(" & Tabela & "." & Pole & ") AS MinVrednost " & " FROM " & Tabela & " WHERE (" & Tabela & "." & Uslov & ")"
  85.               End If
  86.        
  87.       Set rcMin = cn.Execute(strMin)
  88.       If IsNull(DMin = rcMin!MinVrednost) Then
  89.           DMin = ""
  90.       Else
  91.           DMin = rcMin!MinVrednost
  92.       End If
  93.     rcMin.Close
  94. End Function