We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Databases in Visual Basic

Medium Priority
704 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
Comment
Watch Question

CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.