RUN SQL Statement

[lovemuscle2005]

Hi All,

I'm trying to run the following SQL statement via RUNSQL, but not getting very far, can anyone help?

Thanks


Option Compare Database

Function BLANKSSQL()
On Error GoTo BLANKSSQL_Err
DoCmd.RunSQL "SELECT [Store Lookup List].[Store No], [Store Lookup List].[Store Name], [Store Lookup List].Region, [Store Lookup List].Division FROM (([Store Lookup List] LEFT JOIN [MQ1 Test] ON [Store Lookup List].[Store No] = [MQ1 Test].[Store No]) LEFT JOIN [MQ2 Test] ON [Store Lookup List].[Store No] = [MQ2 Test].[Store No]) LEFT JOIN [MQ3 Test] ON [Store Lookup List].[Store No] = [MQ3 Test].[Store No] WHERE ((([MQ1 Test].[New Contracts]) Is Null));"
TestSQL_Exit:
Exit Function
BLANKSSQL_Err:
MsgBox Error$
Resume TestSQL_Exit
End Function

 

[Norie]

What problems are you actually having?

Why are you using a Function instead of a Sub?

 

[lovemuscle2005]

I get an error message saying:

"A Run SQL Action requires an argument consisting of an SQL statement"

 

[Norie]

Got the same error myself.

Try testing your SQL string in a normal query.

I can't quite see anything wrong with it but that might help, maybe there's a space missing somewhere.

By the way I think a better way to do this sort of thing is to build the SQL string piece by piece.

strSQL = "SELECT [Store Lookup List].[Store No], [Store Lookup List].[Store Name], [Store Lookup List].Region, [Store Lookup List].Division "
strSQL = strSQL & "FROM (([Store Lookup List] LEFT JOIN [MQ1 Test] ON [Store Lookup List].[Store No] = [MQ1 Test].[Store No]) LEFT JOIN [MQ2 Test] ON [Store Lookup List].[Store No] = [MQ2 Test].[Store No]) LEFT JOIN [MQ3 Test] ON [Store Lookup List].[Store No] = [MQ3 Test].[Store No] "
strSQL = strSQL & "WHERE ((([MQ1 Test].[New Contracts]) Is Null));"

DoCmd.RunSQL strsSQL

 

[Philem]

CORRECTION

You cannot use the DoCmd.RunSQL statement with a SELECT query. Instead, you may use

Set rs = CurrentDatabase.OpenRecordset(strSQL)

that Norie nearly had. Again, DoCmd.RunSQL will NOT work with SELECT queries.

HTH
P

D'oh! Please note above corrected code line.

 

[mdmilner]

Hmm, can't get that to work at all Philem.
Usually I do this when I need to create an actual query object.

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb()

strSQL = "various valid SQL"

Set qdf = dbs.CreateQueryDef(aqryName, strSQL)

If all I need to do is open a recordset object - to walk through it, for example, I'd substitute this for the Set qdf statement

Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)

vary the dbOpenDynaset option depending on need.

Mike

 

[Philem]

You're right, Mike! <blush> Made an error which has been corrected.

Apologies, all!
P

 

[mdmilner]

I tried that too when I was fiddling with the statement.
I think I'm missing something.

I usually do this as a two step process.

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset(sqlstring, mode)

In A2K, CurrentDb() is not equivalent to CurrentDatabase

Am I missing a reference to see that as an option or are you in a different version of Access?

Mike

 

[Philem]

I normally don't use the second argument of OpenRecordset. Also, I'm running A97. Corporate policy. Let me attach some working A97 code:

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

Set db = CurrentDb
strsql = "SELECT * from Table1;"
set rs = db.OpenRecordset(strSQL)

I have a DAO3.5 reference set.


P

 

[mdmilner]

What wasn't working is the object "CurrentDatabase"
O2K seems to require "CurrentDb()"

That's unless 'CurrentDatabase' is actually a variable.
Looks like we're doing the exact same thing otherwise.

Mike