[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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

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
neilmcaliece
Asked:
neilmcaliece
  • 2
1 Solution
 
DalinCommented:
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
 
neilmcalieceAuthor Commented:
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
 
wsh2Commented:
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
 
neilmcalieceAuthor Commented:
Thanks - it helped me out
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now