Solved

Unable to open recordset.

Posted on 2006-06-14
9
578 Views
Last Modified: 2012-08-13
Hello, I am trying to open a recordset in my access data project.

Here is the sample code I am using:
Private Sub Command1_Click()
Dim msg As Integer
Dim recSQL As String

recSQL = "SELECT dbo.Job.PurchaseOrder, dbo.Notes.Note" & _
" FROM dbo.Notes INNER JOIN" & _
" dbo.Job ON dbo.Notes.Job_ID = dbo.Job.Job_ID" & _
" WHERE (dbo.Notes.NoteType_ID = 5)"

msg = MsgBox("This will send all values from Notes / code 5 to the PurchaseOrder field!", vbOKCancel)
If msg = 1 Then ' Yes
'preform operation
Dim oCon As DAO.Database
Dim newRec As DAO.Recordset
Set oCon = CurrentDb()

‘>>> Conks out here
Set newRec = oCon.OpenRecordset(recSQL, 2)

I got the SQL syntax form the query designer so I think it fine.
The error message I get is:
Run time error 91:
Object variable or With Block variable not set.



And the rest of the code:
newRec.MoveFirst
Do While newRec.EOF = False
  'Transfer data
NewRec.MoveNext
  Loop
 MsgBox "Done!"
 
Else
'msg = no
Exit Sub
End If
End Sub
Does any one know what my problem is?
Thanks,  Jacob
0
Comment
Question by:JakeBushnell
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 16906556
Try this:

Dim cnn As ADODB.Connection
Set cnn=CurrentProject.Connection
Dim newRec As New ADODB.Recordset
With newRec
    .Open recSQL, cnn, adOpenDynamic, adLockOptimistic
    Do While Not .EOF
        ' transfer data
        .MoveNext
    Loop
    .Close
End With
Set newRec = Nothing
Set cnn = Nothing
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16906583
I can't immediately see whats wrong with pasted code

Ive tweaked it slightly


Private Sub Command1_Click()

    Dim msg As Integer
    Dim recSQL As String
    Dim newRec As DAO.Recordset
   
    recSQL = "SELECT Job.PurchaseOrder, Notes.Note " & _
             "FROM dbo.Notes INNER JOIN " & _
             "Job ON Notes.Job_ID = Job.Job_ID " & _
            "WHERE (Notes.NoteType_ID = 5)"

'HERE IT DUMPS THE SQL IN THE IMMEDIATE WINDOW            
    Debug.Print recSQL
   
    msg = MsgBox("This will send all values from Notes / code 5 to the PurchaseOrder field!", vbOKCancel)
    If msg = vbYes Then ' Yes
   
        'preform operation
        Set newRec = CurrentDb.OpenRecordset(recSQL, 2)
   
        'newRec.MoveFirst  - THIS NOT REALLY NEEDED
        Do While newRec.EOF = False
            'Transfer data

'SEE IMMEDIATE WINDOW DO U GET INFO APPEARING?
            debug.print rs!PurchaseOrder
            newRec.MoveNext
        Loop
        MsgBox "Done!"
     
        newRec.Close
        Set newRec = Nothing
       
    End If
   
End Sub




Now your sql, do u need to put dbo. in front?
does it work with it removed e.g. in my post here?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16906590
Is this Access Backend DB or some other DB? I was wondering because dbo. was used
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 65

Expert Comment

by:rockiroads
ID: 16906594
Also was the reference to DAO added?
0
 
LVL 84
ID: 16906607
Try using something other than 1 in your openrecordset:

Set newRec = oCon.OpenRecordset(recSQL, 2)

1 = dbOpenTable
2 = dbOpenDynaset
0
 
LVL 84
ID: 16906616
Sorry, you already using the dbOpenDynaset constant ...
0
 
LVL 2

Author Comment

by:JakeBushnell
ID: 16906680
This a actually an ADP used to get data from a SQL server based ADB. I will try your sugjestions.. Hold on a min.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16906716
ok, in that case, u best use ADO and not DAO

0
 
LVL 2

Author Comment

by:JakeBushnell
ID: 16906719
Bingo! Thanks a lot. I thought about trying the ADODB object but I am not used to it.

Thanks again everyone!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

737 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