[Raaj71]

Hi !

I need help to connect MS Access Database. I dont want to use Data Control.

Can anyone please guide me the simplest way to connect database and navigate through records ?

Thanks in Advance.

Raj

[maj3091]

Try looking in the tutorials section to begin with.

[Raaj71]

maj3091, on 22 October 2011 - 02:22 AM, said:

Try looking in the tutorials section to begin with.

Sorry Mr. Maj3091, I could not find any topic in tutorials section which could answer my simple question.

I want to connect access database using ado. i have a database file having 3 fields and i want to display data of these three fields in text boxes on the form along with navigation buttons.

Can any one provide me help with connection ?

[BobRodes]

Try This and see if it helps you.

[Raaj71]

BobRodes, on 22 October 2011 - 08:53 PM, said:

Try This and see if it helps you.

Dear BobRodes

I already googled for it returned several pages which contains material for experienced users who already have knowledge of vb.

Hope, someone here will come out and share his/her knowledge with new bees too.

Tonns of Thanks for your EXPERT ADVICE.

[maj3091]

If you look at the first two results returned by Bob's link, you'll find that they're pretty basic, which is where you need to start. They explain the connection method and navigation methods and even go onto queries.

Did you actually look at any of the links? The W3Schools one for example, is like a basic tutorial. If you followed the example given, it will explain to you all you need to know.

If you're saying they're too advanced for you, I'm not sure what you want people to tell you?

[BobRodes]

Raaj71, on 23 October 2011 - 05:25 AM, said:

BobRodes, on 22 October 2011 - 08:53 PM, said:

Try This and see if it helps you.

Dear BobRodes

I already googled for it returned several pages which contains material for experienced users who already have knowledge of vb.

Hope, someone here will come out and share his/her knowledge with new bees too.

Tonns of Thanks for your EXPERT ADVICE.

Seriously? You've been just killing yourself trying to figure out all those pages you googled, and now as a last resort, you come to the experts for their expert advice. Baloney.

My expert advice is this: quit relying on other people to do your work for you. Get as far as you can before you ask for help. If as far as you can get is nowhere, then quit programming and find something else to do with your time. Note that I'm entirely convinced that you can work this out on your own, or with very little guidance, once you quit trying to get ahead by panhandling from the people who actually put some effort into figuring out things for themselves.

Hope that helps you.

[Raaj71]

thanks everyone.

I got success with this little thing (although it was big for me with no programming background).

Program is working fine. Am able to access, add, edit and delete the contents of my database through coding.

After i reviewed it, I found something missing. A program is not complete if it can't search the database, so i added a search button to it.

I have few text boxes on the form which got filled automatically with the last record when the form is loaded. All buttons i.e. First, Last, Next, Previous works fine and show appropriate records of database.

I have added a textbox to collect the string for search operations. When FIND button is clicked, it shows all the records according to my search criteria. Now what happens is the data base file got limited to search results and functionality to NEXT, PREVIOUS, FIRST AND LAST buttons show only the searched records. (I assume it is creating another instance of database on my search criteria).

Here is the code :

Private Sub Form_Load()
    Set AddCn = New ADODB.Connection
    AddCn.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " + App.Path & "\Address Book.Mdb"
    AddCn.Open
    Set AddRs = New ADODB.Recordset
    With AddRs
        .Open "Address", AddCn, adOpenDynamic, adLockPessimistic, adCmdTable
        If Not (.EOF And .BOF) Then
            AddRs.MoveLast
            FillFields
            DisFields
        End If
    End With
    EnbNavButt
End Sub

'---------------------------------------------------------------------------------------------------------------
Private Sub cmdFind_Click()
    AddRs.Close
    txtSearch.Text = Trim$(txtSearch.Text)
    AddRs.Open "SELECT * FROM Address WHERE First_Name LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    If AddRs.EOF = True Or AddRs.EOF = True Then
        MsgBox ("No Record Exists")
        Exit Sub
    Else
        FillFields
    End If
End Sub

Now, if i want to refer to the first record of the database, it shows the first searched record and my navigation buttons works fine but are restricted to show the searched records.

Anyone can help ?

[BobRodes]

First, good job getting as far as you have. Now, as for find. You are correct in what you surmise. You close the recordset and open it again using a sql where clause, and it's a bit problematical to see all your data again.

It would be much simpler to use the .Filter property. This restricts the records that can be viewed to those specified in your where clause, without actually changing the values in the recordset. You use the property by setting it equal to a valid SQL WHERE clause (e. g.

myRS.Filter = "First_Name LIKE '%Bob%'"

). Then you can wander around in the recordset just as if the filter weren't there, and when you're done you can set the Filter property to "" to get back all your records.

The Filter property is documented here.

This post has been edited by BobRodes: 10 November 2011 - 11:00 AM

[Raaj71]

Ok ! I am finally done with this.

Thanks to everyone who helped me directly or indirectly.

Here is the code :

----------------------------------------------------------------
'Reference to Microsoft ActiveX Data Objects 2.8 Library Added
----------------------------------------------------------------
Option Explicit
    Private AddCn As ADODB.Connection
    Private AddRs, DupRs As ADODB.Recordset
    Public StrLen, i As Integer
    Public StrCheck, StrCheck1, Confirm As String
----------------------------------------------------------------
Private Sub Form_Load()
    Set AddCn = New ADODB.Connection
    AddCn.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " + App.Path & "\Address Book.Mdb"
    AddCn.Open
    Set AddRs = New ADODB.Recordset
    With AddRs
        .Open "Address", AddCn, adOpenDynamic, adLockPessimistic, adCmdTable
        If Not (.EOF And .BOF) Then
            AddRs.MoveLast
            FillFields
            DisFields
        End If
    End With
    EnbNavButt
    Me.MousePointer = vbNormal
    cmdClearSearch.Enabled = False
    OptFirstName = True
End Sub
----------------------------------------------------------------
Private Sub Form_Unload(Cancel As Integer)
    If Not AddRs Is Nothing Then
        If AddRs.State = adStateOpen Then
            AddRs.Close
            AddCn.Close
        End If
        Set AddRs = Nothing
    End If
    Set AddCn = Nothing
End Sub
----------------------------------------------------------------
Private Sub cmdFirst_Click()
    If AddRs.BOF = False Then
        ResetFields
        AddRs.MoveFirst
        FillFields
        DisFields
    End If
End Sub
----------------------------------------------------------------
Private Sub cmdLast_Click()
    If AddRs.EOF = False Then
        AddRs.MoveLast
        FillFields
        DisFields
    End If
End Sub
----------------------------------------------------------------
Private Sub cmdNext_Click()
    AddRs.MoveNext
    If AddRs.EOF Then
        AddRs.MoveLast
        MsgBox ("Last Record...")
    End If
    FillFields
End Sub
----------------------------------------------------------------
Private Sub cmdPrev_Click()
    If AddRs.BOF = False Then
        AddRs.MovePrevious
        If AddRs.BOF Then
            MsgBox ("This is First Record")
            AddRs.MoveNext
        End If
        FillFields
        DisFields
    End If
End Sub
----------------------------------------------------------------
Private Sub cmdPrev_LostFocus()
    cmdPrev.Enabled = True
End Sub
----------------------------------------------------------------
Private Sub cmdUpdate_Click()
    'AddRs.AddNew
    AddRs.Fields("First_Name").Value = Trim$(txtFirstName.Text)
    AddRs.Fields("Last_Name") = Trim$(txtLastName.Text)
    AddRs.Fields("Address1") = Trim$(txtAdd1.Text)
    AddRs.Fields("Address2") = Trim$(txtAdd2.Text)
    AddRs.Fields("Address3") = Trim$(txtAdd3.Text)
    AddRs.Fields("City") = Trim$(txtCity.Text)
    AddRs.Fields("State") = Trim$(txtState.Text)
    AddRs.Fields("Pin_Code") = Trim$(txtPin.Text)
    AddRs.Fields("Telephone1") = Trim$(txtPhone1.Text)
    AddRs.Fields("Telephone2") = Trim$(txtPhone2.Text)
    AddRs.Fields("Mobile1") = Trim$(txtMbl1.Text)
    AddRs.Fields("Mobile2") = Trim$(txtMbl2.Text)
    AddRs.Update
    DisFields
    EnbNavButt
End Sub
----------------------------------------------------------------
Private Sub cmdAddNew_Click()
    ResetFields
    EnbFields
    DisNavButt
    txtFirstName.SetFocus
    AddRs.AddNew
End Sub
----------------------------------------------------------------
Private Sub cmdClearSearch_Click()
    AddRs.Close
    AddRs.Open "SELECT * FROM Address"
    AddRs.MoveLast
    FillFields
End Sub
----------------------------------------------------------------
Private Sub cmdDelete_Click()
    Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
    If Confirm = vbYes Then
        AddRs.Delete
        MsgBox "Record Deleted!", , "Message"
        If AddRs.EOF = True Then
            AddRs.MovePrevious
        Else
            AddRs.MoveNext
        End If
        FillFields
    End If
End Sub
----------------------------------------------------------------
Private Sub cmdEdit_Click()
    If MsgBox("Are you sure you want to Edit this record?", _
    vbYesNo + vbQuestion, "Edit") = vbNo Then
        Exit Sub
    End If
    EnbFields
    DisNavButt
    txtFirstName.SetFocus
    cmdUpdate.Enabled = True
End Sub
----------------------------------------------------------------
Private Sub cmdExit_Click()
    If MsgBox("Are you Sure ?", vbYesNo + vbQuestion, "Exit") = vbYes Then
        Set AddRs = Nothing
        Set AddCn = Nothing
        Unload Me
    End If
End Sub
----------------------------------------------------------------
Private Sub cmdFind_Click()
    AddRs.Close
    If txtSearch.Text = vbNullString Then
        MsgBox ("Please tell me what to find...")
        txtSearch.SetFocus
    End If
    txtSearch.Text = Trim$(txtSearch.Text)
    If OptFirstName = True Then
        AddRs.Open "SELECT * FROM Address WHERE First_Name LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    ElseIf OptLastName = True Then
        AddRs.Open "SELECT * FROM Address WHERE Last_Name LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    ElseIf OptCity = True Then
        AddRs.Open "SELECT * FROM Address WHERE City LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    ElseIf OptMobile = True Then
        AddRs.Open "SELECT * FROM Address WHERE mobile1 LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    End If
    If AddRs.EOF = True Or AddRs.EOF = True Then
        MsgBox ("No Record Exists")
        txtSearch.Text = vbNullString
        Exit Sub
    Else
        txtSearch.Text = vbNullString
            FillFields
    End If
End Sub
----------------------------------------------------------------
Public Sub FillFields()
    If Not (AddRs.BOF = True Or AddRs.EOF = True) Then
        If IsNull(AddRs("First_Name")) Then
            txtFirstName.Text = vbNullString
        Else
            txtFirstName.Text = AddRs.Fields("First_Name")
        End If
        
        If IsNull(AddRs("Last_Name")) Then
            txtLastName.Text = vbNullString
        Else
            txtLastName.Text = AddRs.Fields("Last_Name")
        End If
        
        If IsNull(AddRs("Address1")) Then
            txtAdd1.Text = vbNullString
        Else
            txtAdd1.Text = AddRs.Fields("Address1")
        End If
        
        If IsNull(AddRs("Address2")) Then
            txtAdd2.Text = vbNullString
        Else
            txtAdd2.Text = AddRs.Fields("Address2")
        End If
        
        If IsNull(AddRs("Address3")) Then
            txtAdd3.Text = vbNullString
        Else
            txtAdd3.Text = AddRs.Fields("Address3")
        End If
        
        If IsNull(AddRs("City")) Then
            txtCity.Text = vbNullString
        Else
            txtCity.Text = AddRs.Fields("City")
        End If
        
        If IsNull(AddRs("State")) Then
            txtState.Text = vbNullString
        Else
            txtState.Text = AddRs.Fields("State")
        End If
        
        If IsNull(AddRs("Pin_Code")) Then
            txtPin.Text = vbNullString
        Else
            txtPin.Text = AddRs.Fields("Pin_Code")
        End If
        
        If IsNull(AddRs("Telephone1")) Then
            txtPhone1.Text = vbNullString
        Else
            txtPhone1.Text = AddRs.Fields("Telephone1")
        End If
        
        If IsNull(AddRs("Telephone2")) Then
            txtPhone2.Text = vbNullString
        Else
            txtPhone2.Text = AddRs.Fields("Telephone2")
        End If
        
        If IsNull(AddRs("Mobile1")) Then
            txtMbl1.Text = vbNullString
        Else
            txtMbl1.Text = AddRs.Fields("Mobile1")
        End If
        
        If IsNull(AddRs("Mobile2")) Then
            txtMbl2.Text = vbNullString
        Else
            txtMbl2.Text = AddRs.Fields("Mobile2")
        End If
    Else
        txtFirstName.Text = vbNullString
        txtLastName.Text = vbNullString
        txtAdd1.Text = vbNullString
        txtAdd2.Text = vbNullString
        txtAdd3.Text = vbNullString
        txtCity.Text = vbNullString
        txtState.Text = vbNullString
        txtPin.Text = vbNullString
        txtPhone1.Text = vbNullString
        txtPhone2.Text = vbNullString
        txtMbl1.Text = vbNullString
        txtMbl2.Text = vbNullString
    End If
    DisFields
End Sub
----------------------------------------------------------------
Private Sub EnbNavButt()
    cmdFirst.Enabled = True
    cmdPrev.Enabled = True
    cmdNext.Enabled = True
    cmdLast.Enabled = True
    cmdAddNew.Enabled = True
    cmdEdit.Enabled = True
    cmdDelete.Enabled = True
    cmdUpdate.Enabled = False
End Sub
----------------------------------------------------------------
Private Sub DisNavButt()
    cmdFirst.Enabled = False
    cmdPrev.Enabled = False
    cmdNext.Enabled = False
    cmdLast.Enabled = False
    cmdAddNew.Enabled = False
    cmdEdit.Enabled = False
    cmdDelete.Enabled = False
    cmdUpdate.Enabled = False
    cmdClearSearch.Enabled = False
End Sub
----------------------------------------------------------------
Private Sub ResetFields()
    txtFirstName.Text = vbNullString
    txtLastName.Text = vbNullString
    txtAdd1.Text = vbNullString
    txtAdd2.Text = vbNullString
    txtAdd3.Text = vbNullString
    txtCity.Text = vbNullString
    txtState.Text = vbNullString
    txtPin.Text = vbNullString
    txtPhone1.Text = vbNullString
    txtPhone2.Text = vbNullString
    txtMbl1.Text = vbNullString
    txtMbl2.Text = vbNullString
End Sub
----------------------------------------------------------------
Private Sub DisFields()
    txtFirstName.Enabled = False
    txtLastName.Enabled = False
    txtAdd1.Enabled = False
    txtAdd2.Enabled = False
    txtAdd3.Enabled = False
    txtCity.Enabled = False
    txtState.Enabled = False
    txtPin.Enabled = False
    txtPhone1.Enabled = False
    txtPhone2.Enabled = False
    txtMbl1.Enabled = False
    txtMbl2.Enabled = False
End Sub
----------------------------------------------------------------
Private Sub EnbFields()
    txtFirstName.Enabled = True
    txtLastName.Enabled = True
    txtAdd1.Enabled = True
    txtAdd2.Enabled = True
    txtAdd3.Enabled = True
    txtCity.Enabled = True
    txtState.Enabled = True
    txtPin.Enabled = True
    txtPhone1.Enabled = True
    txtPhone2.Enabled = True
    txtMbl1.Enabled = True
    txtMbl2.Enabled = True
End Sub
----------------------------------------------------------------
Private Sub txtFirstName_LostFocus()
    If txtFirstName.Text = vbNullString Then
        MsgBox ("First Name cannot be left blank.... Please fill in First Name")
        txtFirstName.SetFocus
    End If
End Sub
----------------------------------------------------------------
Private Sub txtMbl2_LostFocus()
    cmdAddNew.Enabled = False
    cmdUpdate.Enabled = True
End Sub

Above code is now working perfectly. I have added a button to clear search criteria set by SELECT - WHERE clause by coding that button with Select * FROM Address.Mdb

Now I just have few doubts, if anyone can clear please.

1. Is there any other better way than (FIND) for search operations on databases.

2. I want the program to break its execution (Where ever it is and whatever it is doing) without Unloading form (Since there is only one form) by using escape key and return it to the position when the form gets loaded (i.e. the beginning) of the program where it loads the last record and displays it on the form in text boxes. How can i code event for ESCAPE key to behave like this ?

[BobRodes]

1. Sure. Just read my post, even if you're still mad at me.
2. Start with setting the form's key preview property to true, then use the keypress event to catch the escape key. There's more, but you'll want to work that out first.

Also, while you deserve to be commended on getting your code to work (as well as for the neatness of your layout), there are more concise solutions that suggest themselves.

        If IsNull(AddRs("First_Name")) Then
            txtFirstName.Text = vbNullString
        Else
            txtFirstName.Text = AddRs.Fields("First_Name")
        End If
 

can be written

txtFirstName.Text = AddRs("First_Name") & ""

It's a neat trick: concatenating a blank string to a text leaves it as is, and concatenating "" (or vbNullString, if you prefer) to a null gives "". This also avoids assigning a null value from a database to a text box.

Also, all this

Private Sub DisFields()
    txtFirstName.Enabled = False
    txtLastName.Enabled = False
    txtAdd1.Enabled = False
    txtAdd2.Enabled = False
    txtAdd3.Enabled = False
    txtCity.Enabled = False
    txtState.Enabled = False
    txtPin.Enabled = False
    txtPhone1.Enabled = False
    txtPhone2.Enabled = False
    txtMbl1.Enabled = False
    txtMbl2.Enabled = False
End Sub
----------------------------------------------------------------
Private Sub EnbFields()
    txtFirstName.Enabled = True
    txtLastName.Enabled = True
    txtAdd1.Enabled = True
    txtAdd2.Enabled = True
    txtAdd3.Enabled = True
    txtCity.Enabled = True
    txtState.Enabled = True
    txtPin.Enabled = True
    txtPhone1.Enabled = True
    txtPhone2.Enabled = True
    txtMbl1.Enabled = True
    txtMbl2.Enabled = True
End Sub

can be reduced to

Private Sub ToggleTextEnable(TF as Boolean)
Dim cCtl as Control
For Each cCtl in Me.Controls
    If TypeOf cCtl is TextBox Then
        cCtl.Enabled = TF
    End If
Next

Of course, this assumes that you want to enable or disable all of your text boxes at once. If not, you can apply the same technique to a control array, or add a design time value to the Tag property which you can also evaluate in your for each loop.

Another even cooler use of the Tag property here occurs to me. If you set each of your text boxes' tag property to the name of the field it's supposed to represent (at design time, e. g. set txtFirstName.Tag to "First_Name"), then you can replace this

Public Sub FillFields()
    If Not (AddRs.BOF = True Or AddRs.EOF = True) Then
        If IsNull(AddRs("First_Name")) Then
            txtFirstName.Text = vbNullString
        Else
            txtFirstName.Text = AddRs.Fields("First_Name")
        End If
        
        If IsNull(AddRs("Last_Name")) Then
            txtLastName.Text = vbNullString
        Else
            txtLastName.Text = AddRs.Fields("Last_Name")
        End If
        
        If IsNull(AddRs("Address1")) Then
            txtAdd1.Text = vbNullString
        Else
            txtAdd1.Text = AddRs.Fields("Address1")
        End If
        
        If IsNull(AddRs("Address2")) Then
            txtAdd2.Text = vbNullString
        Else
            txtAdd2.Text = AddRs.Fields("Address2")
        End If
        
        If IsNull(AddRs("Address3")) Then
            txtAdd3.Text = vbNullString
        Else
            txtAdd3.Text = AddRs.Fields("Address3")
        End If
        
        If IsNull(AddRs("City")) Then
            txtCity.Text = vbNullString
        Else
            txtCity.Text = AddRs.Fields("City")
        End If
        
        If IsNull(AddRs("State")) Then
            txtState.Text = vbNullString
        Else
            txtState.Text = AddRs.Fields("State")
        End If
        
        If IsNull(AddRs("Pin_Code")) Then
            txtPin.Text = vbNullString
        Else
            txtPin.Text = AddRs.Fields("Pin_Code")
        End If
        
        If IsNull(AddRs("Telephone1")) Then
            txtPhone1.Text = vbNullString
        Else
            txtPhone1.Text = AddRs.Fields("Telephone1")
        End If
        
        If IsNull(AddRs("Telephone2")) Then
            txtPhone2.Text = vbNullString
        Else
            txtPhone2.Text = AddRs.Fields("Telephone2")
        End If
        
        If IsNull(AddRs("Mobile1")) Then
            txtMbl1.Text = vbNullString
        Else
            txtMbl1.Text = AddRs.Fields("Mobile1")
        End If
        
        If IsNull(AddRs("Mobile2")) Then
            txtMbl2.Text = vbNullString
        Else
            txtMbl2.Text = AddRs.Fields("Mobile2")
        End If
    Else
        txtFirstName.Text = vbNullString
        txtLastName.Text = vbNullString
        txtAdd1.Text = vbNullString
        txtAdd2.Text = vbNullString
        txtAdd3.Text = vbNullString
        txtCity.Text = vbNullString
        txtState.Text = vbNullString
        txtPin.Text = vbNullString
        txtPhone1.Text = vbNullString
        txtPhone2.Text = vbNullString
        txtMbl1.Text = vbNullString
        txtMbl2.Text = vbNullString
    End If
    DisFields
End Sub

with this

Public Sub FillFields()
Dim cCtl as Control
With AddRs
    If .BOF and .EOF then
        For Each cCtl in Me.Controls
            If TypeOf cCtl is TextBox then
                cCtl.Text = ""
            End If
        Next
    Else    
        For Each cCtl in Me.Controls
            If TypeOf cCtl is TextBox Then
                cCtl.Text = .Fields(cCtl.Tag) & ""
            End If
        Next
    End If
End With
ToggleTextEnable False
End Sub

It's better to check for empty recordset outside the loop, even though you have to have two for each statements that way.

This post has been edited by BobRodes: 15 November 2011 - 09:08 AM

[Raaj71]

@ Bob Rodes

Thanks for evaluating my code deeply and suggesting me tips and techniques to reduce the code. As I said earlier, am a beginner. I don't believe in mere copying the piece of code and us in my programs, rather I use to write the pieces of code I get from net on the paper and try to understand it first, So that I can apply the concept in my programs at various different conditions. I read somewhere about creating an array of text boxes so that disabling, enabling or resetting of values can be done through a loop. I didn't spent much time on discovering it deeply cause my priority at that time was How to connect to database(s).

I was making an testing program to check my knowledge and skills. I intend to make a program for my business through which I will track Orders Received, Order Processing Status, Delivery Date, Advance Received and Balance due.

Now I am done with testing part i.e. accessing more than one table from vb, it was real troublesome but any how i learnt it with help of some people online, who shared their knowledge and guided me with detailed answers, checking my code, at codguru.

Am gonna start my project soon and surely your advice will help in making my code shorter neat and clean.


Regards

[BobRodes]

There you go. By the way, to make control arrays, you just create a bunch of controls of the same type (say, text boxes) and give them all the same name. Then, you set the Index property sequentially for each text box (the first one has an index of 0, the next 1, and so on). This happens automatically if you create one text box, copy it, and do a paste once for each control that you want.

When you do this, they all share the same event procedures. You'll notice that each event procedure will have an Index argument added to it automatically, and this will be equal to the index property of the control raising the event.

[Raaj71]

Well ! Thanks a lot for the support.

Am all set to start it my project. I just wanted to know, if I am allowed to start a new topic and share my day to day progress (starting from scratch) regarding that project. I guess, It will be helping me and many users like me who are learning or want to learn VB6 programming.

Am expecting to learn various newt things this way. Many of you experts have done/been doing it for long (and may have invented various things which you would like to share with inexperienced people) and if am going wrong somewhere, I can get help from your experience?

Regards

[BobRodes]

Feel free to ask for better ways to do things once you've done them. Also, if you have problems, then post the relevant code, any error message you're getting, and the line of code creating the error.