[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Accessing Access database using Visual Basic .NET ADO library

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…

612 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