Connect to,add,edit & delete records

Connect to,add,edit & delete records

Post by glenne » Mon, 30 Dec 2002 02:38:08



Please give insight or sample code on how to connect to an Access 2000
database & table, then add, edit & delete records in that table using ADO
coding and not a data control.
Thanks in advance
 
 
 

Connect to,add,edit & delete records

Post by glenne » Mon, 30 Dec 2002 02:26:26


Please give insight or sample code on how to connect to an Access 2000
database & table, then add, edit & delete records in that table using ADO
coding and not a data control.
Thanks in advance

 
 
 

Connect to,add,edit & delete records

Post by Barry Kimelma » Mon, 30 Dec 2002 04:39:33


[This followup was posted to microsoft.public.vb.database.ado and a copy
was sent to the cited author.]



Quote:> Please give insight or sample code on how to connect to an Access 2000
> database & table, then add, edit & delete records in that table using ADO
> coding and not a data control.
> Thanks in advance

Here is some code extracted from one of my programs, it should
be sufficient to show you what you need to do :

Public Sub ModifyTableContents(strTableName As String)

' Display the specified table in the form "frmTableModify"
' inside the DataGrid named "dgrModifyGrid" using
' an Adodc control. This grid allows changes to the
' database.

    frmTableModify.lblTableModify.Caption = "Contents of table " & _
            strTableName
    frmTableModify.adoTableModify.CommandType = adCmdTable
    frmTableModify.adoTableModify.RecordSource = strTableName
    frmTableModify.adoTableModify.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;User
ID=Admin;Data Source=c:\somewhere\dbname.mdb;Mode=Share Deny None"
    Set frmTableModify.dgrModifyGrid.DataSource =
frmTableModify.adoTableModify
    frmTableModify.Show vbModal

    Exit Sub
End Sub

"frmTableModify" is a form containing :

    datagrid control ("dgrModifyGrid") with the properties
              "AllowAddNew" , "AllowDelete" and "AllowDelete"
              set to true

    Adodc control ( "adoTableModify")
        with "BOFAction" set to 0 - adDoMoveFirst

    label ("lblTableModify") - this label identifies the table
         that is being manipulated by the form

--
---------

Barry Kimelman
Winnipeg, Manitoba, Canada

 
 
 

Connect to,add,edit & delete records

Post by Glenn Marc » Mon, 30 Dec 2002 15:10:33


Thank you very much for the insight.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Connect to,add,edit & delete records

Post by Steven Bras [M » Sat, 04 Jan 2003 03:02:57


In a nutshell, here's a sample that shows adding, updating and deleting
rows using an ADODB recordset object:

'-- Begin sample code
  Dim oCn As New ADODB.Connection
  Dim oRs As New ADODB.Recordset

  'Open a connection to the Access database
  oCn.Open "provider=microsoft.jet.oledb.4.0;data source=c:\northwind.mdb;"

  'Open a recordset containing all the rows from the Customers table
  oRs.Open "SELECT * FROM CUSTOMERS", oCn

  'Add a new row to the recordset
  oRs.AddNew
  oRs!CustomerID = "12345"
  oRs!CompanyName = "My Test Company"
  oRs.Update

  'Or update a particular row
  oRs!CustomerID = "12344"
  oRs.Update

  'Or delete the row
  oRs.Delete

  'Now clean up, closing your objects
  oRs.Close
  oCn.Close

  Set oRs = Nothing
  Set oCn = Nothing
'-- End sample code

You must add a reference to "ActiveX Data Objects Library 2.5" or later to
your project for this code to work. You also must have Microsoft Data
Access Components 2.5 or later installed on your machine. This will give
you the Jet 4.0 driver that allows you to read Access 2000 and later
databases. If you are working with Access 97 or earlier, you can change the
provider name to "microsoft.jet.oledb.3.5" and use ADO 2.1 if it exists on
your machine.

Hope this helps!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

Connect to,add,edit & delete records

Post by glenne » Mon, 06 Jan 2003 15:10:24


Thanks so very much.  I posted this several days ago and got some friendly
and some not-so-friendly replies.



Quote:> In a nutshell, here's a sample that shows adding, updating and deleting
> rows using an ADODB recordset object:

> '-- Begin sample code
>   Dim oCn As New ADODB.Connection
>   Dim oRs As New ADODB.Recordset

>   'Open a connection to the Access database
>   oCn.Open "provider=microsoft.jet.oledb.4.0;data

source=c:\northwind.mdb;"
Quote:

>   'Open a recordset containing all the rows from the Customers table
>   oRs.Open "SELECT * FROM CUSTOMERS", oCn

>   'Add a new row to the recordset
>   oRs.AddNew
>   oRs!CustomerID = "12345"
>   oRs!CompanyName = "My Test Company"
>   oRs.Update

>   'Or update a particular row
>   oRs!CustomerID = "12344"
>   oRs.Update

>   'Or delete the row
>   oRs.Delete

>   'Now clean up, closing your objects
>   oRs.Close
>   oCn.Close

>   Set oRs = Nothing
>   Set oCn = Nothing
> '-- End sample code

> You must add a reference to "ActiveX Data Objects Library 2.5" or later to
> your project for this code to work. You also must have Microsoft Data
> Access Components 2.5 or later installed on your machine. This will give
> you the Jet 4.0 driver that allows you to read Access 2000 and later
> databases. If you are working with Access 97 or earlier, you can change
the
> provider name to "microsoft.jet.oledb.3.5" and use ADO 2.1 if it exists on
> your machine.

> Hope this helps!

> Steven Bras, MCSD
> Microsoft Developer Support/Visual Basic WebData

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.

 
 
 

Connect to,add,edit & delete records

Post by Steven Bras [M » Sun, 12 Jan 2003 02:09:18


Glad I could be of assistance!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.