Solved

Open Recordset Method Failed

Posted on 2014-04-17
9
931 Views
Last Modified: 2014-05-13
Can someone help me figure out why this won't open for me.

Dim strSQL As String, strF As String, strT As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


CurrentProject.Connection.Execute "Delete * From TempAssetTotals"

strF = txtDateFrom
strT = txtDateTo
 



strSQL = "SELECT DISTINCT qryFilters.Filters FROM qryFilters INNER JOIN AssetServiceDates ON qryFilters.AssetID = AssetServiceDates.AssetID" _
            & " WHERE (((qryFilters.Filters) Is Not Null) AND ((AssetServiceDates.Next) Between #" & strF & "# And #" & strT & "#))"


'''fails to open here....says method of open failed

With rs
    .Open strSQL, CurrentProject.Connection
    While Not .BOF And Not .EOF
        CurrentProject.Connection.Execute "INSERT INTO TempAssetTotals (ConsType, Size) Values ('Filters','" & rs("Filters") & "')"
    .MoveNext
        Wend
    .Close
End With
0
Comment
Question by:jb702
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 40008027
looks like something wrong with the SQL. Please capture this string in your debug window and attach so I can see.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 40008232
Instead of "CurrentProject.Connection" can you try "CurrentProject.AccessConnection".  It may not make a difference, but worth a try.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 40008474
Your SQL and code are fine.

The issue is in your naming conventions -- using reserved words.

In your SELECT statement, "Next" is a reserved word.

You will run into similar issues on your INSERT statement, because "Size" is also a reserved word.

If you must use these field names (ideally change them to something else), then enclose them in square brackets.  Note the changes below.

This will definitely work (tested), assuming a simple qryFilters.  If it does NOT work for you, post the SQL of qryFilters in your next comment.

Dim strSQL As String, strF As String, strT As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


CurrentProject.Connection.Execute "Delete * From TempAssetTotals"

strF = txtDateFrom
strT = txtDateTo
  



strSQL = "SELECT DISTINCT qryFilters.Filters FROM qryFilters INNER JOIN AssetServiceDates ON qryFilters.AssetID = AssetServiceDates.AssetID" _
            & " WHERE (((qryFilters.Filters) Is Not Null) AND ((AssetServiceDates.[Next]) Between #" & strF & "# And #" & strT & "#))"


'''fails to open here....says method of open failed

With rs
    .Open strSQL, CurrentProject.Connection
    While Not .BOF And Not .EOF
        CurrentProject.Connection.Execute "INSERT INTO TempAssetTotals (ConsType, [Size]) Values ('Filters','" & rs("Filters") & "')"
    .MoveNext
        Wend
    .Close
End With

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 40008477
As an aside, see this list of reserved words:
http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx

Next and Size are included in the last list, titled "Access database engine reserved words".

Any of these words WILL cause errors in your recordset code, and generally should be avoided when naming fields (although enclosing them in square brackets identifies them as fields, and suppresses the errors).
0
 

Author Comment

by:jb702
ID: 40056654
I've requested that this question be deleted for the following reason:

Went another route
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40056655
I object to this simply being deleted.

The author never bothered to respond to any of the comments posted.

The original question was:

<< Can someone help me figure out why this {the recordset} won't open for me. >>

I tested my suggested correction before posting it, with the reserved word bracketed and it definitely resolved the error - allowing the recordset to properly open.

<< Went another route >>

The author MAY have encountered other problems with this method after resolving this error, in code, queries or other things not posted here -  leading him or her to go another route (or alternatively post feedback or follow up questions here).  However with the lack of feedback, there was no way for us to know.

The deletion was probably to clear out abandoned questions, in order to unlock the Question Wizard to post this new one:
http://www.experts-exchange.com/Database/MS_Access/Q_28430970.html
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 40059248
I also object.  I appreciate the fact that the author went a different route.  

If the question was answered then points should be awarded.  If the question is no longer needed/wanted/etc., then points should be awarded.  The commitment should be there when the question is posted.  

If there is another issue, so be it.  

Thanks.
0
 

Author Comment

by:jb702
ID: 40063660
Sorry about no seeing the post about the reserved words. I will make sure i keep this list handy to refer to. I believe you are correct in identify the problem.

thank you.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Null or "" 28 52
SQL Get Store Procedure Column Name As Row 5 39
Using Access multi Listbox to pass criteria to a query 9 27
get and set file atrributes 5 10
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question