dcsimg
Results 1 to 12 of 12

Thread: I have connected to an Acceaa db (2010) How Do I read records in that db using sql?

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Unhappy I have connected to an Acceaa db (2010) How Do I read records in that db using sql?

    I the following code is what I used in XOJO but need help making it work in VB.

    Imports System.Data
    Imports System.Data.OleDb

    Public Class OpeningForm1
    Dim DB_Connection As OleDbConnection

    Private Sub OpeningForm1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    DB_Connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = c:\00_VB_2010\MyDataBases\1RecordDB14.accdb")
    DB_Connection.Open()
    NOTE This works to this point.
    -------------------------------------------------------------------
    My Problem is that I want to run the following SQL to my access db
    I expect that I need something like this but I'm just guess (Can't find anything like this in my manual and I don't word a search for it.

    dim DB__01 as SQLiteDatabase not a part VB
    dim strAA as string
    Dim RS01 As RecordSet not a part of VB
    RS01 = DB__01.SQLSelect("SELECT * FROM Table_One where (Record_Type) = 'SETUP 0' ORDER BY Record_Type asc ")
    'Read the test SETUP 0 record.
    --------------------------------------------------------------------------------------------------------
    If I can get the above coding or it's equivalent working I think I'm OK and can run the following as a test.

    While Not RS01.EOF
    However this might not be the right code.
    strAA = RS01.IdxField(2).StringValue
    RS01.MoveNext
    wend

    Thanks for any help you can give me because I'm stuck.

    Dave

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    Welcome to VBForums

    For several examples (which cover most situations), see the article Retrieving and Saving Data in Databases , which is one of the resources listed in our Database Development FAQs/Tutorials (at the top of the Database Development forum)

    The examples use SQL Server based objects (SqlConnection and SqlCommand, etc), but you only need to change the first part of the name to OLEDB instead (eg: OLEDBConnection and OLEDBCommand, etc)

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    Thanks for the help I changed my code to the following after I connected to the DB.

    Dim rs1 As New OleDbCommand
    Dim SQLSelect As OleDbConnectionStringBuilder
    rs1 = CType(SQLSelect("SELECT * FROM tblAccessDB14 "), OleDbCommand)

    The error I get is: 'Object reference not set to an instance of an object.

    I have no idea what this means.

    I will start to look at the threads you pointed out.

    Dave

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,118

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    I have no idea why you have decided to use a ConnectionStringBuilder (you already had a working connection string), or why you attempted to run an SQL statement against it.

    Following one of the examples in the article I linked to is the way to go

  5. #5

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    thanks for trying to help but I guess I'm just to dense to get my head around something new like your database example shows. I'm still trying to use the principles I've been using since VB4 came out and at 72 years old I'm just too stubborn to readily change to the way you guys are doing things. I'm sure it's better than the old way I'm doing it.

    I really wish I could change that quickly to your new way of doing things because I sure it's better but I've never heard of anything in this line of code: Using reader As SqlDataReader = command.ExecuteReader()

    I just need to get away for a while (days probably) I just can't believe that VB 2010 is so new that I can't do it the old way. That's what I can't get through my head.

    Thanks again for trying to help; I can tell you are really good but right now your are talking English and I'm hearing German LOL
    Dave

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,788

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    That CodeBank thread of mine provides examples for various scenarios so the first thing to do is to identify which scenario matches what you're trying to do. You can then examine the example for that scenario, gain an understanding of what it's actually doing (ask questions if you need to) and then make the appropriate modifications to have the code work in your specific circumstances.

    The point of using a connection string builder is to build a connection string from parts. If you already have a complete connection string then using one is pointless.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,740

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    Visual Basic .Net uses ADO .Net for working with databases. If your use to VB4 then you were probably using Dao or ADODB. If you have MS Access available you can still use Dao/Adodb, with VBA. You actually can use Adodb in VB .Net but you would need to set a reference to MS Data Object library. Most people advise against using Adodb . in .Net.

    what is it your trying to do? Is this just a small hobby project or are you trying to create a business project? Maybe a video would help, just Google "visual basic connecting to access database" and then select videos. Make sure the video is about VB .Net and not VB6.
    Last edited by wes4dbt; Sep 8th, 2018 at 03:12 PM.

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    wes4dbt
    You have me figured out. I'm working on my own Bible program and I have an example of ADODB working in Access 2010 and was trying to move that procedure to visual basic 2010 and since vb 2010 is much more powerful it should have been able to handle ADODB too but you saw how that turned out.

    I guess when I see the video's I'll see if I can figure out what to do.

    Thanks again for your help at least now I know what is going on.
    Best wishes
    Dave

    PS if this doesn't work I can go back to access 2010 and limit the scope of my program.

  9. #9

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    wes4dbt
    You have me figured out. I'm working on my own Bible program and I have an example of ADODB working in Access 2010 and was trying to move that procedure to visual basic 2010 and since vb 2010 is much more powerful it should have been able to handle ADODB too but you saw how that turned out.

    I guess when I see the video's I'll see if I can figure out what to do.

    Thanks again for your help at least now I know what is going on.
    Best wishes
    Dave

    PS if this doesn't work I can go back to access 2010 and limit the scope of my program.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,740

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    There are major differences between VBA and VB .Net. Not sure it would really be worth the time to learn if your goal is a single user database system like you described. What is it that you hope to gain in VB? Is there something you have in mind? Also you can get help on specific VBA questions here in the Office Development Forum.

  11. #11
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,231

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    Hi,

    here a simple example with a Sql-Search-Statement

    you will have to adjust the Access-Version part.

    Code:
    Imports System.Data.OleDb
    
    
    Public Class Form1
    
      
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sDB As String = "D:\Adressen.mdb"
            Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & sDB & ";"
    
            Dim Cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(sCon)
            Cn.Open()
            'search with the letter c
            Dim sSearch As String = "'c" & "%'"
            Dim sSQL As String = "Select * From Products Where ProductName Like " & sSearch & _
                                 "Order By ProductName, UnitsInStock"
    
            Dim Cmd As New OleDb.OleDbCommand(sSQL, Cn)
            Dim Dr As OleDb.OleDbDataReader
            Dr = Cmd.ExecuteReader
    
            With ListView1
                If .Columns.Count = 0 Then
                    .View = View.Details
                    .Columns.Add("ProductName", 250)
                    .Columns.Add("UnitsInStock", 150)
                End If
    
                .Items.Clear()
                .BeginUpdate()
                Do While Dr.Read
                    Dim Li As New ListViewItem
                    Li.Text = Dr("ProductName").ToString
                    Li.SubItems.Add(Dr("UnitsInStock").ToString)
    
                    .Items.Add(Li)
                Loop
                .EndUpdate()
            End With
    
            Dr.Close()
        End Sub
    End Class
    but like wes said what are you gaining ? If your programm runs fine in Access-VBA why change ?

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    New Member
    Join Date
    Sep 2018
    Posts
    6

    Re: I have connected to an Acceaa db (2010) How Do I read records in that db using s

    To wes and Chris
    You guys are making a lot of sense; I am more comfortable with access 2010 and Its VBA.

    My problem is that I've never really looked at it as a full blown programming language before.
    Thanks to you guy and a good nights sleep my eyes have been opened and I will go that route.

    The only problem that I can see is random numbers but I can probably get them off the time of day if I have too.

    Thanks so much for clearing up the muddy water for me. I now see access 2010 as a basic programming language with an attached database. Before I saw access 2010 as a database with a few attached rules.

    Thanks again for the help it really got me out of a dark place.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width