how to insert values into the database table using vba in ms access

Question

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

Answer 1

The first step needed to  your code is to start with removing the appropriate errors before moving further as for smooth execution of program.

Below code will help to insert values into the database :

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

Answer 2

This query can be resolved using the CTE, in which we have a CTE known as `DateSeries`. This functionality starts with an initial date and then it keeps on adding one day till it reaches the upper bound. Now, we can use a INSERT query over here to take all the dates that are being generated, and then can add the same into the mentioned table