fragglea Posted September 28, 2007 Share Posted September 28, 2007 Im trying to take a set of variables "AssetNumber" "EquipmentCode" and insert a new record into the [Asset Numbers] table. In a loop to insert a list in a single go.Iv been banging my head on this for a few hours and reduced the SQL statement to the most simple procedure I can.Its looking like this:SQL = "INSERT INTO AssetNumbers([Asset Number]) VALUES ('44'),('43');"the I use the doCmd.runSQL to run the script.I know something is wrong with the script. I just cant see what.The above statement returns the "missing ; ". So what am I missing?This is being used in Access, with VB. Link to comment Share on other sites More sharing options...
justsomeguy Posted October 1, 2007 Share Posted October 1, 2007 Are you sure Access supports inserting multiple records? I can't find a lot of information about it. Link to comment Share on other sites More sharing options...
fragglea Posted October 1, 2007 Author Share Posted October 1, 2007 I have no idea.Thats why Im asking. LOLI have a terrible feeling your right, which means I have to make a whole new table and redo the code. Create a sepperate Query to merge it with the target... <sigh> Link to comment Share on other sites More sharing options...
justsomeguy Posted October 1, 2007 Share Posted October 1, 2007 Welcome to the wonderful world of Access. Link to comment Share on other sites More sharing options...
fragglea Posted October 2, 2007 Author Share Posted October 2, 2007 Alright.Now. Since access apparently cant add tables in a bulk fasion.I can force it to insert one at a time in a loop and remove the "Im about to insert.....".For Loop1 = 1 to 30mySql = "INSERT INTO myTable( ...) " & _"VALUES (" & Loop1 ", ...) " & _CurrentDb.Execute mySql, dbFailOnError Next Loop1This will add one record at a time and surpress the irritating "press 'yes' "message.It takes a little while though...Any thoughts on a speedier way to accomlish this? (give the data is in a "listbox" not another table) Link to comment Share on other sites More sharing options...
justsomeguy Posted October 2, 2007 Share Posted October 2, 2007 Well, the main problem is that Access just isn't very fast. Or good. It's easy to use, but that's about it, you're fairly limited in what you can do and how fast you can do it compared with other database systems like SQL Server or MySQL. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.