strSQL inserting multiple records

[CheyenneWay]

I know this is a very basic question but my brain is broke. How do I insert multiple records with a strSQL statment.

my example:

dim strSQL as String
dim strSQL2 as String

strSQL = "INSERT INTO myTable(Field1, Field2)" & _
"Values("myStuff","moreStuff")"

strSQ2L = "INSERT INTO myTable(Field1, Field2)" & _
"Values("MoreMyStuff","SomeMoreStuff")"



Do I really need to declare a strSQL2 to insert another record? Seems too complicated to be true and I would think I could just add many rows with just one strSQL statement. Thank you for taking a look!

 

[dhookom]

With Access and "Values", it's one record at a time. In SQL Server, I believe you can use:

INSERT INTO myTable(Field1,Field2)
SELECT Value1, Value2
UNION
SELECT Value3, Value4

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya CheyenneWay . . .

For static values like you've shown, following is an example:

[blue]   Dim db As DAO.Database, SQL As String
   Dim F1 As String, F2 As String, x As Integer
   
   Set db = CurrentDb
   
   For x = 1 To 4
      F1 = Choose(x, "MyStuff1", "MyStuff2", "MyStuff3", "MyStuff4")
      F2 = Choose(x, "MyStuff1a", "MyStuff2a", "MyStuff3a", "MyStuff4a")
      
      SQL = "INSERT INTO myTable(Field1, Field2) " & _
         "Values('" & F1 & "','" & F2 & "')"
      [green]'Debug.Print SQL[/green]
      
      db.Execute SQL, dbFailOnError
      DoEvents
   Next[/blue]

If your talking a large record count,put the values in a table and use a [blue]Select statement[/blue] instead of [blue]values[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JoeAtWork]

You can just keep using the same strSQL variable (just make sure you are done with the old value before you give it a new value):

strSQL = "INSERT INTO myTable(Field1, Field2)" & _
         "Values("myStuff","moreStuff")"

db.Execute strSQL

strSQL = "INSERT INTO myTable(Field1, Field2)" & _
         "Values("MoreMyStuff","SomeMoreStuff")"

db.Execute strSQL