Solved

Reading / writing to database using variables/objects instead of form based controls

Posted on 2000-03-07
4
153 Views
Last Modified: 2013-11-26
Hi,

I'm writing a program which requires me to process access database data without using any form based controls.

I would like to read a record into a string, modify it and then write it back.

I'm using a database created in Access97

I have a table called proddata which has these fields
prodid - text
stock - long integer
preddesc - text

I'd like to read in a record where the prodid is specified, change the values of the fields using other program code and then write it back so that it's updated in the database.

Any help would be aprpecaited.

A generic example would be great - doesn't have to be tailored for the above table, I just need the method of achieving this.

Thanks

Neil


0
Comment
Question by:neilmcaliece
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
Dalin earned 150 total points
Comment Utility
Try this.  Let me know if you have any Qs.
Regards

Dim lMyDB As Database
Dim lMYRs As Recordset

'Open the database
Set lMyDB = DBEngine.OpenDatabase("YourDatabaseNameWithPAth")
Set lMYRs = lMyDB.OpenRecordset("YourTableName", dbOpenDynaset)


'Specify the prodID
lSpecificID = "ABC123"
' Now to find a record
lMYRs.FindFirst "ProdID='" & lSpecificID & "'"
If lMYRs.NoMatch = False Then
  'Find it
  'Change it
  lMYRs.Edit
  lMYRs!Stock = 124565
  lMYRs!ProdSpec = "WhatEver"
  'Saveit
  lMYRs.Update
  Else
 'Not found
  MsgBox "Record not Exist"
  End If
 
lMYRs.Close
lMyDB.Close
Set lMYRs = Nothing
Set lMyDB = Nothing



0
 

Author Comment

by:neilmcaliece
Comment Utility
I keep getting errors in the program when I try to execute it.

Dim lMyDB As Database is showing up as a user defined data type.

Note - this routing is going to be called in a subroutine that can be running more than once at the same time - just in case this affects the answer.
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Add a Reference (Menu.. Project.. Reference) to Microsoft DAO 3.51 Object Library to your project.. <smile>.

Just because I am bored.. I played with Dalin's code a bit.. and this is what I came up with..

<---- Code Begin ----->

Private Function xUpdate _
    (ByVal istrDatabase As String, _
    ByVal istrRecordset As String, _
    ByVal istrProdId As String, _
    ByVal istrStock As String, _
    ByVal istrProdSpec As String) _
    As Boolean

    Static dbDatabase As Database
    Static rsRecordset As Recordset
   
    'Open
    If istrProdId = "@OPEN" _
    Then
        On Error GoTo Tag_Database_Error
        Set dbDatabase = DBEngine.OpenDatabase(istrDatabase)
        On Error GoTo Tag_Recordset_Error
        Set rsRecordset = dbDatabase.OpenRecordset(istrRecordset, dbOpenDynaset)
        Exit Function
    End If
   
    'Close
    If istrProdId = "@CLOSE" _
    Then
        On Error GoTo Tag_Recordset_Error
        rsRecordset.Close
        Set rsRecordset = Nothing
        On Error GoTo Tag_Database_Error
        dbDatabase.Close
        Set dbDatabase = Nothing
        Exit Function
    End If
   
    If dbDatabase Is Nothing _
    Or rsRecordset Is Nothing _
    Then
        GoTo Tag_Fileopen_Error
    End If
   
    'Update
    rsRecordset.FindFirst "ProdID='" & istrProdId & "'"
    If rsRecordset.NoMatch = True _
    Then
        GoTo Tag_Notfound_Error
    End If

    On Error GoTo Tag_Update_Error
    With rsRecordset
        .Edit
        !Stock = istrStock
        !ProdSpec = istrProdSpec
        .Update
    End With

    On Error GoTo 0
    xUpdate = True
    Exit Function
   
Tag_Database_Error:
    On Error GoTo 0
    MsgBox ("xUpdate: " & istrDatabase & " Database Error")
    Exit Function
Tag_Recordset_Error:
    On Error GoTo 0
    MsgBox ("xUpdate: " & istrRecordset & " RecordSet Error")
    Exit Function
Tag_Fileopen_Error:
    On Error GoTo 0
    MsgBox ("xUpdate: No Database / Recordset Is Open")
    Exit Function
Tag_Notfound_Error:
    On Error GoTo 0
    MsgBox ("xUpdate: " & istrProdId & " Record Not Found")
    Exit Function
Tag_Update_Error:
    On Error GoTo 0
    MsgBox ("xUpdate: " & istrProdId & "Update Error")
    Exit Function

End Function

<----- Code End ----->

Enjoy
0
 

Author Comment

by:neilmcaliece
Comment Utility
Thanks - it helped me out
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now