Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: How to do Insert the record in Access form

    I don't know how to edit which can insert the record in the table (Name:brand) through the Access form
    the table has one column( Name: BR_NAME)
    __________________________________________________ ________
    Private Sub BrandInsertCommand_Click()

    Dim remoteConnection As ADODB.Connection
    Dim rsAdd As ADODB.Recordset
    Dim sql As String

    On Error GoTo DbError

    remoteConnection.Provider = "Microsoft.Access.OLEDB.10.0"
    remoteConnection.Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"

    remoteConnection.Properties("Data Source").Value = "G:\COMP351\tony\MobilePhoneShop2.mdb"

    remoteConnection.Open

    rsAdd.CursorType = adOpenDynamic
    rsAdd.LockType = adLockOptimistic

    rsAdd.Open "brand", remoteConnection, , , adCmdTable

    rsAdd.AddNew
    rsAdd!BR_NAME = Me!BrandNameFillin.Value
    rsAdd.Update
    rsAdd.Close

    MsgBox "Recond Added.", vbInformation
    remoteConnection.Close

    Exit Sub

    DbError:
    MsgBox "There was an error adding the record." & Err.Number & ", " & Err.Description



    End Sub
    __________________________________________________ ___________

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Now...it's not quite clear whether or not the table you want to insert the record into is within the current database or in a remote (different MDB) database. I'm going to assume it's a remote DB but in either case I'll show for both.

    An easy way to insert a record into a database table is ti do the following (you may find it easier):

    To insert a record into a table located within the current active Database:

    Dim SQLstrg As String
    SQLstrg = "INSERT INTO brand (BR_NAME) VALUES ('" & Me!BrandNameFillin.Value & "')"
    ' If DAO is Referenced.
    CurrentDb.Execute SQLstrg, dbFailOnError

    ' use below only if DAO is not Referenced.
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLstrg
    DoCmd.SetWarnings True
    __________________________________________________ __

    The SQL string actually relates to:
    "INSERT INTO TableName (TableFieldName) VALUES ('" & FormFieldValue & "')"

    __________________________________________________ __

    To insert a record into a table located within the Remote Database:
    (off the top of my head)

    Dim SQLstrg As String
    SQLstrg = "INSERT INTO brand (BR_NAME) IN G:\COMP351\tony\MobilePhoneShop2.mdb VALUES ('" & Me!BrandNameFillin.Value & "')"
    ' If DAO is Referenced.
    CurrentDb.Execute SQLstrg, dbFailOnError

    ' use below only if DAO is not Referenced.
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLstrg
    DoCmd.SetWarnings True

    __________________________________________________ __

    The SQL string actually relates to:
    "INSERT INTO TableName (TableFieldName) IN PathAndFileNameToRemoteDatabase VALUES ('" & FormFieldValue & "')"
    __________________________________________________ __

    The Remote Database path may need to be within quotes.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •