SQL statement doesn't work in VBA.

[Dan]

When I run the following, it gave me " A RunSQL action requires an argument
consisting of an SQL statement". Can anyone help?

Dim SQL as String
SQL = "SELECT [Contact List].[RFQ Vendor], [Contact List].[Email Sent],
[Contact List].Email, [Contact List].SupplierCCEmail, RFQ_Master.* " & _
"FROM RFQ_Master INNER JOIN [Contact List] ON RFQ_Master.[RFQ Vendor] =
[Contact List].[RFQ Vendor];"
DoCmd.RunSQL SQL

 

[Chris O'C via AccessMonster.com]

Runsql requires an action query or a data definition query, like "update
tablename", "insert into", "create table" etc. You're using a select query,
which only gathers records in tables for display. It doesn't do anything to
those records or the tables.

Select queries can be used with querydefs in vba code.

Chris
Microsoft MVP

When I run the following, it gave me " A RunSQL action requires an argument
consisting of an SQL statement". Can anyone help?

Dim SQL as String
SQL = "SELECT [Contact List].[RFQ Vendor], [Contact List].[Email Sent],
[Contact List].Email, [Contact List].SupplierCCEmail, RFQ_Master.* " & _
"FROM RFQ_Master INNER JOIN [Contact List] ON RFQ_Master.[RFQ Vendor] =
[Contact List].[RFQ Vendor];"
DoCmd.RunSQL SQL

 

[Paolo]

Hi Dan,
I'm pretty sure SQL is an access reserved word. Change the name of your
variable. e.g.

Dim str_SQL as String
str_SQL = "SELECT [Contact List].[RFQ Vendor], [Contact List].[Email Sent],
[Contact List].Email, [Contact List].SupplierCCEmail, RFQ_Master.* " & _
"FROM RFQ_Master INNER JOIN [Contact List] ON RFQ_Master.[RFQ Vendor] =
[Contact List].[RFQ Vendor];"
DoCmd.RunSQL (str_SQL)

HTH Paolo

 

[Chris O'C via AccessMonster.com]

And don't use sql as a variable. It's a reserved word.

Chris
Microsoft MVP

Runsql requires an action query or a data definition query, like "update
tablename", "insert into", "create table" etc. You're using a select query,
which only gathers records in tables for display. It doesn't do anything to
those records or the tables.

Select queries can be used with querydefs in vba code.

Chris
Microsoft MVP

When I run the following, it gave me " A RunSQL action requires an argument
consisting of an SQL statement". Can anyone help?

[quoted text clipped - 5 lines]

[Contact List].[RFQ Vendor];"
DoCmd.RunSQL SQL

 

[Paolo]

I didn't notice that the statement was just a select query. As Chris said
runsql requires an action query or a data definition query

Hi Dan,
I'm pretty sure SQL is an access reserved word. Change the name of your
variable. e.g.

Dim str_SQL as String
str_SQL = "SELECT [Contact List].[RFQ Vendor], [Contact List].[Email Sent],
[Contact List].Email, [Contact List].SupplierCCEmail, RFQ_Master.* " & _
"FROM RFQ_Master INNER JOIN [Contact List] ON RFQ_Master.[RFQ Vendor] =
[Contact List].[RFQ Vendor];"
DoCmd.RunSQL (str_SQL)

HTH Paolo

When I run the following, it gave me " A RunSQL action requires an argument
consisting of an SQL statement". Can anyone help?

Dim SQL as String
SQL = "SELECT [Contact List].[RFQ Vendor], [Contact List].[Email Sent],
[Contact List].Email, [Contact List].SupplierCCEmail, RFQ_Master.* " & _
"FROM RFQ_Master INNER JOIN [Contact List] ON RFQ_Master.[RFQ Vendor] =
[Contact List].[RFQ Vendor];"
DoCmd.RunSQL SQL