Why SQL command doesn't work

[Nova]

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.

 

[Douglas J. Steele]

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.

 

[John Spencer]

In addition to Mr. Steele's comment.

Your SQL statement is flawed in that it is missing spaces. You will end up with
an error when you attempt to run the query.
I've added three spaces to highlight the places that require a space

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]));"

DoCmd.SetWarnings False
DoCmd.RunSQL Strsql
DoCmd.SetWarnings True
End Sub

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

(no e-mails, please!)

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.

 

[Randy Harris]

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, I find I can get the equivalent functionality from ADO with:

CurrentProject.Connection.Execute Strsql

 

[Douglas J. Steele]

Doug, I find I can get the equivalent functionality from ADO with:

CurrentProject.Connection.Execute Strsql

Yeah, that should work as well.

I never use ADO with Jet, so I never think of it!