469,323 Members | 1,611 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Insert multiple records in Access 2003 using one INSERT statement

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,

Expand|Select|Wrap|Line Numbers
  1. For i = 0 To DateDiff("m",[start], [end])
  2.  INSERT INTO [table] (Column0, column1, ...) VALUES  (value0, value1 ...)
  3. 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
Attached Files
File Type: zip Example1.zip (39.6 KB, 748 views)
Apr 1 '11 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Dim numMonths As Integer
  2. Dim intCounter As Integer
  3. Dim strSQL As String
  4.  
  5. numMonths = [DateDiff]
  6.  
  7. For intCounter = 0 To numMonths
  8.   strSQL = "INSERT INTO Test ([ProjectMasterNumber],[DateOfVolume],[VolumePerMonth]) VALUES (" & _
  9.             Me![ProjectNumber] & ", #" & CDate(Me("year" & intCounter)) & "#, " & _
  10.             Me("volume" & intCounter) & ")"
  11.     CurrentDb.Execute strSQL, dbFailOnError
  12. Next
  13.  
  14. MsgBox (numMonths + 1) & " Records have been added to the Table [Test]", _
  15.        vbInformation, "Append Data"

12 15299
hype261
207 100+
Two things... You can turn off the prompt to ask the user if they want to append a row using...

DoCmd.SetWarnings 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...

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tbl_Foo (Foo1, Foo2) 
  2. SELECT Value1, Value2 FROM tbl_Values WHERE 
  3. m > StartDate and m < EndDate.
  4.  
Apr 1 '11 #2
ADezii
8,800 Expert 8TB
Just subscribing for now...will return later.
Apr 1 '11 #3
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
Apr 1 '11 #4
ADezii
8,800 Expert 8TB
@Blake Rice - This should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Dim numMonths As Integer
  4. Dim intCounter As Integer
  5.  
  6. numMonths = [DateDiff]
  7.  
  8. Set MyDB = CurrentDb
  9. Set rst = MyDB.OpenRecordset("Test", dbOpenDynaset, dbOpenAppendOnly)
  10.  
  11. For intCounter = 0 To numMonths
  12.   With rst
  13.     rst.AddNew
  14.       ![ProjectMasterNumber] = Me![ProjectNumber]
  15.       ![DateOfVolume] = Me("year" & intCounter)
  16.       ![VolumePerMonth] = Me("volume" & intCounter)
  17.     rst.Update
  18.   End With
  19. Next
  20.  
  21. MsgBox (numMonths + 1) & " Records have been added to the Table [Test]", _
  22.        vbInformation, "Append Data"
  23.  
  24. rst.Close
  25. Set rst = Nothing
P.S. - No Error Checking or Data Validation has been included.
Apr 2 '11 #5
NeoPa
32,173 Expert Mod 16PB
Firstly, there are two main methods of executing SQL that I use (illustrated below).
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunSQL("SQL Statement")
  2. 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).
Apr 2 '11 #6
@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
Apr 2 '11 #7
ADezii
8,800 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. Dim numMonths As Integer
  2. Dim intCounter As Integer
  3. Dim strSQL As String
  4.  
  5. numMonths = [DateDiff]
  6.  
  7. For intCounter = 0 To numMonths
  8.   strSQL = "INSERT INTO Test ([ProjectMasterNumber],[DateOfVolume],[VolumePerMonth]) VALUES (" & _
  9.             Me![ProjectNumber] & ", #" & CDate(Me("year" & intCounter)) & "#, " & _
  10.             Me("volume" & intCounter) & ")"
  11.     CurrentDb.Execute strSQL, dbFailOnError
  12. Next
  13.  
  14. MsgBox (numMonths + 1) & " Records have been added to the Table [Test]", _
  15.        vbInformation, "Append Data"
Attached Files
File Type: zip Example2.zip (29.3 KB, 322 views)
Apr 2 '11 #8
NeoPa
32,173 Expert Mod 16PB
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 :

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2.  
  3. Set db = CurrentDb
  4. ...
  5. Call db.Execute(strSQL, dbFailOnError)
Apr 3 '11 #9
ADezii
8,800 Expert 8TB
@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.OpenRecordset(). 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:
Expand|Select|Wrap|Line Numbers
  1. Dim i As Long
  2. Dim rst(1000) As DAO.Recordset
  3.  
  4. For i = 1 To 1000
  5.   Set rst(i) = CurrentDb.OpenRecordset("Select * from Table1", dbOpenDynaset)
  6.     rst(i).MoveFirst
  7. 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.
Apr 3 '11 #10
NeoPa
32,173 Expert Mod 16PB
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.
Apr 3 '11 #11
@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
Apr 5 '11 #12
NeoPa
32,173 Expert Mod 16PB
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 :-)
Apr 7 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by shank | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.