asked on

MS Access ADO open question

I have the following code in an Access 2007 DB. When I get to the line that is supposed to open rstIW I get an error that states:
Run time error 91
Object variable or With Block variable not set.

I tried creating a second connection called cnnLocal2 but that didn't work, I got the same error.
It's probably a rookie mistake (because I am one) but I can't see it. What am I doing wrong?

Sub Extract_Raw_Data()
Dim cnnLocal As New ADODB.Connection
Dim rstExcel As New ADODB.Recordset
Dim rstIW As ADODB.Recordset
Dim IWType As String
Dim IWSubType As String
Set cnnLocal = CurrentProject.Connection
rstExcel.Open "Select * from ImageWires", cnnLocal, adOpenStatic, adLockPessimistic
rstIW.Open "Select * from tblIW WHERE 1 = 0", cnnLocal, adOpenDynamic, adLockOptimistic

While Not rstExcel.EOF
If rstExcel.Fields("F2") = "Pre-Labeled ImageWires 1.5" Or _
rstExcel.Fields("F2") = "Pre-Labeled ImageWires 1.8" Or _
rstExcel.Fields("F2") = "Pre-Labeled C7 Dragonfly" Then
IWType = rstExcel.Fields("F2")
End If
If (rstExcel.Fields("F2") = "12424-00" Or _
rstExcel.Fields("F2") = "12513-00" Or _
rstExcel.Fields("F2") = "13751-00") And _
rstExcel.Fields("F4") > " " Then 'Use the part # to indicate data rec and Lot # to show data present
rstIW.AddNew
rstIW.Fields("IW PN") = rstExcel.Fields("F2")
rstIW.Fields("IW Lot") = rstExcel.Fields("F4")
rstIW.Fields("IW Qty") = rstExcel.Fields("F6")
If IsNumeric(rstExcel.Fields("F8")) Then
rstIW.Fields("IW Exp_Date") = rstExcel.Fields("F8")
rstIW.Fields("IW Status") = Null
Else
rstIW.Fields("IW Exp_Date") = Null
rstIW.Fields("IW Status") = rstExcel.Fields("F8")
End If
rstIW.Fields("IW Type") = IWType
rstIW.Update
End If

rstExcel.MoveNext
Wend
rstExcel.Close
End Sub

Usama Foad

I think the problem in:

IWType = rstExcel.Fields("F2")

You set the "IWType" in the first IF statement but you do not if this condition has not been achieved. So in line 32 (rstIW.Fields("IW Type") = IWType) the "IWType" generate the error. You need to set "IWType" before the second "IF".
Also I think nesting the first "IF" with the other "IF"s will be better.

Sub Extract_Raw_Data()
    Dim cnnLocal As New ADODB.Connection
    Dim rstExcel As New ADODB.Recordset
    Dim rstIW As ADODB.Recordset
    Dim IWType As String
    Dim IWSubType As String
    Set cnnLocal = CurrentProject.Connection
    rstExcel.Open "Select * from ImageWires", cnnLocal, adOpenStatic, adLockPessimistic
    rstIW.Open "Select * from tblIW WHERE 1 = 0", cnnLocal, adOpenDynamic, adLockOptimistic
    
    While Not rstExcel.EOF
        If rstExcel.Fields("F2") = "Pre-Labeled ImageWires 1.5" Or _
           rstExcel.Fields("F2") = "Pre-Labeled ImageWires 1.8" Or _
           rstExcel.Fields("F2") = "Pre-Labeled C7 Dragonfly" Then
            IWType = rstExcel.Fields("F2")
        else
			IWType = ' =============================You need to set IWType here======================
			If (rstExcel.Fields("F2") = "12424-00" Or _
				rstExcel.Fields("F2") = "12513-00" Or _
				rstExcel.Fields("F2") = "13751-00") And _
				rstExcel.Fields("F4") > " " Then   'Use the part # to indicate data rec and Lot # to show data present
				rstIW.AddNew
				rstIW.Fields("IW PN") = rstExcel.Fields("F2")
				rstIW.Fields("IW Lot") = rstExcel.Fields("F4")
				rstIW.Fields("IW Qty") = rstExcel.Fields("F6")
				If IsNumeric(rstExcel.Fields("F8")) Then
					rstIW.Fields("IW Exp_Date") = rstExcel.Fields("F8")
					rstIW.Fields("IW Status") = Null
				Else
					rstIW.Fields("IW Exp_Date") = Null
					rstIW.Fields("IW Status") = rstExcel.Fields("F8")
				End If
				rstIW.Fields("IW Type") = IWType
				rstIW.Update
			End If
        End If
        rstExcel.MoveNext
    Wend
    rstExcel.Close
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

RichNH

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial