Hi

The following code is an example of the Event Procedure assigned to the OnClick event of a button. It demonstrates how to create an SQL string and how to execute it. The pieces of text starting "me." are referencing controls on the form. If I was being fussy, I should be using "me!" to avoid any confusion in Access. This example is probably a bit more complex than you currently need as I am doing some stuff with some dates which are part of the record I'm inserting. I also have a loop for reasons which currently escape me (this is from a project I was working on a year ago).

It includes an example of both an insert and a delete - all you need now to complete the set is an update (and if you know SQL that shouldn't be too hard). But most importantly it provides you with an example of building an SQL string in VB. Positioning and inclusion of the double-quote characters (") are crucial as they not only mark the points at which the string is interrupted to add in the control values eg

& ",""" & Nz(Me.Brand, "") & """,""" &

but they are also used to surround string values.

The ampersand character (&) is the joiner but one of the most confusing things is the use of the double-quote. & """,""" & translates into the completed string as ",". Therefore & ",""" & Nz(Me.Brand, "") & """,""" & translates into ,"SomeBrandNameFromTheForm","

I suggest you do some research into using breakpoints and the Immediate Window (activated using <CTRL>G) if you don't already know how to use them. Its very handy to step through your code and to display the actual string being created.

The SetWarnings False command turns off the messages asking the user to confirm the action and SetWarnings True turns them back on again - a lot of new developers fail to turn them back on again and get into strife - you have been warned 😀

I'm using the NZ function because most of the values I'm inserting are strings so to avoid any issues I'm turning NULLs into empty strings (habit from coding Transact SQL).

Hmm! Re-reading your last past you mentioned stored procedures - are you wanting to use a stored procedure to do the data stuff? If so, it is possible to do it, but then I am used to using the Access MDB format rather than ADP so I'm a little vague as whether or not the methods I would use in an MDB will still work in an ADP. Lets tackle that when I have more info.

Please note, I live in New Zealand so our time zones are likely out of synch. I'm making this last post from my desk at work and then heading home - it is coming up for 6pm. It is also Friday 🙂 Wohooo!! TGIF!!! But I'll keep pace with this over the weekend when I get spaces.

All the best.

Cheers

Private Sub btnUpdateDataWarehouse_Click()

Dim dtEndOfMonth As Date

Dim dtLoopDate As Date

Dim strSQL As String

dtEndOfMonth = DateSerial(Year(Date), Month(Date), 0)

dtLoopDate = DateAdd("m", -12, dtEndOfMonth)

Do While dtLoopDate <= dtEndOfMonth

strSQL = "Insert Into dbo_rpt_ProgressiveStores ( " & _

"Store,[Site No],Brand,Region,Area,SiteDescription,SiteAddress, " & _

"SiteSuburb,[State],PostCode,TargetTonnes,DefaultDensity,ForTheMonthEnding) " & _

"Select " & _

"""" & Nz(Me.Store, "") & """," & Me.SiteNo & ",""" & Nz(Me.Brand, "") & """,""" & Nz(Me.Region, "") & """," & _

"" & Nz(Me.Area, "NULL") & ",""" & Nz(Me.SiteDescription, "") & """,""" & Nz(Me.SiteAddress, "") & """," & _

"""" & Nz(Me.SiteSuburb, "") & """,""" & Nz(Me.CState, "") & """,""" & Nz(Me.PostCode, "") & """," & _

Me.TargetTonnes & "," & Me.DefaultDensity & ",#" & dtLoopDate & "#;"

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

dtLoopDate = DateSerial(Year(DateAdd("m", 1, dtLoopDate)), Month(DateAdd("m", 1, dtLoopDate)) + 1, 0)

Loop

MsgBox "New Store data created.", vbOKOnly, "Add Store Data"

strSQL = "DELETE ProgressiveStores.*, ProgressiveStores.[Site No] " & _

"FROM ProgressiveStores " & _

"WHERE (((ProgressiveStores.[Site No]) = " & Me.SiteNo & "));"

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Me.Form.Requery

End Sub