Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Databases in Visual Basic

Posted on 1997-05-12
1
Medium Priority
?
630 Views
Last Modified: 2013-11-25
I am trying to use the data manager to create a database of stock (barcode, price, description, number of stock,  etc..) and I don`t know how to get Visual Basic to enter the data base and read the info and then update the number of stock after a sale.
Thanks in advance
Daniel Journo
daniel@journo.nwnet.co.uk
0
Comment
Question by:danieljourno
1 Comment
 
LVL 7

Accepted Solution

by:
tward earned 200 total points
ID: 1426221
Below is a set of functions that I created so I could just add it to a project and open databases and create dynasets.

To Do updates easily just look up the ExecuteSql method..

  Option Explicit
 
  Public SQL$
  Public DatabaseName$
 
  Public dbDatabase As Database
  Public dbWorkspace As Workspace
 
  Public DynaSetOpenError As Boolean

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  CloseDynaSet()                    '
'                                                 '
' Description:  Closes the Open Dynaset.          '
'                                                 '
'       Input:  None.                             '
'                                                 '
'      Output:  None.                             '
'                                                 '
'     Returns:  Nothing into the DynaSet Object.  '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function CloseDynaSet() As Object

  Set CloseDynaSet = Nothing

End Function


'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseClose()                   '
'                                                 '
' Description:  Closes an Open Access Database.   '
'               Database should have been opened  '
'               using OpenDatabase.               '
'                                                 '
'       Input:  NONE.                             '
'                                                 '
'      Output:  Clears Database connection '
'                                                 '
'     Returns:  NONE.                             '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub DatabaseClose()
 
  On Error Resume Next
 
  Set dbWorkspace = Nothing
  Set dbDatabase = Nothing
  Set DBEngine = Nothing

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  DatabaseOpen(DATABASE$)           '
'                                                 '
' Description:  Opens the Database Passed to it   '
'               in DATABASE.                      '
'                                                 '
'       Input:  DATABASE$ - "Path+Name of File"   '
'                                                 '
'      Output:  Message Box on Error, otherwise   '
'               an Open Database Object.          '
'                                                 '
'     Returns:   True = Database was Opened.      '
'               False = Database Open Failed.     '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function DatabaseOpen(ByVal DatabaseName$) As Boolean
 
  DatabaseOpen = True
 
  On Error Resume Next
  Set dbWorkspace = DBEngine.Workspaces(0)
  Set dbDatabase = dbWorkspace.OpenDatabase(DatabaseName$)
 
  If Err.Number <> 0 Then
   
    MsgBox "Access Error:  " & Err.Description, vbOKOnly + vbCritical, "Access Database Open Error...."
    DatabaseOpen = False
   
  End If
   
End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''
'    Function:  OpenDynaset(SQL$)                 '
'                                                 '
' Description:  Opens a Dynaset using the SQL$    '
'               SQL Statement passed to it.       '
'                                                 '
'       Input:  SQL$ - Any valid SQL Statement.   '
'                                                 '
'      Output:  An Open Dynaset, Use CloseDynaset '
'               to Close Dynasets.                '
'                                                 '
'     Returns:  The OpenDynaset Object.           '
'                                                 '
'''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function OpenDynaSet(ByVal SQL$) As Object

  On Error Resume Next

  DynaSetOpenError = False
 
  Set OpenDynaSet = dbDatabase.OpenRecordset(SQL$, dbOpenDynaset)
 
  If Err.Number <> 0 Then

    Call MsgBox("Error #" & Err.Number & ":  " & Err.Description, vbCritical + vbOKOnly, "SQL Query Error.....")

    DynaSetOpenError = True
   
  ElseIf OpenDynaSet.RecordCount = 0 Then
       
        Call MsgBox("No Records Found for Query:  " & SQL$, vbExclamation + vbOKOnly, "No Records Found.....")

        DynaSetOpenError = True
 
  Else
   
    OpenDynaSet.MoveLast
    OpenDynaSet.MoveFirst

  End If
  On Error GoTo 0

End Function

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

963 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