Solved

Unable to open recordset.

Posted on 2006-06-14
9
579 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Expert Comment

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

Set newRec = oCon.OpenRecordset(recSQL, 2)

1 = dbOpenTable
2 = dbOpenDynaset
0
 
LVL 85
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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