?
Solved

Unable to open recordset.

Posted on 2006-06-14
9
Medium Priority
?
580 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 2000 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
Industry Leaders: 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 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

Industry Leaders: 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!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 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