Solved

Accessing Access database using Visual Basic .NET ADO library

Posted on 2006-06-12
3
568 Views
Last Modified: 2013-11-25
Hi,
 I am trying to write a VB command line program that can access ACCESS database to read/add/modify data into tables.
 The program stops right after accessing the database.  As a first step I  want to just read the tables. When I run it in debug more it stops right  when I call the function oRS.Open("Select * From object", oConn) with an excection.

The first question is, do I need to import the namespaces to use the library?
If I don't use it, the program compiles correctly, if I use it, VB.NET complains about a function that I use to filter the command line, Command.Split, saying that it's not a member of ADODB.Command (Why doesn it say that?)

From the command line this is the exception that it prints:

************************
Unhandled Exception: System.Runtime.InteropServices.COMException (0x80040E14): Syntax error in FROM clause.
   at ADODB.RecordsetClass.Open(Object Source, Object ActiveConnection, CursorTypeEnum CursorType, LockTypeEnum LockType
, Int32 Options)
   at tas_db_interface.Module1.DBConnection() in c:\02  temporary files\tas_db_interface\tas_db_interface\Module1.vb:lin
e 265
   at tas_db_interface.Module1.Main() in c:\02  temporary files\tas_db_interface\tas_db_interface\Module1.vb:line 362
***************************

Line 265 is oRS.Open("Select * From object", oConn).  It seems that it doesn't like the query.

To use the ADO library I added adodb.NET (Version 7.0.3300.0) to my project.

The function that I use to read the record has everything hard coded just to get it to work for now.
This is the code
Public Sub DBConnection()
        Dim oConn As New ADODB.Connection
        Dim oRS As New ADODB.Recordset
          oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb")
        oRS.Open("Select * From object", oConn) 'object is one of the tables included in db1.mdb
        ' or oRS = oConn.Execute("select * from object") ?
        While Not oRS.EOF
            Debug.Print(oRS.Fields(0))
            oRS.MoveNext()
        End While
        oRS = Nothing
        oConn.Close()
        oConn = Nothing
    End Sub

What am I doing wrong?

Once passed this issue it would be great to know how I can query the tables in my database.  Enter a new record and modify an existing one.

Thanks
Andrea
0
Comment
Question by:atoncelli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Expert Comment

by:prosh0t
ID: 16887586
Object might be a keyword... try putting brackets around it in your query:

"Select * From [object];"
0
 
LVL 7

Accepted Solution

by:
prosh0t earned 125 total points
ID: 16887665
Also.. if you're using .NET, it's really best to use the ADO.NET libraries.  Not the old traditional ADO libraries.  VB.NET doesn't make it very easy to work with the traditional one.  

With regard to traditional ADO, to add and modify data  you can use these recordset functions:

oRS.AddNew()   'adds a row

oRs.Fields("MyFieldName").Value = "myValue"

' and then to update:

oRs.Update()

Check out this post if you want to see some code showing how you would use the new ADO.NET libraries:

http://www.experts-exchange.com/Programming/Q_21877837.html

oh and if you do have a table named 'object' it might be best to change it to something more specific, so you can't get confused in your code or anything.

Good luck!


0
 

Author Comment

by:atoncelli
ID: 16894731
The suggestion was right to the point.  I guess 'object' is a key word because as soon as I changed that the error disappeared.
Thanks for the quick reply. I'll try the new ADO.NET libraries and see how it goes.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

749 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