Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Unable to open recordset.

Posted on 2006-06-14
9
Medium Priority
?
581 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

636 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