adding data into access from vb forms

i am an amature programmer working on a inventory system and dont know how to add new items in the database.Can someone please help me.

Comments

  • : i am an amature programmer working on a inventory system and dont know how to add new items in the database.Can someone please help me.
    :
    you can use ADO or DAO, ADO is newer but DAO can offer slightly better performance since its optimized for access/jet database-

    [code]
    DAO

    'Need to set a reference to-
    'Microsoft DAO 3.6 Object Library
    Sub ConnectDAO()
    Dim wk As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set wk = DAO.DBEngine(0)
    Set db = wk.OpenDatabase("d:db2.mdb") 'OPEN DB
    'Set rs = db.OpenRecordset("table1", dbOpenSnapshot) 'READONLY
    Set rs = db.OpenRecordset("table1", dbOpenDynaset) 'WRITE

    rs.AddNew 'ADD A NEW RECORD
    rs!FIELD1 = "new rec" 'ASSIGN VALUE TO A FIELD
    rs.Update 'UPDATE/SAVE RECORD TO TABLE

    'TRAVERSE AND OUTPUT RECORDS
    'Do While Not rs.EOF
    ' MsgBox rs!FIELD1
    ' rs.MoveNext
    'Loop

    rs.Close: db.Close: wk.Close
    Set rs = Nothing: Set db = Nothing: Set wk = Nothing
    End Sub
    [/code]



    [code]
    ADO

    'requires a reference set to
    'microsoft activex data objects 2.x library
    '
    '7/6/2006 - ADO CONNECTION TO A ACCESS DB
    'PASS [False] for unsecured db
    'PASS [True] for a secured db - requires acccount/password
    '
    Public Sub AdoConnectSecureDB(ByVal SecureDB As Boolean)
    'SET DB AND SYSTEM PATHS
    Const cMyDBpath = "D:MyDB.mdb"
    Const cMySysMDW = "D:System.mdw"

    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Dim sCon As String
    Dim MyAccount As String
    Dim MyPassword As String
    Dim errCount As Integer

    On Error GoTo erh

    Select Case SecureDB
    Case Is = False
    MyAccount = "Admin"
    MyPassword = ""
    Case Is = True
    MyAccount = "[ACCOUNT]"
    MyPassword = "[PASSWORD]"
    End Select

    'CONNECTION STRING
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "User ID=" & MyAccount & ";Password=" & MyPassword & ";" & _
    "Data Source=" & cMyDBpath & ";" & _
    "Persist Security Info=False"
    'SECURE DB W/ SYSTEM.MDW?
    If SecureDB = True Then sCon = sCon & ";Jet OLEDB:System database=" & cMySysMDW

    'CREATE NEW CONNECTION
    Set cn = New ADODB.Connection
    'OPEN CONNECTION WITH CONNECTION STRING
    cn.Open sCon
    'CREATE NEW RECORDSET
    Set rs = New ADODB.Recordset
    'OPEN RECORDSET
    'READ/WRITE
    rs.Open "TABLE1", cn, adOpenDynamic, adLockOptimistic, adCmdTable
    'READ ONLY
    'rs.Open "TABLE1", cn, adOpenStatic, adLockOptimistic, adCmdTable

    rs.AddNew 'ADD NEW RECORD
    rs!FIELD1 = "new value" 'ASSIGN VALUE TO A FIELD
    rs.MoveNext 'MOVE TO NEXT/SAVE RECORD

    xit1:
    'CLOSE
    If rs.State <> adStateClosed Then rs.Close
    If cn.State <> adStateClosed Then cn.Close
    xit2:
    'RELEASE RESOURCES
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub

    erh:
    MsgBox Err.Description, vbExclamation, Err.Number
    errCount = errCount + 1
    If errCount = 1 Then Resume xit1 Else Resume xit2
    End Sub
    [/code]

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion