Solved

Accessing Access database using Visual Basic .NET ADO library

Posted on 2006-06-12
3
570 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 66
Collapse and expand table in Word 2010 2 163
Graph API & MS Apps 1 83
Help with Classic ASP - Parameterizing Query 16 67
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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 is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

752 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