Solved

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

Posted on 2000-03-07
4
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 9

Accepted Solution

by:
Dalin earned 150 total points
ID: 2593932
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
ID: 2594156
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
ID: 2594680
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
ID: 2598835
Thanks - it helped me out
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

734 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