having trouble requesting two recordsets from my MS Access Database

[tvdij]

I'm having trouble requesting two recordsets from my MS Access Database.

The first recordset "Rs" works fine and I can add to the database.

The second recordset called "Rs2" I want to be read-only, so that I can look up a value in a another table, and add that value to the table being accessed by the first recordset.

But I get errors when using the second recordset E.G. MsgBox Rs2("Error ID") will return an error.

Any Ideas?

Code is below:

Option Explicit
Sub Access_Data(theTool As String)
'FileSystemObject will only be recognized if you go to VISBASIC window->TOOLS->REFERENCES->ENABLE "Microsoft Scripting Runtime"
Dim FileName As String, sText As String, curText As String, SQL As String
Dim Cn As ADODB.Connection 'Must go to TOOLS->REFERENCES->ENABLE "Microsoft ActiveX Library 2.x
Dim Rs As ADODB.Recordset, Rs2 As ADODB.Recordset
Dim MyConn
Dim errorInfo(1 To 7) As String
Dim index As Integer, Count As Integer
    ChDir "F:\User Data\alopez\Desktop\Documents\Atlas Error Logs\" & Right(theTool, 6)
On Error GoTo FileCancel
    FileName = Application.GetOpenFilename("ERROR LOG (*.dat),*.dat", , "Import " & Right(theTool, 6) & " Data")
Dim oFSO As New FileSystemObject
Dim oFS
Set oFS = oFSO.OpenTextFile(FileName)
Do Until oFS.AtEndOfStream
    sText = oFS.ReadLine 'Requires reference to Microsoft ActiveX Data Objects xx Library
If (InStr(1, sText, "E0000") = 0) And (sText <> "") And (InStr(1, sText, "20000,") = 0) Then
    MyConn = "F:\User Data\alopez\Desktop\Documents\Atlas Error Logs\Error Database\Atlas Errors.mdb" 'set Source
    Set Cn = New ADODB.Connection 'Create RecordSet
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
    End With
    Set Rs = New ADODB.Recordset
    Set Rs2 = New ADODB.Recordset
    Rs.CursorLocation = adUseServer
    Rs.Open Source:=theTool, _
            ActiveConnection:=Cn, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockOptimistic, _
            Options:=adCmdTable
    index = 1
    Count = 1
    Rs.AddNew
        Do Until Count = 8
            sText = Trim(sText)
            index = InStr(sText, ",")
            If index = 0 Then
                curText = sText
            Else
                curText = Left(sText, index - 1)
            End If
            If index <> 0 Then
                sText = Right(sText, Len(sText) - index)
            End If
            errorInfo(Count) = curText
            Count = Count + 1
        Loop
    SQL = "SELECT * FROM tblErrorList WHERE [Error ID]=" & errorInfo(1)
    Set Rs2 = Cn.Execute(SQL)
    Rs("Error ID") = errorInfo(1)
    Rs("Time Stamp") = errorInfo(2)
    Rs("Error Label") = errorInfo(3)
    Rs("Error Description") = errorInfo(4)
    Rs("Millisecond") = errorInfo(5)
    Rs("Extra Message 1") = errorInfo(6)
    Rs("Extra Message 2") = errorInfo(7)
    Rs("Tool ID") = Right(theTool, 6)
    Rs("Error Cat") = Rs2("Error Cat")
    Rs.Update
    'Write RecordSet to results area
    Rs.Close
    Rs2.Close
    'Loop Goes Here
    Set Cn = Nothing
End If
Loop
oFS.Close
MsgBox "Import Completed Successfully!"
Exit Sub
FileCancel:
    MsgBox "The CANCEL button was clicked, and the file was not imported."
End Sub

 

[timothybard]

My first thought is that the problem is with the following line:

SQL = "SELECT * FROM tblErrorList WHERE [Error ID]=" & errorInfo(1)

I'm guessing the SQL statement needs quotes around the value of errorInfo(1), like this:

SQL = "SELECT * FROM tblErrorList WHERE [Error ID]= '" & errorInfo(1) & "'"

 

[tvdij]

Much appreciated! Is it recommended to always put quotes around a string (in this case errorInfo(1)) even if there are no spaces in the string?

It's been awhile since I've built SQL statements (Maybe 1 year or so), so I don't quite remember.

Thanks again though!

 

[timothybard]

Did my suggestion work? Here is more info about the WHERE clause and the use of quotes: http://www.w3schools.com/Sql/sql_where.asp

 

[tvdij]

Your suggestion worked. The link you posted explained everything perfectly.

Thanks