UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open _recordset Failed, Access 2016    
 
   
Psycoperl
post Jan 10 2019, 05:03 PM
Post#1



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways



Happy belated New Year!


I am experiencing an error that I have never seen before and cannot figure out how to solve. I use ADODB in this project already.

QUOTE
Run-time error: -2147467259 (800004005): Method 'Open' of object '_Recordset' failed


On the RSS.Open line in the code below.

CODE
    Dim cnC As ADODB.Connection
    Set cnC = CurrentProject.Connection
    Dim cuC As ADODB.CursorTypeEnum
        cuC = adOpenDynamic
    Dim lkC As ADODB.LockTypeEnum
        lkC = adLockOptimistic
    
    Dim RSS As ADODB.Recordset
    Set RSS = New ADODB.Recordset
    
    Dim intStagingSize As Integer
        intStagingSize = 0
    Dim intCounter As Integer
        intCounter = 0
        
    Dim sqlS As String
        sqlS = "SELECT SR73Staging.ID, SR73Staging.Term, SR73Staging.Session, SR73Staging.Institution, SR73Staging.InstructionMode, " & _
        " SR73Staging.Career, SR73Staging.Subject, SR73Staging.Catalog, SR73Staging.Section, SR73Staging.[Class Nbr], " & _
        " SR73Staging.CourseDescr, SR73Staging.[Tot Enrl], SR73Staging.[Enrl Stat], SR73Staging.[Class Stat], " & _
        " SR73Staging.[Class Type], SR73Staging.[Cap Enrl], SR73Staging.[Wait Cap], SR73Staging.[Wait Tot], " & _
        " SR73Staging.[Min Enrl], SR73Staging.Component, SR73Staging.[Course ID], SR73Staging.[Offer Nbr], " & _
        " SR73Staging.[EMPLID], SR73Staging.[Last Name], SR73Staging.[First Name], SR73Staging.[Start Date], SR73Staging.[End Date], " & _
        " SR73Staging.OTE_START, SR73Staging.OTE_END, SR73Staging.[EVAL START], SR73Staging.[EVAL END], " & _
        " SR73Staging.[EVAL CYCLE], SR73Staging.NOTES, SR73Staging.[SMART EVAL COURSE#] " & _
        " FROM z_StagingCU_BAR_SR_00073_ClassTableLoad AS SR73Staging;"

    Debug.Print sqlS
    
    Dim rsCS As ADODB.Recordset
    Set rsCS = New ADODB.Recordset
    
    Dim sqlCS As String
        sqlCS = "Select * from _tblCFClassScheduleWithEvaluationPeriods Where 1=2"
    
    RSS.Open sqlS, cnC, cuC, lkC


Suggestions? Guidance?

Thanks in advance
Go to the top of the page
 
Psycoperl
post Jan 10 2019, 05:05 PM
Post#2



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways


P.S. I had already tried the Compact and Repair Database trick....
Go to the top of the page
 
LPurvis
post Jan 11 2019, 07:13 AM
Post#3


UtterAccess Editor
Posts: 16,311
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Have you, of course, tested the SQL as output in your Debug.Print line in a new query? Just to ensure there's nothing wrong there? (Though the error message would usually be more helpful than that you've received were that the case.)
Some of your field names could be considered reserved, but they're fully qualified so I'd be surprised.

Can you provide a cut down version of your accdb with just that table (empty the data out) and the code which also fails for our inspection?

Cheers

--------------------
Go to the top of the page
 
Psycoperl
post Jan 11 2019, 07:51 AM
Post#4



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways


First thing I did was debug.print the SQL and ran it without issues.

I will look into the rest when I get to my office.
Go to the top of the page
 
projecttoday
post Jan 11 2019, 11:46 AM
Post#5


UtterAccess VIP
Posts: 10,478
Joined: 10-February 04
From: South Charleston, WV


Or just temporarily substitute a much-simpler SQL to see if that's it.

Speaking of much-simpler SQL, what is

CODE
Dim sqlCS As String
        sqlCS = "Select * from _tblCFClassScheduleWithEvaluationPeriods Where 1=2"


? Did you already try that?

--------------------
Robert Crouser
Go to the top of the page
 
Psycoperl
post Jan 11 2019, 01:01 PM
Post#6



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Will try...
Go to the top of the page
 
Psycoperl
post Jan 11 2019, 03:14 PM
Post#7



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways


A funny thing happened in VBA.

If I used Select * from table it worked. If i specified columns it failed. Since the original query used all but 2 columns. I just went with the select * version
Go to the top of the page
 
RJD
post Jan 11 2019, 03:56 PM
Post#8


UtterAccess VIP
Posts: 9,289
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but maybe we could get some more discussion on this from others. My understanding of .Open is that it opens a stored/saved table or query, not a construct within the VBA. And SELECT * ... simply opens the source table.

You could test this by defining the SQL and saving it to a query def. Then using the open on that saved query.

But, of course, I may be all wet on this, and I would happily yield to others with better knowledge of this. Anyone else want to contribute? A sharp clarification would be nice to have ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jan 11 2019, 04:10 PM
Post#9


UtterAccess VIP
Posts: 10,478
Joined: 10-February 04
From: South Charleston, WV


A typo in the SELECT list, psyco? You could try entering the various columns one at a time (unless, of course, you've decided to forget about it and move on).

(A psyco typo? !!)

--------------------
Robert Crouser
Go to the top of the page
 
Psycoperl
post Jan 11 2019, 04:39 PM
Post#10



Posts: 357
Joined: 11-March 15
From: Somewhere lost in the NY Subways


Since it was only two columns that were not being used and they are pretty small (single char per field).... I just went with * and moved on. Have bigger issues to code...

The funny part is that it did not matter what number of fields I used it did the same thing on this table. Strange....
Go to the top of the page
 
LPurvis
post Jan 12 2019, 08:50 AM
Post#11


UtterAccess Editor
Posts: 16,311
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Since not specifying column names allowed it to proceed, it sounds more like a reserved word than before.
One easy test would be to create a saved query akin to the SQL you had for the recordset (along with field aliases - but including the first few fields being aliased too) and open that query in the recordset.

Anyway, same result really, you're off and running.

Cheers

--------------------
Go to the top of the page
 
LPurvis
post Jan 12 2019, 09:10 AM
Post#12


UtterAccess Editor
Posts: 16,311
Joined: 27-June 06
From: England (North East / South Yorks)


Hi Joe

>> Hi: PMFJI, but maybe we could get some more discussion on this from others.
Always worth putting info out there for the future.

>> My understanding of .Open is that it opens a stored/saved table or query, not a construct within the VBA. And SELECT * ... simply opens the source table.
In ADO, the recordset object does indeed have an Open method, and it's that which is used to open the recordset (i.e. move from simply prepping it, to launching it in the same way you would in DAO with OpenRecordset.)
You can set other properties before or at the same time as the Open method by passing them as parameters to it.
Other than that difference, the expectations are much the same. The source for either type of recordset can be a table, query or ad hoc SQL statement. Conceptually, it's the Open method of the recordset, not so much the opening of a specific object.

>> You could test this by defining the SQL and saving it to a query def. Then using the open on that saved query.
Well, as mentioned in my previous reply, I'd be curious to see that done with some aliasing anyway. (Square brackets are often a standard way to escape a reserved meaning.)
e.g. [Session] and [Catalog]
Some words have a tendency to be reserved-ish. (Quite annoying.) In that they don't cause a failure to open in every instance. So escaping them as routine is always worthwhile.

Cheers

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th January 2019 - 02:58 AM