On Feb 2, 11:18 am, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> 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?

Well I need Access to run it and then I have to export the table to
MySQL. I would prefer to do everything in MySQL with a script or query
if possible.

Other parts of the solution I have to a larger problem includes
queries that never complete in Access. I have another reply post that
outlines the bigger problem below.