Error on DoCmd 2

[Blondie96]

I have a procedure that does a query & creates a table from it. That part works fine. I am trying to run a 2nd query. However when I run it I get:
'
'"Run-Time error '2342'
' A RunSQL action requires an argument consisting
' of an SQL statement.
this occurs on the DoCmd.RunSQL getSchedChannel line.

I have tried using different runsql's but no matter how I set up the select, it bombs on the 2nd occurrence. (the sql's were cut & pasted directly from the access query builder so format shouldn't be the problem)

Any guidance would be appreciated. Thanks


The code is:

Private Sub Command1_Click()

Dim SQL As String

'
SQL = "SELECT DISTINCT [Channel Type].ChnlTypeId, ChannelName.ChannelName, Unit.UnitName, ScheduleHours.Day, ScheduleHours.Hour " & _
"INTO MonthlySchedule FROM [Channel Type] " & _
"INNER JOIN ((ScheduleHours INNER JOIN Unit ON ScheduleHours.UnitID = Unit.UnitID) " & _
"INNER JOIN ChannelName ON ScheduleHours.ChannelID = ChannelName.ChnlKey) " & _
"ON [Channel Type].ChnlTypeId = ChannelName.ChannelType"

DoCmd.RunSQL SQL

Dim dbsSATSCP As Database
Dim tdfMonthlySchedule As TableDef
Dim fldLoop As Field

' CodeDb refers to the database where code is running
Set dbs = CodeDb
Set tdfMonthlySchedule = dbs.TableDefs!MonthlySchedule

' Add new field
AppendDeleteField tdfMonthlySchedule, "APPEND", _
"ColorCd", dbLong

Dim getSchedChannel As String

'Here's where it gets stuck

getSchedChannel = "SELECT DISTINCT [Channel Type].ChnlTypeId, ChannelName.ChannelName " & _
"FROM [Channel Type] INNER JOIN (ScheduleHours INNER JOIN " & _
"ChannelName ON ScheduleHours.ChannelID=ChannelName.ChnlKey) ON " & _
"[Channel Type].ChnlTypeId=ChannelName.ChannelType"

DoCmd.RunSQL getSchedChannel

' Results in a :
'"Run-Time error '2342'
' A RunSQL action requires an argument consisting
' of an SQL statement.

End Sub

 

[Tranman]

Blondie,
The RunSQL method only works when you are running an action query; Insert/Delete/Update/Drop/Whatever. The first one works because you are doing a "Select...Into".

The second one doesn't work because you're just doing a select. Access would not know what to do with the results of the Select statement.

Hope that helps.
Tranman

 

[earthandfire]

In other words you should use DoCmd.OpenQuery getSchedChannel but since this is at the end of your Sub, and you don't seem to do anyuthing with the query, I'm a little confused as to why you would run a select query at this point.

 

[Blondie96]

Thank you Tranman, that is important information I missed.

Thank you Earthandfire. I only included in the code those things that were relevant to the problem. The sub will go on to get information from the 2nd query and create information to add into a field in the table created by the first query (the append of the field is already working).

I tried the DoCmd.OpenQuery, I got a Run-time error '7874'
access can't find object 'Select ....' I guess this is looking for a saved query. Can I run a command to do this query without having a query object already defined?

Thanks again

 

[earthandfire]

Using DAO syntax, I'm afraid I've never bothered with ADO

Dim rs as recordset
Set rs = CurrentDb.OpenRecordset(getSchedChannel)

you can then access the data using:

rs!FieldName to get the value of a specific field in the current record

rs. gives you a list of the available methods

to make sure that rs has records use something like:

if not(rs.bof and rs.eof) then
etc
etc

to iterate throuh the records you could use:
while not rs.eof
etc etc

 

[Blondie96]

Thank you earthandfire. I haven't tried implementing it yet, but it looks like what you have provided will solve several of my questions/needs.

 

[Blondie96]

Thank you earthandfire. I haven't tried implementing it yet, but it looks like what you have provided will solve several of my questions/needs.

 

[Blondie96]

I am using access 2003, which syntax is the most recent, DAO or ADO?

 

[PHV]

which syntax is the most recent
ADO

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

As PHV says, ADO is the more recent.

I don't know whether or not my example would work under ADO, however, if not, if you set DAO up in your References, you should be able to use my code by making changes similar to the following:

Dim rs as DAO.Recordset

and

Set rs = DAO.CurrentDB.OpenRecordset(getSchedChannel)

A little experimentation may be needed, but something alongs these lines should do the trick.

Never having used ADO, I always remove the reference in my systems, I'm afraid I can't be more precise.

 

[earthandfire]

I've just done a bit of experimenting and unfortunately CurrentDb doesn't appear to work as above if you are using ADO as you main reference.

So, if you add a reference to DAO and then try the following, which works with Access 2003

Dim DAOdb As DAO.Database
Dim rs As DAO.Recordset

Set DAOdb = DAO.DBEngine(0).Databases(0)
Set rs = DAOdb.OpenRecordset(getSchedChannel)

 

[Blondie96]

In my research I found the following:

' CodeDb refers to the database where code is running
Set dbs = CodeDb

so, I may be able to use:

Dim rs as recordset
Set rs = dbs.OpenRecordset(getSchedChannel)


but I will have to try it out.

Thanks for the help.

 

[earthandfire]

I don't think that will work because DAO and ADO use different object libraries.

So although CodeDb may refer the the database in question, I doubt that it will give you a DAO reference to it, which is what is needed for the Recordset object to work as intended. The problem, I think, is that both ADO and DAO have a Recordset object. This means that unless you qualify any reference to Recordset, Access will presume you mean the object from the first reference library.

If in your references you have ADO, then DAO then any ambiguous reference will be resolved as belonging to ADO, and obviously the reverse holds true.

Assuming your references are ADO, DAO then to overcome this problem, you must explicity state that the Recordset object that you require is from DAO. This is why I used that method.