-
Jun 21st, 2018, 08:22 AM
#1
Thread Starter
New Member
search a TABLE in MS ACCESS
good day everyone! i am trying to make a program for patients information for a clinic. Every patient has its own table for their own records inside and the program i used is working properly but the problem is i dont know what is the code for searching a table.
this is my code for form1 ; adding a new patient
Dim Con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Public Function MakeATable()
Dim db As DAO.Database
Dim table_def As Object
Dim fld1, fld2, fld3, fld4, fld5 As DAO.Field
Dim tbname As String
tbname = Text1.Text & Space(1) & Text2.Text & Space(1) & Text3.Text
Set db = OpenDatabase("D:\ALLPATIENTS.mdb")
Set table_def = db.CreateTableDef
table_def.Name = tbname
Set fld1 = table_def.CreateField("Age", dbText)
table_def.Fields.Append fld1
Set fld2 = table_def.CreateField("Address", dbText)
table_def.Fields.Append fld2
Set fld3 = table_def.CreateField("First Visit", dbText)
table_def.Fields.Append fld3
Set fld4 = table_def.CreateField("Notes", dbText)
table_def.Fields.Append fld4
Set fld5 = table_def.CreateField("Diagnosis", dbText)
table_def.Fields.Append fld5
db.TableDefs.Append table_def
End Function
Private Sub Command2_Click()
Dim pname As String
pname = Label10.Caption
rs.Open "Select * from [" & pname & "]", Con, adOpenDynamic, adLockPessimistic
rs.AddNew
rs.Fields("Age").Value = Text4.Text
rs.Fields("Address").Value = Text5.Text
rs.Fields("First Visit").Value = Label8.Caption
rs.Fields("Notes").Value = Text6.Text
rs.Fields("Diagnosis").Value = Text7.Text
rs.Update
MsgBox "New Patient Information Recorded!"
Clear
Unload Me
End Sub
Private Sub Command1_Click()
MakeATable
Label4.Visible = True
Label5.Visible = True
Label6.Visible = True
Label7.Visible = True
Label8.Visible = True
Label9.Visible = True
Text4.Visible = True
Text5.Visible = True
Text6.Visible = True
Text7.Visible = True
Command1.Visible = False
Command2.Visible = True
Label10.Caption = Text1.Text & Space(1) & Text2.Text & Space(1) & Text3.Text
End Sub
Private Sub Form_Load()
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ALLPATIENTS.mdb;Persist Security Info=False"
End Sub
Sub Clear()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Label8.Caption = ""
Text6.Text = ""
Text7.Text = ""
Label10.Caption = ""
End Sub
Private Sub Timer1_Timer()
Label8.Caption = Date
End Sub
and for form2 i want to search a table and display all the records inside a table
-
Jun 21st, 2018, 09:06 AM
#2
Fanatic Member
Re: search a TABLE in MS ACCESS
1) FWIW: It would make it easier for someone to help you if the code was separately identified from your request (use #) and the code was also indented.
2) In regard to:
and for form2 i want to search a table and display all the records inside a table
These are two different actions. Look in Access help for:
a) SEEK and NOMATCH to search a table.
b) Do / Loop to display all records.
c) You may want to check .BOF and .EOF to see if table is empty (has no records) first.
-
Jun 21st, 2018, 09:44 AM
#3
Re: search a TABLE in MS ACCESS
You seem to be using DAO, so the following statements seem to be pointless.
Or are you really mixing them while both DAO and ADO have an open connection to the MDB file?
Code:
Dim Con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub Form_Load()
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ALLPATIENTS.mdb;Persist Security Info=False"
End Sub
The following code does not what you expected:
Code:
Dim fld1, fld2, fld3, fld4, fld5 As DAO.Field
fld1, fld2, fld3, fld4 are Variants only fld5 is dimmed as DAO.Field
The correct line would be:
Code:
Dim fld1 As DAO.Field, fld2 As DAO.Field, fld3 As DAO.Field, fld4 As DAO.Field, fld5 As DAO.Field
-
Jun 21st, 2018, 10:14 AM
#4
Thread Starter
New Member
Re: search a TABLE in MS ACCESS
thanks guy but the codes i have given above are working properly, no problem at all. all i need is another code for searching a table. i simply dont have idea
-
Jun 21st, 2018, 10:45 AM
#5
Re: search a TABLE in MS ACCESS
> "Every patient has its own table for their own records"
Terrible idea, even in Access.
How do you intended to search across patients?
You could write a loop to search ea Patient table in turn, but how do you know what they're all called?
One table for all patients, properly structured and indexed, will do the job perfectly well.
> "Set fld1 = table_def.CreateField("Age", dbText)"
Do not store Age.
Unless you intended to [reliably] run a process every single night of the year to work out who's had a birthday (the date of which you're not actually storing) and increment their age, this just isn't going to work. Instead, store the Date Of Birth for each patient and calculate their Age as and when you need it.
Code:
select * from Patients ;
+----+-----------------+---------------+---------+
| id | name | date_of_birth | address |
+----+-----------------+---------------+---------+
| 1 | Fred Flintstore | 01/01/0001 | Bedrock |
+----+-----------------+---------------+---------+
select * from Appointments ;
+----+------------+---------------------+----------+--------------------+
| id | patient_id | appt_time | notes | diagnosis |
+----+------------+---------------------+----------+--------------------+
| 22 | 1 | 01/01/0055 12:30:00 | Headache | Removed woodpecker |
+----+------------+---------------------+----------+--------------------+
-- As run on 21/06/0056
select p.name
, int( now - p.date_of_birth ) age
, a.appt_time
, a.notes
, a.diagnosis
from Patients p
inner join Appointments a
on a.patient_id = p.id
where p.name = 'Fred Flintstone'
order by 1, 3 ;
+-----------------+-----+---------------------+----------+--------------------+
| name | age | appt_time | notes | diagnosis |
+-----------------+-----+---------------------+----------+--------------------+
| Fred Flintstone | 55 | 01/01/0055 12:30:00 | Headache | Removed woodpecker |
+-----------------+-----+---------------------+----------+--------------------+
To search the table, you need to know which field(s) to look in and what to look for.
For example, in the above query, I'm finding Fred by name. I could do that by patient ID, or appointment date or by [some] part of the notes or diagnosis (although that's potentially very, very slow).
Give us an idea of what you need to search by and we'll see what we can come up with.
Regards, Phill W.
-
Jun 21st, 2018, 10:52 AM
#6
Re: search a TABLE in MS ACCESS
renziebass,
Arnoutdv speaks truth to you. You seem to be mixing the DAO and ADO technologies, which is a very bad idea. I use both frequently, but the two shall never meet the way I use them.
I tend to stick with the DAO for MS-Access type databases, just because it's deeply embedded in my primary application, and it does all I could ever ask when using an MS-Access type database. However, if you ever wish to jump to SQL servers, the ADO is a necessity. Again, don't mix, or I just can't imagine that you won't have problems.
Now, beyond the way you actually get your database open, the two are quite similar. The most notable difference I know of is that the ADO doesn't require the .Edit method before editing a record, whereas that's a requirement in the DAO.
And, regarding searching for a record, as outlined by vb6forever, you've got two choices: 1) spin through all the records with .MoveFirst, .MoveNext, checking for .EOF along the way, or 2) create an index and then use the .Seek method along with the .NoMatch property.
The second approach (index, .Seek, .NoMatch) will be faster, and progressively so as the number of records in your table grows. Also, when using .MoveFirst, you must always put it in a block that tests for .RecordCount<>0, or you'll get an error on the .MoveFirst call. Whereas a .Seek won't care.
The MSDN for both the DAO (found here) and the ADO (found here) is completely available.
Best Of Luck,
Elroy
Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.
-
Jun 21st, 2018, 11:00 AM
#7
Thread Starter
New Member
Re: search a TABLE in MS ACCESS
Elroy & Phill.W thank you so much to the both of you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|