Results 1 to 7 of 7

Thread: search a TABLE in MS ACCESS

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2018
    Posts
    11

    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

  2. #2
    Fanatic Member
    Join Date
    Feb 2017
    Posts
    532

    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.

  3. #3
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    3,889

    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

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2018
    Posts
    11

    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

  5. #5
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    671

    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.

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,472

    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. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jun 2018
    Posts
    11

    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
  •  



Click Here to Expand Forum to Full Width