Solved

ADODB MS Access Statement

Posted on 2011-03-11
2
218 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:ict-torquilclark
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now