473,882 Members | 1,575 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert multiple records in Access 2003 using one INSERT statement

19 New Member
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
Apr 1 '11
12 16162
NeoPa
32,584 Recognized Expert Moderator MVP
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
Blake Rice
19 New Member
@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,584 Recognized Expert Moderator MVP
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

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

Similar topics

10
3246
by: shank | last post by:
I have a recordset that contains multiple records of product a user is purchasing. For clarity, I converted the recordset fields to variables. I need to take that entire recordset and insert it into another table on a remote server. The below code only inserts 1 record. How do I change the code to get all records inserted? thanks! <% Dim DataConn2 Set DataConn2 = Server.CreateObject("ADODB.Connection")
8
5526
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE, UPDATE, DELETE) which are controlled by a web frontend and the table records are manipulated to control the permissions. Example: The Press Release section record would look like this: Username: John Doe Function Name: Press Release
0
1552
by: Ian Macey | last post by:
I am wanting to Insert multiple records into a table, and although I have some working code I feel that there must be a better way. The purpose of the code is to do a directory listing of a sub-directory and for each file listed insert a record into a temporary table. Can anyone help? My code is as follows.... SQL Stored Procedure dbo.sp_bmwrInsSpool @username VarChar(8), @spoolName VarChar(12), @spoolDate VarChar(6) AS DECLARE...
3
5793
by: Wim Verhavert | last post by:
Hi all, I'm bothered with this question for weeks now.... Is it possible to edit multiple records at once using VBA? I have this continuous form and depending on the selection the user makes, I want to edit another table (not the recordsource of the form) in my database. How can I access the selected records in VBA? Any ideas would be very welcome.... Thanks in advance,
4
2127
by: nepifanio | last post by:
Hi Guys, I'm trying to import data out of MySQL db to MS Access 2003 using ImportXML. I don't have any problems doing the import, except for one table with a "text" field defined. I'm getting an error message saying "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.". Thanks,
5
16473
by: r_o | last post by:
hi, im somehow new to web development, im developing an application with ms access 2003 using asp i want to know if there's a way to make a bulk insert into the database using the recordset object. im trying to use the updatebatch method by im still not able to perform(i dont know if it's the right 1 to use) in my database i use an autonumber field type as my PK if there's a chance that 2 users would be inserting data as the same time...
0
4462
chumlyumly
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The tables are all linked with the field 'member_id', which is an auto-increment field in the parent table ('members'). I've been able to input multiple records into the other three tables 'specialty_groups', 'committee_interest' and 'committee_member'...
3
4460
by: uma9 | last post by:
hi, the code below is used to insert a single record....i want to know how to insert multiple records using a "for" loop...please help Set connect = CreateObject ("ADODB.Connection") connect.open "DSN=OPTUMETL;Driver= Oracle in OraHome92;Server=urnts1.uhc.com;UID=OPTUMETL;PWD=OPTUMETL" Reporter.ReportEvent 0, "Database connection", "Successfully connected to URNTS1" Set objRecordset = CreateObject("ADODB.Recordset") ' Stmt to execute...
1
4731
by: ronakinuk | last post by:
how to insert multiple rows in excel using vba how to insert multiple rows in excel using vba how to insert multiple rows in excel using vba
8
6066
by: cehlinger | last post by:
I am trying to add multiple records based on a date range. I found some examples and have tried them but am getting errors and I can't seem to figure out what the problem is. I am not a VBA programmer so am a little lost. Any help would be appreciated. As an example, the user would enter in the following: 4 522 6/5/2010 6/10/2010 Once the user selected OK it would enter 6 records into the schedule table as the following:
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11108
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10830
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7956
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4601
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.