Populating 10+ Years of Dates Table

[Mordred]

Hi all,

I have created a Dates table in Acces 2003 that has the following attributes:
DateID (PK), Days, Months, Quarters, & Years. I was wondering if there was a way to populate this table, which will span 10+ years, without manually doing it? While I have worked with Access in the past, I have yet to create a table like this. Any help is, as always, appreciated.

-Michael

[tlafferty]

You'd need to do some looping to get the below to work, and add variables for the values you want, and it's untested...

Private Sub CmdAddRecord_Click()
On Error GoTo Err_CmdAddRecord_Click
 
    DoCmd.GoToRecord , , acNewRec
    'Prevent user warnings
  DoCmd.SetWarnings False
 
Dim SQL As String
   SQL = "INSERT INTO tbl_Dates values(Variables here for the values to insert put in order of your fields in the table except for DateID which probably autopopulates);"
 
    DoCmd.RunSQL SQL
 
'Allow user warnings
  DoCmd.SetWarnings True
 
Exit_CmdAddRecord_Click:
    Exit Sub
 
Err_CmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_CmdAddRecord_Click
 
End Sub

[Mordred]

Thanks Thomas,

I am not used to VBA for Access syntax but I am about to learn.

[Mordred]

Sorry for my ignorance Thomas but I have no idea how that code is supposed to populate the fields that I have. I guess I could create this in Excel and then import the table, unless you have a lot of patience to walk me through your code.

[tlafferty]

That might be a better option. The code is untested and it would take quite a bit for me to get it ready to run.

The first thing you'd need to do is find a way to increment the date, month and qtr values, and a for next with a counter could do that for you. This line:

SQL = "INSERT INTO tbl_Dates values(

is where you'd position values separated with commas for each field you'd be writing to.

Excel is probably going to be a faster bet since I'm a bit of a noob to Access - I've just done this before but it's been quite a while...sorry!

[Mordred]

No need to apologize, I also posted a new thread here but it is waiting for mod approval I guess because it is not up on the forum yet.

[Mordred]

I did this by importing the table from Excel but I do plan on figuring this out by coding it.

[mahju]

Hi

I am want to add something

See my solution

I have used two databases. One for data (data.mdb) and the other for code (code.mdb). run the form from code.mdb and it will populate the data in data.mdb

Hera is the code under Generate button

Private Sub Command1_Click()
Dim strdate As Date
Dim dbs As DAO.Database
Dim recinsert As DAO.Recordset

strpath = CurrentProject.Path & "\data.mdb"
Set dbs = OpenDatabase(strpath)
strinsert = "select * from datatable"
Set recinsert = dbs.OpenRecordset(strinsert)
reccount = recinsert.RecordCount
'Use the line below to delte the already populated records (Remove comment mark (')
If reccount > 0 Then
recinsert.Delete
End If
strdate = Me.Text2.Value
stryear = Me.Text5.Value
looptime = stryear * 365
For i = 1 To looptime
recinsert.AddNew
recinsert("dateid") = strdate
recinsert.Update
strdate = strdate + 1
Next i
Set recinset = Nothing
Set dbs = Nothing
MsgBox "Data population completed"

End Sub

Unzip the attached zip file and run the code.mdb in data population folder.

Thanks

Best Regards