Not able to read data from access database

Question

I have a vb form in visual studio 2010 and I want to put data from it by importing the data from an access database. What I'm trying to create is a multiple answer quiz for my students. In my database I have 30 questions but I only want to randomly select 10. This is what my database look like. Database view

This the the code that I have for this part.

Dim provider As String
Dim dataFile As String
Dim connString As String
Public myConnection As OleDbConnection = New OleDbConnection
Public dr As OleDbDataReader


Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    dataFile = "Data Source =F:\Quiz\Programs\UNZipped\questions.accdb; User Id =admin; Password=password;"
    connString = provider & dataFile
    myConnection.ConnectionString = connString

    myConnection.Open()

    Dim str As String
    str = "SELECT Top 10 ID_Question From Questions ORDER BY RND(ID_Question)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader


    While dr.Read()

        Label3.Text = dr("ID_Question").ToString
        MsgBox("test")
    End While
    myConnection.Close()
    MsgBox("fsafa")
 End Sub

The code run with no errors but it seems like the connection does not work. I tried changing Microsoft.Jet.OLEDB.4.0 by Microsoft.Ace.OLEDB.12.0 like someone suggested on another similar question. I'm quite new at this kind of stuff so I'd like to get some help.

Thank you

Did you get any specific error message? What do you mean when you say _it seems like the connection does not work_? — Steve, Jan 27 '15 at 20:30
No error message. I've put some msgBox but none of them show — phil652, Jan 27 '15 at 20:31
Move that code to a button click event. The Load event is probably swallowing the exception. Make sure you are compiling to x86 in the My Project - Compile tab - Target CPU. — LarsTech, Jan 27 '15 at 20:37
An Access database in the format ACCDB cannot be opened by Microsoft.Jet.OLEDB.4.0 unless you have saved it in 2003 format. — Steve, Jan 27 '15 at 20:39
I moved the code to a button_click and I now get the error Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine. What do I need to change to make it work with an ACCDB extension — phil652, Jan 27 '15 at 20:44
You could get some useful tip from this question http://stackoverflow.com/questions/17716207/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine-w — Steve, Jan 27 '15 at 20:53

score 1 · Accepted Answer · answered Jan 27 '15 at 21:06

Microsoft.Jet.OLEDB.4.0 can be used only to read MDB files (or any extension you like provided that the file is in Access 2003 format). Also OLEDB.4.0 is a 32bit driver. If you compile your application with TargetCPU = AnyCPU AND you run your app on a 64bit system, the driver will be unusable. (Not Registered). Changing your TargetCPU to x86 fixes the problem both for 32bit and 64bit operating systems.

Instead, the scenario is more complex with Microsoft.ACE.12.0. You need to install different libraries for 32bit and 64bit, BUT you cannot install both on the same machine. Moreover, Office installs the same libraries compatible with its bit version. So, if the installed Office is the 64bit version you cannot install the 32bit version of ACE.12.0.

If you cannot control the running environment of your customers then I suggest to compile your app for 32bit and use the 2003 format for your database file or prepare two different releases of your app. One for 32bit and one for 64bit

And might I add Steve, the OP can check which drivers are installed by Start menu and searching ODBC Sources. If you have a 64bit machine, there will be two Data Sources to check usually: C:\Windows\System32\odbcad32.exe or C:\Windows\SysWOW64\odbcad32.exe...I had a similar issue getting Python to work with ODBC. — Parfait, Jan 27 '15 at 21:31

Not able to read data from access database

1 Answers1