CODEWITHSUNDEEP
sqlms-accessvba
David G
David G

Reputation: 2355

Adding a new record with VBA

I have a form in which one of the ComboBoxes lists all the documents of a given project. The user should select one and after pressing a button, and if present in Table Dessinsit opens a second form showing that record. If it is not present in that table, I want to add it in.

One of my collegues told me all I had to do was to execute an SQL query with VBA. What I have so far is this:

    Dim rsDessin As DAO.Recordset
    Dim strContrat As String
    Dim strProjet As String
    Dim strDessin As String
    Dim sqlquery As String

  'I think these next 3 lines are unimportant. I set a first query to get information I need from another table

    strDessin = Me.Combo_Dessin
    strProjet = Me.Combo_Projet
    sqlquery = "SELECT [Projet HNA] FROM [Projets] WHERE [Projet AHNS] = '" & strProjet & "'"

    Set rsDessin = CurrentDb.OpenRecordset(sqlquery)

    If Not rsDessin.RecordCount > 0 Then 'If not present I want to add it 
        strContrat = rsDessin![Projet HNA]
        sqlquery = "INSERT INTO Feuilles ([AHNS], [Contrat], [No Projet]) VALUES (strDessin, strContrat, strDessin)"
        'Not sure what to do with this query or how to make sure it worked.
    End If

 'Checking my variables
    Debug.Print strProjet
    Debug.Print strContrat
    Debug.Print strDessin

 'By here I'd like to have inserted my new record.

    rsDessin.Close
    Set rsDessin = Nothing

I also read online that i could achieve a similar result with something like this:

Set R = CurrentDb.OpenRecordset("SELECT * FROM [Dessins]")
R.AddNew
R![Contrat] = strContrat
R![Projet] = strProjet
R![AHNS] = strDessin
R.Update
R.Close
Set R = Nothing
DoCmd.Close

Is one way better than the other? In the case where my INSERT INTO query is better, what should I do to execute it?

Upvotes: 0

Views: 12756

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15375

You're asking which is preferable when inserting a record: to use an SQL statement issued to the Database object, or to use the methods of the Recordset object.

For a single record, it doesn't matter. However, you could issue the INSERT statement like this:

CurrentDb.Execute "INSERT INTO Feuilles ([AHNS], [Contrat], [No Projet]) VALUES (" & strDessin & ", " & strContrat & ", " & strDessin & ")", dbFailOnError

(You should use the dbFailOnError option to catch certain errors, as HansUp points out in this answer.)

For inserting multiple records from another table or query, it is generally faster and more efficient to issue an SQL statement like this:

Dim sql = _
    "INSERT INTO DestinationTable (Field1, Field2, Field3) " & _
    "SELECT Field1, Field2, Field3 " & _
    "FROM SourceTable"
CurrentDb.Execute sql

than the equivalent using the Recordset object:

Dim rsSource As DAO.Recordset, rsDestination As DAO.Recordset
Set rsSource = CurrentDb.OpenRecordset("SourceTable")
Set rsDestination = CurrentDb.OpenRecordset("DestinationTable")
Do Until rs.EOF
    rsDestination.AddNew
    rsDestination!Field1 = rsSource!Field1
    rsDestination!Field2 = rsSource!Field2
    rsDestination!Field3 = rsSource!Field3
    rsDestination.Update
    rs.MoveNext
Loop

That said, using an SQL statement has its limitations:

  • You are limited to SQL syntax and functions.

    This is partially mitigated in Access, because SQL statements can use many VBA built-in functions or functions that you define.

  • SQL statements are designed to work on blocks of rows. Per-row logic is harder to express using only the Iif, Choose, or Switch functions; and logic that depends on the current state (e.g. insert every other record) is harder or impossible using pure SQL. This can be easily done using the Recordset methods approach.

    This too can be enabled using a combination of VBA and SQL, if you have functions that persist state in module-level variables. One caveat: you'll need to reset the state each time before issuing the SQL statement. See here for an example.

Upvotes: 3

HansUp
HansUp

Reputation: 97131

One part* of your question asked about INSERT vs. Recordset.AddNew to add one row. I suggest this recordset approach: 

Dim db As DAO.Database
Dim R As DAO.Recordset
Set db = CurrentDb
Set R = db.OpenRecordset("Dessins", dbOpenTable, dbAppendOnly)
With R
    .AddNew
    !Contrat = rsDessin![Projet HNA].Value
    !Projet = Me.Combo_Projet.Value
    !AHNS = Me.Combo_Dessin.Value
    .Update
    .Close
End With

* You also asked how to execute an INSERT. Use the DAO.Database.Execute method which Zev recommended and include the dbFailOnError option. That will add clarity about certain insert failures. For example, a key violation error could otherwise make your INSERT fail silently. But including dbFailOnError ensures you get notified about the problem immediately. So always include that option ... except in cases where you actually want to allow an INSERT to fail silently. (For me, that's never.)

Upvotes: 1

Related Questions