Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Accessing Access database using Visual Basic .NET ADO library

Posted on 2006-06-12
3
Medium Priority
?
589 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
  • 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 500 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

876 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