ADODB MS Access Statement

I have written a vb.net app that executes a SQL Statement on a MSACCESS database.

It uses the attatched SQL code to query an access database.

if there is a number in the LoggedOn fields in the ACCESS database then it works fine

however if there is no number i get the error "Unspecified Error" on the "rs2.Open(sqlstr2, Local1stCnnString, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)" line of the code.

Can anybody see why?
Dim rs2 As New ADODB.Recordset
Dim sqlstr2 As String

sqlstr2 = "SELECT UserName FROM LocalUser WHERE LoggedOn <> 0"

rs2.Open(sqlstr2, Local1stCnnString, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

Open in new window

LVL 1
ict-torquilclarkAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you rather shall do it:
Dim cnn as ADODB.Connection
Dim rs2 As ADODB.Recordset
Dim sqlstr2 As String

set cnn = new ADODB.Connection
cnn.open Local1stCnnString

set rs2 = new ADODB.Recordset
set rs2.ActiveCOnnection = cnn
rs2.CursorType = ADODB.CursorTypeEnum.adOpenStatic
rs2.LockType = ADODB.LockTypeEnum.adLockOptimistic

sqlstr2 = "SELECT UserName FROM LocalUser WHERE LoggedOn <> 0"

rs2.Open sqlstr2

Open in new window

0
 
DhaestCommented:
One big remark: why are you still using adodb instead of ado.net ?
Dim connetionString As String
        Dim cnn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim sql As String
        Dim reader As OleDbDataReader

        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
        sql = "SELECT UserName FROM LocalUser WHERE LoggedOn <> 0"

        cnn = New OleDbConnection(connetionString)
        Try
            cnn.Open()
            cmd = New OleDbCommand(sql, cnn)
            reader = cmd.ExecuteReader()
            While reader.Read()
                MsgBox(reader.Item(0))
            End While
            reader.Close()
            cmd.Dispose()
            cnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

Open in new window

0
All Courses

From novice to tech pro — start learning today.