Why SQL command doesn't work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nova - 30 Dec 2005 09:35 GMT

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.

Reply to this Message

Douglas J. Steele - 30 Dec 2005 12:31 GMT

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.

Signature

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

>I create from and copy SQL statement form Query Design to command click
>event
[quoted text clipped - 13 lines]
> After I click commamd6 buttton then I open the HistoryEQ tablel, It has no
> any data inserted to table. Help me Please.

Reply to this Message

John Spencer - 30 Dec 2005 13:40 GMT

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.
[quoted text clipped - 43 lines]
> > After I click commamd6 buttton then I open the HistoryEQ tablel, It has no
> > any data inserted to table. Help me Please.

Reply to this Message

Randy Harris - 30 Dec 2005 14:11 GMT

> Simply creating a SQL statement doesn't do anything by itself: you have to
> run the SQL.
[quoted text clipped - 20 lines]
> 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

Signature

Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Reply to this Message

Douglas J. Steele - 30 Dec 2005 14:57 GMT

> 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!

Signature

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

Reply to this Message