Insert multiple records in Access 2003 using one INSERT statement

[Blake Rice]

Hi everyone! This is my first time posting here, so here goes.

THE SITUATION:
I have a form that has a start date, end date, project number, and total volume. These values are pulled from another form. Then depending on the difference between the start and end dates, that number of text boxes have their visibility set to TRUE and are displayed on the form. The user then enters volumes for each of the months between the start date and end date.

THE QUESTION:
I know that I can insert these records into a table one at a time using a loop,

For i = 0 To DateDiff("m",[start], [end])
 INSERT INTO [table] (Column0, column1, ...) VALUES  (value0, value1 ...)
Next

this would then prompt the user if they were sure they wanted to append 1 records yes/no, for every record. I would like to know if there was a way to just loop through the values and have one insert statement.

I have tried using UNION ALL it comes up with a syntax error, I have tried comma separating the groups of values it isn't supported by this version of SQL. I have looked all over the internet but haven't found a suitable solution. Any help or information would be greatly appreciated.

Here is what I have so far.

Thanks,
Blake

[hype261]

Two things... You can turn off the prompt to ask the user if they want to append a row using...

DoCmd.SetWarnin gs False

Just make sure you set it back on again somewhere. I usually set it back on the Exit portion of my function just to be sure it will be set back on if something Errors.

You can insert multiple records using a single insert statement using an Insert and a Select SQL query. So an example...

INSERT INTO tbl_Foo (Foo1, Foo2) 
SELECT Value1, Value2 FROM tbl_Values WHERE 
m > StartDate and m < EndDate.

[ADezii]

Just subscribing for now...will return later.

[Blake Rice]

Thank you Hype261,

Turning the warnings off did achieve the desired result and is now my best solution for this problem.

I tried to make the code that you wrote work but because the values are not coming from any table I dont know what do put after the FROM clause. Also since the values are all coming from text boxes on the form I am not sure how to, 1: Collect all the ones that are visible, and, 2. Attach the same project number to each of the dates.

Thank you for your reply.

Blake

[ADezii]

@Blake Rice - This should do the trick:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim numMonths As Integer
Dim intCounter As Integer
    
numMonths = [DateDiff]

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Test", dbOpenDynaset, dbOpenAppendOnly)
    
For intCounter = 0 To numMonths
  With rst
    rst.AddNew
      ![ProjectMasterNumber] = Me![ProjectNumber]
      ![DateOfVolume] = Me("year" & intCounter)
      ![VolumePerMonth] = Me("volume" & intCounter)
    rst.Update
  End With
Next

MsgBox (numMonths + 1) & " Records have been added to the Table [Test]", _
       vbInformation, "Append Data"

rst.Close
Set rst = Nothing

P.S. - No Error Checking or Data Validation has been included.

[NeoPa]

Firstly, there are two main methods of executing SQL that I use (illustrated below).

Call DoCmd.RunSQL("SQL Statement")
Call db.Execute("SQL Statement")

The first approach will warn of any updates proposed, but the second won't. If you want any SQL to run without the prompts the easiest way (generally) is to use the second method. The object [db] in the second method must be set to your database (Set db = CurrentDb) prior to use of course.

Your original question stated you knew that inserting the values was possible using a loop. One assumes from that that you understand how to access the relevant values within your loop, yet your post #4 indicates you may not know how to do this. We can probably help with this if you need it, but it's always preferable to express the question clearly in the first post. Let us know where you are with this and we can see what you still need to go forward with this. Of course, if you do need to be told how to progress on that basis, then we will need the relevant information to work with, which was not present in the OP (or in the thread so far).

[Blake Rice]

@ADezii
Thank you very much for your help, I cant wait to get back to the office and try that approach. It looks like it should work. I realize that there hasn't been any error checking or data validation added just yet, but I was trying to be able to add these groups of records first. I was having a difficult time working with the SQL and, I am just learning the Access environment. I will have to look into DAO.recordsets more. I see that you closed the rst variable and set it to nothing. Do I need to do that with the MyDB variable? Again your help is greatly appreciated.

@NeoPa
I am coming from a Java background and I am still unfamiliar in an Access and VBA environment. I know how to use a loop in that, I can set one up and have it iterate through some code. The question I had in post #4 was he was using a SELECT statement to gather a set of data to INSERT INTO the table. I didn't see how you could use a SELECT statement to gather values that ONLY existed on a form and hadn't yet been entered into a table. I think I can access the relevant values in a loop, what I am unfamiliar with is what ADezii used with the DAO.database and DAO.recordset, and the methods that they have. Thank you for your help, I appreciate it.

Blake Rice

[ADezii]

@Blake - The code that I posted has been tested, with your Sample Database, and it does work. Copy-N-Paste the code just as it is, and you should be fine. I also took the liberty of Attaching the Revised Database just in case you were having problems.

P.S. - Here is also code that will accomplish exactly the same thing, but only avoids the use of a DAO Recordset to accomplish same.

Dim numMonths As Integer
Dim intCounter As Integer
Dim strSQL As String

numMonths = [DateDiff]
  
For intCounter = 0 To numMonths
  strSQL = "INSERT INTO Test ([ProjectMasterNumber],[DateOfVolume],[VolumePerMonth]) VALUES (" & _
            Me![ProjectNumber] & ", #" & CDate(Me("year" & intCounter)) & "#, " & _
            Me("volume" & intCounter) & ")"
    CurrentDb.Execute strSQL, dbFailOnError
Next
  
MsgBox (numMonths + 1) & " Records have been added to the Table [Test]", _
       vbInformation, "Append Data"

[NeoPa]

Nice Blake. A response that actually deals with the points made, and well too. Let me see if I can clarify the points you've brought up.

The INSERT INTO ... SELECT construct doesn't work for getting data from a form. You're absolutely right to find that confusing when coming at it from the direction you are. I suspect Hype261 was suggesting that on the basis of not being sure that it was important where the data was coming from. That copies data from a recordset to a table. To work, the data would already need to be available in a table somewhere. It's easy to get confused when reading about other people's technical problems. Most of us overlook a detail or two from time to time, and experience tells us that not all points raised by the OP are set in stone. Sometimes alternative ideas are all that's required to get the member progressing. Anyway, the INSERT INTO statement has two main forms. One where the values are supplied as literals (within the VALUES clause as in your OP) and the other where the values are diverted from another recordset (using the SELECT clause). The Help system provides more details of all this.

ADezii's code uses DAO.Recordset processing, but this works as an alternative to the INSERT SQL for each record. There are many ways to crack the nut in Access, and ADezii either prefers that method (I suspect as he's no great fan of SQL) or is simply offering you an alternative to choose from. As you can see from his post #8, it can be done the other way too, and he's also very capable of doing that.

I would just make a small point about line #11 from the code in post #8. Using CurrentDb directly as a reference is a little unreliable. There are warnings around to that effect, but I only understood why relatively recently. CurrentDb is actually CurrentDb() - a function reference. What goes on within the function is hard to say, but I've seen situations where calling it repeatedly can cause code to behave strangely. I would recommend always defining a DAO.Database variable, setting it up once early in the procedure (or project if you prefer), then accessing the database via that variable exclusively. I illustrate the concept below :

Dim db As DAO.Database

Set db = CurrentDb
...
Call db.Execute(strSQL, dbFailOnError)

[ADezii]

@NeoPa - Interesting points, NeoPa. I do believe that the problem you are describing deals specifically with the OpenRecordset() Method as it applies to CurrentDB(), namely:

CurrentDB returns a reference to currently opened Database, so you can open Recordsets just using CurrentDB.OpenR ecordset(). In most cases this works fine, but in complex and recursive procedures you can easy reach a limit of open References (or Instances) CurrentDB can hold. Code below illustrates this point:

Dim i As Long
Dim rst(1000) As DAO.Recordset

For i = 1 To 1000
  Set rst(i) = CurrentDb.OpenRecordset("Select * from Table1", dbOpenDynaset)
    rst(i).MoveFirst
Next i

P.S. - Code will stop at i=250 with Error Message 3048: “Can’t open any more databases”

The workaround is to declare a Public Variable dbs as DAO.Database, set it to CurrentDB at program startup and then use it instead of CurrentDB.

[NeoPa]

OpenRecordset (or any reference that returns an object to be fair) is certainly a more severe problem I would agree. I don't imagine calling .Execute would have the same problems, but I'd still avoid calling a function for each iteration if it can be done with an object reference instead. You're right though, as I wouldn't have mentioned it had I realised it was just that.

[Blake Rice]

@NeoPa and ADezii -
I am amazed at the help that I received from you guys. I was able to implement the solution that you suggested and I am moving on in creating the application. I know that I will have more questions in the future, and I know where to come, but I really do appreciate your help.

Thank You

Blake Rice

[NeoPa]

I'm very pleased to hear it Blake. To be fair, I'd welcome more questions from you. Anyone that posts clearly and responds directly to suggestions is easy to work with in my book :-)