by Ashish Parmar (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 19th April 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Access data show in grid with sql
API Declarations
Dim RS As Recordset
VBcoders
On Error GoTo Nodata
cd.Filter = "Database|*.mdb"
cd.ShowOpen
Text1.Text = cd.FileName
Set DB = OpenDatabase(Text1.Text)
Combo1.Clear
Dim tbl As TableDef
For Each tbl In DB.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
Combo1.AddItem tbl.Name
End If
Next
Nodata:
If Err.Number <> 0 Then
MsgBox " Error No. : " & Err.Number & vbCrLf & _
" Error Desc. : " & Err.Description
End If
End Sub
Private Sub Combo1_Click()
On Error GoTo Ctext
Combo2.Clear
Dim FLD As Field
For Each FLD In DB.TableDefs(Combo1.Text).Fields
Combo2.AddItem FLD.Name
Next
Data1.DatabaseName = cd.FileName
Data1.RecordSource = Combo1.Text
Data1.Refresh
Text3.Text = " "
Text3.Text = "Select * From " & Combo1.Text
Ctext:
If Err.Number <> 0 Then
MsgBox " Error No. : " & Err.Number & vbCrLf & _
" Error Desc. : " & Err.Description
End If
End Sub
Private Sub Command2_Click()
On erro GoTo SQERR
Data1.DatabaseName = cd.FileName
If IsNumeric(Text2.Text) = True Then
Text3.Text = "Select * from " & Combo1.Text & " Where " _
& Combo2.Text & Combo3.Text & Text2.Text
Else
Text3.Text = "Select * from " & Combo1.Text & " Where " _
& Combo2.Text & " " & Combo3.Text & " '" & Text2.Text & "*'"
End If
Data1.RecordSource = Text3.Text
Data1.Refresh
MsgBox Text3.Text
SQERR:
If Err.Number <> 0 Then
MsgBox " Error No. : " & Err.Number & vbCrLf & _
" Error Desc. : " & Err.Description
End If
End Sub
Private Sub Text2_LostFocus()
On Error GoTo derr
Text3.Text = " "
Dim temp As Fields
If IsNumeric(Text2.Text) = True Then
Text3.Text = "Select * from " & Combo1.Text & " Where " _
& Combo2.Text & Combo3.Text & Text2.Text
Else
' Text3.Text = "Select * from " & Combo1.Text & " Where " _
' & Combo2.Text & Combo3.Text & "'" & Text2.Text & "'"
'rs1.Open "Select Name From empl Where Name LIKE '" & Text1.Text & "*'", db, adOpenStatic, adLockOptimistic
Text3.Text = "Select * from " & Combo1.Text & " Where " _
& Combo2.Text & " " & Combo3.Text & " '" & Text2.Text & "*'"
End If
derr:
If Err.Number <> 0 Then
MsgBox Err.Number & vbCrLf & _
Err.Description
End If
End Sub
Private Sub Text4_Change()
On Error GoTo derr
If IsNumeric(Text3.Text) = False Then
Text3.Text = "Select * from " & Combo1.Text & " Where " _
& Combo2.Text & " " & Combo3.Text & " '" & Text4.Text & "*'"
End If
Command2_Click
derr:
If Err.Number <> 0 Then
MsgBox Err.Number & vbCrLf & _
Err.Description
End If
End Sub
