Re: Why SQL command doesn't work

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Simply creating a SQL statement doesn't do anything by itself: you have to
run the SQL.

You can use

DoCmd.RunSQL Strsql

That will generate a "You're about to insert..." message to which the user
has to respond. To avoid that, you can use:

DoCmd.SetWarnings False
DoCmd.RunSQL Strsql
DoCmd.SetWarnings True

or, my preference,

CurrentDb.Execute Strsql, dbFailOnError

The latter will raise a trappable error if something's wrong when the SQL is
executed. Note, though, that it requires a reference to be set to DAO. If
you're using Access 2000 or Access 2002, that reference isn't one of the
defaults: with any code module open, select Tools | References from the menu
bar, scroll through the list of available references until you find the one
for Microsoft DAO 3.6 Object Library, and select it.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Nova" <Nova@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B5B2F662-3FD0-4EC2-88D7-E8D8F03A8342@xxxxxxxxxxxxxxxx
>I create from and copy SQL statement form Query Design to command click
>event
> procedure But it is not work, If I Run from Query it works. I don't
> understand why?.
> This is my SQL statement
>
> Private Sub Command6_Click()
> Dim Strsql As String
> Strsql = "INSERT INTO HistoryEQ ( Detail )"
> Strsql = Strsql & "SELECT MaintReport.MaintID AS Detail FROM MaintReport"
> Strsql = Strsql & "INNER JOIN RX ON MaintReport.MaintID = RX.MaintID"
> Strsql = Strsql & "WHERE
> (((RX.AssetNo)=[forms]![Equipdetail].[AssetNo]));"
> End Sub
>
> After I click commamd6 buttton then I open the HistoryEQ tablel, It has no
> any data inserted to table. Help me Please.


.

Relevant Pages

  • Re: Simple Insert Into...
    ... Or just listen to Tom and disregard my posts:) ... Sometimes it can be very helpful to copy the SQL statement from the Immediate ... Private Sub YourProcedureName() ... Dim strSQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: This UPDATE query has me baffled. Should be simple.
    ... into the SQL statement. ... > I'm getting an error MsgBox saying: ... > Private Sub cmdSaveLetterGrades_Click ... Dim StrSQL As String ...
    (microsoft.public.access.queries)
  • Re: Why SQL command doesnt work
    ... Your SQL statement is flawed in that it is missing spaces. ... Dim Strsql As String ... Note, though, that it requires a reference to be set to DAO. ... >> Private Sub Command6_Click ...
    (microsoft.public.access.gettingstarted)
  • Re: Too few parameters, expected n when executing SQL from VBA
    ... desired SQL statement, either, so I suggest a completely different approach ... Public Sub TestQuery(ByVal strFieldName As String) ... Dim strSQL As String, strTemp As String ...
    (microsoft.public.access.modulesdaovba)