How to insert values into the database table using VBA in MS access

Question

I ve started to use access recently  I am trying to insert a few rows into the database  however  I am stuck as it is throwing an error       Too few parameters    I have a table test with only one column in it named start date I want to insert all the dates between two dates for example if I consider 1 7 2014 to 3 7 2014 I need dates as 1 7 2014 2 7 2014 3 7 2014 in my table  but I have problem inserting the code I used is as follows   Private Sub createRec Click   Dim StrSQL As String Dim InDate As Date Dim DatDiff As Integer Dim db As database InDate Me FromDateTxt  here I have used a code to find out the difference between two dates that i ve not written For i   1 To DatDiff StrSQL    INSERT INTO Test  Start Date  VALUES      amp  InDate  amp         StrSQL   StrSQL  amp   SELECT  Test   db Execute StrSQL db close i i 1 next i End Sub   My code throws an error in the line Db Execuite StrSQL  as too few parameters  Hope somebody could help me with this issue  Thanks In advance

User · Answer

Remove this line of code  For i   1 To DatDiff  A For loop must have the word NEXT Also  remove this line of code  StrSQL   StrSQL  amp   SELECT  Test   because its making Access look at your final SQL statement like this  INSERT INTO Test  Start Date  VALUES      amp  InDate  amp       SELECT  Test  Notice the semicolon in the middle of the SQL statement  should always be at the end  its by the way not required  you can also omit it   also  there is no space between the semicolon and the key word SELECT   in summary  remove those two lines of code above and your insert statement will work fine  You can the modify the code it later to suit your specific needs  And by the way  some times  you have to enclose dates in pounds signs like

User · Answer

since you mentioned you are quite new to access  i had to invite you to first remove the errors in the code  the incomplete for loop and the SQL statement   Otherwise  you surely need the for loop to insert dates in a certain range   Now  please use the code below to insert the date values into your table  I have tested the code and it works  You can try it too  After that  add your for loop to suit your scenario  Dim StrSQL As String Dim InDate As Date Dim DatDiff As Integer  InDate   Me FromDateTxt  StrSQL    INSERT INTO Test  Start Date  VALUES      amp  InDate  amp          DoCmd SetWarnings False DoCmd RunSQL StrSQL DoCmd SetWarnings True

User · Answer

You can t run two SQL statements into one like you are doing   You can t  execute  a select query   db is an object and you haven t set it to anything   e g  set db   currentdb   In VBA integer types can hold up to max of 32767 - I would be tempted to use Long   You might want to be a bit more specific about the date you are inserting   INSERT INTO Test  Start Date  VALUES       amp  format InDate   mm dd yyyy    amp

[sql] How to insert values into the database table using VBA in MS access

Examples related to sql

Examples related to ms-access

Examples related to vba

Examples related to ms-access-2010