Converting Sql insert loop to Access???

[ptenhet]

I'm trying to use a sql query to insert into an Access 2007 query. I'm assuming isn't as straight forward as it seems. I have very little experience with Access, so the example below is something I would like to get into Access. Basically I want a form to take a couple of fields and create duplicate records for each day. The loop create unique days for each month. In the end, I'll run the loop from a start of month date until total days in that month. So for Feb, I'll have 28 records with the same set values except the day will be different. When I try this in Access, it errors saying Expecting "Select", "Delete", etc...
Thanks
Pt


declare @NumDays int
declare @StartDate Datetime
declare @SDate datetime
declare @SMonth int
declare @SYear int
declare @Cnt int
declare @IPhase char(20)
declare @IBudWaste int
declare @IBudOre int


set @StartDate='2-1-2013'
set @NumDays=28
set @SMonth=datepart(month,@StartDate)
set @SYear=datepart(year,@StartDate)
set @Cnt=1
set @Iphase='1NE'
set @IBudWaste =22000
set @IBudOre = 1288

while @Cnt<=@NumDays
begin
insertinto TargetsPhase(
MYear
,MMonth
,MDate
,Tphase
,Tbudwaste
,Tbudore )
values (
@SYear
,@SMonth
,@Cnt
,@Iphase
,@IBudWaste
,@IBudOre )
set @Cnt=@Cnt+1
end

 

[pbaldy]

In VBA, you have to build/execute the SQL. I'd actually use a recordset, which would look like:

http://www.baldyweb.com/MultiselectAppend.htm

Except you'd use your Do/While loop. To use SQL, you'd have to concatenate the literal parts of the string with your variables and then execute it:

Dim strSQL As String
strSQL = "INSERT INTO..."
CurrentDb.Execute strSQL

 

[ptenhet]

So you can't really use the SQL view as SQL code, you have to do this stuff in VB code?

 

[pbaldy]

Yes and no. You can use SQL view, but you can't use T-SQL unless the query is a pass-through to SQL Server. Jet SQL doesn't allow the programming that T-SQL does.