Storing, retrieving, finding and deleting details from Access Database

[Victor_TanTW]

Hi all. I'm building a buffer reference list like for keeping track of the books taken out. Here's the step-by-step process.

1. Item has been scanned by reader.
2. Deduct one item from inventory program and add the tag ID and content to reference list.
3. If item is not the intended item, returns back to the inventory.
4. Reader detects the same ID and content.
5. Search for the ID in the reference list.
6. Delete the data from the reference list and add one item back to the inventory program.
7. Clear all data in the reference list upon program shut down.

For this case, can I use MS Access Database to handle this? If yes, how can I add the data into the database automatically whereby a tag is present? Is there a way to search for data if the same ID and content is display? Is there a way to clear all data in the database upon program shut down?

Thank you.

[robbedaya]

- All af this can be done with an MS Access Database.
- You need to look for a tutorial about ADO. ADO allows you to save, update, delete items from any database. (ADO Tutorial)

[Victor_TanTW]

Ok thanks I'll look into it

[Victor_TanTW]

After looking into it, I've solved some of the problems. But there are still some problems left I'm not quite understand or not sure of how to do it.

Problems like automatically do a search in the reference list records to check if a tag ID has been read again, deleting the the records from the list if similar tag ID has been found in records, and finally clear all the records when you close the application, making sure that all records have been cleared the next time the application runs.

Thank you

[robbedaya]

Problems like automatically do a search in the reference list records to check if a tag ID has been read again

Every time tou read a current tag. Set a bit/boolean-field true. So you knwo that that record has been read.

deleting the the records from the list if similar tag ID has been found in records

- "Select * FROM tableName WHERE TagID = '" & tagToDelete & "' "
- If .bof =false and .eof =false then -> remove from list.

and finally clear all the records when you close the application, making sure that all records have been cleared the next time the application runs.

1. Open recorset
Loop throug recordset, and delete every record invidually
OR
2. execute following query -> DELETE FROM tableName
this drops all the records.

Thank you[/QUOTE]

[Victor_TanTW]

I have solved most of the problems with the suggestions you provided. But I still left with one more problem.

I can able to search for the particular information using the following code

datReference.Recordset.Filter = txtTagID.Text

It did got the records I wanted. I tried to make it delete the searched records from the reference list but it deletes the wrong records. It's like for example I have 5 records in the reference list. the program searched for record no. 3. My intention was to delete record no. 3 after it has searched for it and processed. But the program deleted the last record of the list. Any solution on that? Thanks

[robbedaya]

Please post the code you use to delete.

[Victor_TanTW]

Here you go. This is a portion of the code that takes care of the particular stock. The rest of the stocks uses the same code as this one.

Public Sub HarryProcess()

    'Harry Potter Books
    'Deduct one book from Harry Potter if Harry tag is present
    If frmBooks.chkHarryOut.Value = 0 Then
        frmBooks.txtHarryStock.Text = frmBooks.txtHarryStock.Text - 1
        frmBooks.datReference.Recordset.MoveLast
        iCount = iCount + 1
        
        'Threshold Reached
        If frmBooks.txtHarryStock.Text = frmBooks.txtHarryThreshold.Text Then
            frmBooks.txtStatus.Text = "Threshold Reached. Please top up Harry Potter books now!"
        End If
        
        'Locking down at zero if tag is present after zero is display
        If frmBooks.txtHarryStock <= 0 Then
            frmBooks.txtStatus.Text = "Harry Potter books out of stock!"
            frmBooks.txtHarryStock.Text = "0"
        End If
        
        'Put a tick at the database when the tag is taken out
        frmBooks.chkHarryOut.Value = 1
        
    'If checkBox is not equal to 0
    Else
        frmBooks.datReference.Recordset.Filter = frmBooks.txtTagID.Text
        
        'Increment the stock if tag is read once again
        If frmBooks.txtTagID.Text = frmBooks.txtHarryID.Text Then
            frmBooks.txtHarryStock.Text = frmBooks.txtHarryStock.Text + 1
            frmBooks.chkHarryOut.Value = 0
            iCount = iCount - 1
            frmBooks.datReference.Recordset.Delete
            frmBooks.datReference.Recordset.MoveFirst
        End If
    End If
    
End Sub

Please let me know if I've used the wrong code. Thanks.

[Victor_TanTW]

Bringing up my thread. I left this problem left. Thanks.

[ganeshmoorthy]

I would suggest you to use the unbound methods as the bound controls are evil, and yes, I never used bound controls so, Im not sure what is going wrong here...
Try to use SQL statements for filtering and deleting so, you wont face any problems as you will have full control over your code

[Victor_TanTW]

Does SQL goes well with VB 6? On the other hand I'm not sure about that as my supervisor instructed me to use only MS Access database to do a simple inventory control, that's all.

[zeezee]

Does SQL goes well with VB 6? On the other hand I'm not sure about that as my supervisor instructed me to use only MS Access database to do a simple inventory control, that's all.

Well victor you are a beginer. And I m blaming that Instructor 'cause he asked you to build a system without teaching you about the Basic concept of Data Base Mangement Systems (DBMS)

SQL stands for - Structured Query Languauge -try to google this if you need more info
We use SQL to comunicate with DMBSs. (Access is a DBMS , other MySQl, SQL Server, Oracle, etc ...)

The Basics of SQL commands are INSERT, UPDATE ,SELECT,DELETE queries

If you create a ADODB conncetion, then you can get connected to the DB and send these commands (SQL queries) to interact with the DB.

 Public dbcon As ADODB.Connection ' make it public so can be accessed  anywhere in the project

private sub ConncettoDB
    Set dbcon = New ADODB.Connection
  
    dbcon.CursorLocation = adUseClient
    dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data  Source=ControlList.mdb;" ' ControlList.mdb - the Access File. give the path relatively to you project folder or a fixed loaction

end sub

so the conncetion is made now. then you can get results from the DB (ControlList.mdb)

Public Function retrivedata()

     Dim strsql As String
     Dim rst1 As New ADODB.Recordset
     Dim i As Integer
    
    strsql = " SELECT * FROM tbl_test WHERE BookID = 1"
    'Read the statement and understand what it says
    'SELECT all records(*) FROM the table tbl_test WHERE the each record's BookID column value = 1'
    'simple huh?

    rst1.Open strsql, dbcon ' remember the connection we created?
    ' use it to connect to the DB, execute our SQL statment (strsql) and get the results to ADODB.Recordset object so we can see the data
    
    If rst1.RecordCount > 0 Then
        For i = 1 To rst1.RecordCount
            MsgBox "My Books are" & rst1.Fields("BookName") ' BookName  is a column in table tbl_test
        Next
    
    Else
        MsgBox "No such records"
    End If

End Function

Like this you can get records (SELECT), add records (INSERT), Change Records (UPDATE), remove records (DELETE), etc .... from the DB

So try to google and get you assigment right.
And ask your INSTRUCTOR to INSTRUCT you about SQL and DBMS also

you can find about the syntaxes all over the net, cause Internet is made of them. (Now dont get confused)

GOOD LUCK!

PS:
1. IF you already know these stuff, forget all this
2. IF you are Confused , again forget this

[Victor_TanTW]

Lol actually someone has done phase 1 of the project before I started. I'm working on phase 2 of the project. I looked through what they have did with the database and they used simple codes like recordset.addnew or recordset.moveFirst etc. But the program was a bit of a mess so I decided to re-do that program again, retaining some of the codes they've used. There will have phase 3 of the project which someone else is going to do it but that's still long.

Back on the topic, I tried your code. It works alright but an error message pop pop out when I attempted to scan the tag again.

"The connection cannot be used to perform this operation. It is either closed or invalid in this context."

Code affected

rst1.Open strsql, dbcon

[zeezee]

"The connection cannot be used to perform this operation. It is either closed or invalid in this context."

Code affected

rst1.Open strsql, dbcon

You have to open the connection before using it.
Say you put the connection method into a module (modMain)

you create a sub Called sub Main
this is the starting point of the project. you have to set the starting point through
Project -> Properties -> Startup Object menu (Or right click in ProjectName in the Project Explorer -> Properties)

in this sub main, call the db connetion method ConncettoDB

If you have a different Startup position, call this method in that place, before using it for any recordsets or command objects.

if you have a main Form, call the method in this form.
You can check the state of your connection using
dbcon.State property

if dbcon.State = adStateClosed then ' connection is closed
if dbcon.State = adStateOpen then ' connection is Open


Another thing, you have to close the objects you create before using it again or exiting the project
when project is closing, close the db connection

    dbcon.Close
    Set dbcon = Nothing

and for recordsets also after using it, close the object and release all the resources it holds.

        
        rst1.Close
        Set rst1 = Nothing

Keep this as a practise so you wont get any memory leaks or undesierd values in your program.

[Victor_TanTW]

Thanks. I'll try out your suggestion. Will let you know the outcome.

[kobee2]

Hi Everyone, now im the new student(dunno programming) to continue on with this project. Here is the instruction down from my teacher. He wanted the program to be able to add in more books into the database. pls help ~

[kobee2]

hi what i know from the program i have:

That 1 book per database and after it was execute when a tag is read it will -1 stock from the database as well as in the program.

What i needed to do is to be able to add in more inventory of the books. Meaning all books(any kind books) to 1 database and be able to -1 stock from the program and in database when executed with the tag. Hope i can get advise from experts or anyone thx~~

[zeezee]

kobee2 Welcome to the Forums.

Its better to start a new thread stating what you need to do with the project. People here have short and very slow memory. Perhaps better to refresh our memory with your data