Unable to open recordset.
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,
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
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,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is this Access Backend DB or some other DB? I was wondering because dbo. was used
Also was the reference to DAO added?
Try using something other than 1 in your openrecordset:
Set newRec = oCon.OpenRecordset(recSQL,
1 = dbOpenTable
2 = dbOpenDynaset
Set newRec = oCon.OpenRecordset(recSQL,
1 = dbOpenTable
2 = dbOpenDynaset
Sorry, you already using the dbOpenDynaset constant ...
ASKER
This a actually an ADP used to get data from a SQL server based ADB. I will try your sugjestions.. Hold on a min.
ok, in that case, u best use ADO and not DAO
ASKER
Bingo! Thanks a lot. I thought about trying the ADODB object but I am not used to it.
Thanks again everyone!
Thanks again everyone!
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(re
'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?