On 2 Feb, 16:04, steven...@gmail.com wrote:
> Hi all,
>
> I was wondering if anyone knew of a way to fill a table with rows of
> sequential hours spanning a number of years using a query or set of
> queries.
>
> What I am looking for is a way to mimic this VBA code I use in Access
> to create the table currently. I'll post the VBA at the bottom of the
> post. What I am looking for is the following:
>
> CREATE TABLE tbluhahours (
> StartDate datetime NOT NULL,
> EndDate datetime NOT NULL,
> PRIMARY KEY (`StartDate`,`EndDate`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> INSERT INTO tblUHAHours (StartDate, EndDate)
> VALUES ('2000-01-01 00:00:00' , '2000-01-01 00:59:59');
> INSERT INTO tblUHAHours (StartDate, EndDate)
> VALUES ('2000-01-01 01:00:00' , '2000-01-01 01:59:59');
>
> But would l need a row for every hour of every day for say, from 2000
> to 2010. Is it possible to script this somehow?
>
> This is the VBA I used:
>
> Private Sub makeTable_Click()
> Dim theDate As Date
> Dim theTempDate As Date
> Dim endDate As Date
> Dim rsInsert As Recordset
> Dim strSQL As String
> Dim db As Database
>
> Set db = CurrentDb
>
> theDate = "2000-12-01 00:00:00"
> theTempDate = "2000-12-01 00:59:59"
>
> endDate = "2007-01-01 00:00:00"
>
> While theDate < endDate
> strSQL = "INSERT INTO tblUHAHours (StartDate, EndDate) VALUES
> (#" & _
> Format(theDate, "yyyy-mm-dd hh:mm:ss") & "#, #" & _
> Format(theTempDate, "yyyy-mm-dd hh:mm:ss") & "#)"
> If DatePart("yyyy", theDate) < DatePart("yyyy", endDate) Then
> db.Execute (strSQL)
> End If
> theDate = DateAdd("s", 3600, theDate)
> theTempDate = DateAdd("s", 3600, theTempDate)
> ' MsgBox strSQL & " | " & theDate & " | " & theTempDate
> Wend
> End Sub
>
> Thanks so much for any ideas, or advice you can share!
>
> - Jake

What's wrong with the script you've got?