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 · Accepted 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 ('" & InDate & "' );"

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

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

[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