Solved

Unable to open recordset.

Posted on 2006-06-14
9
577 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…

830 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