Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Smokes a Lot
    Join Date
    Jul 2003
    Location
    CA, USA
    Posts
    1,594
    Thanks
    5
    Thanked 20 Times in 20 Posts

    Multiple Inserts Into Access

    I'm trying to make a query that inserts multiple records in one query.

    This is what I have:

    Code:
    function loadSFO()
    {
    	var sfo=new ActiveXObject('scripting.FileSystemObject');
    	var floc=sfo.OpenTextFile(document.getElementById("artfile").value);
    	var str=floc.readAll();
    	var cut=str.split('\n');
    	var sql="INSERT INTO SFO (Manager, Supervisor, Employee, RGN, Cust_ID, MDN,Feat_CD, PKG_DESC, DateOf, User_ID, Line_Act_Date, Coached, Correct, Notes, Status) VALUES";
    	for(var j=0;j<cut.length-1;j++)
    	{
    		var cut3=cut[j].split(',');
    		for(d=0;d<cut3.length;d++)
    		{
    			if(cut3.length!=13)
    			{
    				alert("There are errors in your csv file.  Please ensure you replaced all instances of commas with a | symbol before transfering to csv!");
    				return;
    			}
    			if(cut3[8]=='Y'||cut3[8]=='N')
    			{
    				alert("It appears you have selected the wrong report type!");
    				return;
    			}
    		}
    	}
    	for(var i=0;i<cut.length-1;i++)
    	{
    		var re=/"/g
    		var re2=/'/g
    		var re3=/'|'/g
    		var fix=cut[i].replace(re,'');
    		var fix=fix.replace(re2,'');
    		var cut2=fix.split(',');
    		for(x=0;x<cut2.length;x++)
    		{
    			cut2[x]=cut2[x].replace(/\|/g,',');
    		}
    		sql+="('"+cut2[1]+"','"+cut2[2]+"','"+cut2[3]+"','"+cut2[4]+"','"+cut2[5]+"','"+cut2[6]+"',+'"+cut2[7]+"','"+cut2[8]+"',#"+cut2[9]+"#,'"+cut2[11]+"','"+cut2[12]+"',false,false,'None',false),";
    	}
    	getDataBase();
    	sql=sql.substring('0',sql.length-1);
    	rs.Open(sql+";");
    	dbc.Close;
    	alert('Process Completed Successfully!');
    }
    I've tried this various ways based on different sites examples but none seem to work. Depending on how I have it I either get the message that there are uneeded characters at the end of the SQL statement or Missing semicolon at the end of SQL statement. Am I just going about this all wrong? It works just fine if I open a new recordset for each insert, but it is taking to long. It's inserting about 1500 records and takes upwords of 10 minutes to process. Any help is appreciated.

    Basscyst
    Last edited by Basscyst; 10-22-2004 at 05:11 PM.
    Helping to build a bigger box. - Adam Matthews

  • #2
    Smokes a Lot
    Join Date
    Jul 2003
    Location
    CA, USA
    Posts
    1,594
    Thanks
    5
    Thanked 20 Times in 20 Posts
    Just to add if I do it like this:


    Code:
    function getDataBase4(sql)
     {
     dbc = new ActiveXObject("ADODB.Connection");
     dbc.Provider = "Microsoft.Jet.OLEDB.4.0";
     dbc.ConnectionString = "Data Source=\\\\carcrdsan1\\carcrd-ops$\\Dept_Apps\\BMS\\ART2.mdb";
     
     dbc.Open //open the database connection with the above settings
     dbc.execute(sql)
     dbc.close;
    }
    Code:
    function loadSFO()
    {
    	var sfo=new ActiveXObject('scripting.FileSystemObject');
    	var floc=sfo.OpenTextFile(document.getElementById("artfile").value);
    	var str=floc.readAll();
    	var cut=str.split('\n');
    	var sql="";
    	for(var j=0;j<cut.length-1;j++)
    	{
    		var cut3=cut[j].split(',');
    		for(d=0;d<cut3.length;d++)
    		{
    			if(cut3.length!=13)
    			{
    				alert("There are errors in your csv file.  Please ensure you replaced all instances of commas with a | symbol before transfering to csv!");
    				return;
    			}
    			if(cut3[8]=='Y'||cut3[8]=='N')
    			{
    				alert("It appears you have selected the wrong report type!");
    				return;
    			}
    		}
    	}
    	for(var i=0;i<cut.length-1;i++)
    	{
    		var re=/"/g
    		var re2=/'/g
    		var re3=/'|'/g
    		var fix=cut[i].replace(re,'');
    		var fix=fix.replace(re2,'');
    		var cut2=fix.split(',');
    		for(x=0;x<cut2.length;x++)
    		{
    			cut2[x]=cut2[x].replace(/\|/g,',');
    		}
    		getDataBase4("INSERT INTO SFO (Manager,Supervisor,Employee,RGN,Cust_ID,MDN,Feat_CD,PKG_DESC,DateOf,User_ID,Line_Act_Date,Coached,Correct,Notes,Status) VALUES('"+cut2[1]+"','"+cut2[2]+"','"+cut2[3]+"','"+cut2[4]+"','"+cut2[5]+"','"+cut2[6]+"',+'"+cut2[7]+"','"+cut2[8]+"',#"+cut2[9]+"#,'"+cut2[11]+"','"+cut2[12]+"',false,false,'None',false);");
    	}
    	
    	alert('Process Completed Successfully!');
    }
    It works just fine. But takes forever. BTW: I'm using ACCESS 2000. I think the first example may only work in MySQL 3.22.5 or later are multiple inserts just not supported in ACCESS 2000? I just ran this and it took 30 minutes to insert 1843 records. There just has to be a way to speed it up. Please help.
    Last edited by Basscyst; 10-22-2004 at 07:07 PM.
    Helping to build a bigger box. - Adam Matthews


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •