?
Solved

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

Posted on 2000-03-07
4
Medium Priority
?
161 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 450 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 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