none
Connecting Access .accdb database into VB 2010 by coding

    Question

  • Hi Everyone!,

    Previously i used Visual Studio 6 Professional Ed. Now on to 2010 Ultimate.

    I just want to know how to connect a .accdb (Access 2007 database) in VB 2010 and retrieve/edit/delete records....?

    In VB 6.0. i used the code as below to open a .mdb database file....

    xyz.mdb contains a table called "pwd" with fields "User" & "pwd"

     

    Public db As Database
    
    Public rspwd As Recordset
    
    Set db = OpenDatabase("xyz.mdb", False, False, ";pwd=abc") 'To Open Database
    
    Set rspwd = db.OpenRecordset("select * from pwd", dbOpenDynaset) 'To Open a table as a recordset
    

     

    To point a particular field in the "pwd" table i used "rspwd!User"

    can any one suggest the equivalent VB 2010 code


    • Edited by PBLNRAO Wednesday, September 14, 2011 6:21 AM
    Wednesday, September 14, 2011 6:12 AM

Answers

All replies

  • Hello PBLNRAO,

    Hi Everyone!,

    Previously i used Visual Studio 6 Professional Ed. Now on to 2010 Ultimate.

    I just want to know how to connect a .accdb (Access 2007 database) in VB 2010 and retrieve/edit/delete records....?

    In VB 6.0. i used the code as below to open a .mdb database file....

    xyz.mdb contains a table called "pwd" with fields "User" & "pwd"

     

    Public db As Database
    Public rspwd As Recordset
    Set db = OpenDatabase("xyz.mdb", False, False, ";pwd=abc") 'To Open Database
    Set rspwd = db.OpenRecordset("select * from pwd", dbOpenDynaset) 'To Open a table as a recordset
    

     

    To point a particular field in the "pwd" table i used "rspwd!User"

    can any one suggest the equivalent VB 2010 code



    try to follow this article.

    http://code.msdn.microsoft.com/Esempio-di-utilizzo-file-4d9b07a8

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, September 14, 2011 6:35 AM
  • Did you know that there probably are about 100.000 pages on Internet how to do what you ask?

    But as sample for your code.

             Dim dt As New DataTAble
            Using da As New OleDB.OleDBDataAdapter("select * from pwd", " Provider =Microsoft.ACE.OLEDB.12.0; Data Source =C:\xyz.accdb; Jet OLEDB:Database Password =MyDbPassword;")
                da.Fill(dt)
            End Using
            Dim TheResult = dt.Rows(0)("User") 'result in the User column in the first row 
    

    Be aware this is old fashioned. Use in future the integrated security. Every user in a modorn organization is not allowed to use his computer without passwords. This returns for you also direct the UserName from those who are using that computer.

    Dim TheCurrentUser = Environment.Username


    Success
    Cor

    Wednesday, September 14, 2011 6:37 AM
  • @ Carmelo La Monica, I don't understand what language it was in ur link.

    @ Cor, Thx a ton... for the help...

    One last Question....

    I just wanted to know how to seek a particular record in the table depending on the field (in our example "User") if "pwd" table has 10 records.

    And also thanks for guiding me to use the integrated security.

     

     

     


    Regards, PBL
    Wednesday, September 14, 2011 8:01 AM
  • Hi PBL,

    you would add what you are looking for in your select statement;

    "SELECT * FROM PWD WHERE User = 'fred'"

    if you wanted to have it so the user can change what they select you would add a parameter

    Dim Param As OleDbParameter = New OleDbParameter()
    Param.ParameterName = "@User"
    Param.Value = (TxtUser.Text).ToString
    cmd.Parameters.Add(Param)
    
    "SELECT * FROM PWD WHERE User=@User"

    Hope this helps

    Regards

    Aidan

     

    Wednesday, September 14, 2011 8:38 AM
  • PBL,

    Try to avoid to create follow up messages, these forums are also a knowledgebase for those searching for answers.

    So answering your follow up question can lead some to the wrong place.

    Simply mark all the answers which have given you an answer as answer, vote all messages as helpfull which were like that. 

    And create a new question. There is no limitation on that you know.

     


    Success
    Cor
    Wednesday, September 14, 2011 9:29 AM
  • Hello PBLNRAO,

    @ Carmelo La Monica, I don't understand what language it was in ur link.

    the article was created in two languages​​, then also make the English translation of the article.

    Regards.


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Wednesday, September 14, 2011 10:11 AM
  • There are a number of ways to work with an Access database. Below is a tutorial that should help:

    http://www.homeandlearn.co.uk/NET/nets12p1.html

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Proposed as answer by Mike FengModerator Thursday, September 15, 2011 10:41 AM
    • Marked as answer by PBLNRAO Monday, September 19, 2011 4:23 PM
    Wednesday, September 14, 2011 11:59 AM
  • Paul,

    thx for the help. the article which you suggested was awesome. but only thing is it did not cover the seeking of the particular record in from the dataset.

    i.e if i want to search the dataset for a particular record (in my example "User"). could u help me out in this...


    Regards, PBL
    Friday, September 16, 2011 12:10 PM
  • You can use the Find method of the DataRowCollection:

    http://msdn.microsoft.com/en-us/library/ydd48eyk.aspx

    Or, the Select method of the DataTable:

    http://msdn.microsoft.com/en-us/library/system.data.datatable.select.aspx

    Or, you can use LINQ:

    http://msdn.microsoft.com/en-us/library/bb552415.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by PBLNRAO Monday, September 19, 2011 4:24 PM
    Friday, September 16, 2011 1:39 PM
  • Paul,

    I think that an MVP has enough experience to know that it is not so well for those who are searching for answers if he find complete different answers than the question was. This is known by most forum members. Follow up messages are therefore not done by most of the community. Especially not if in the thread is told this, whoever that tells. 

    Why are you abusing those not written rules?


    Success
    Cor


    Friday, September 16, 2011 2:19 PM
  • Cor,

    You are probably right, but I'm not one who is petty enough to accuse people of doing that so you won't have to defend yourself. Some people do use the "throwing spaghetti at the wall to see what sticks" technique to try get posts marked as answers, but I just figure it's not hurting me and it's not my problem, so I just let it go.

    What do you think?

    ;-)

     

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 16, 2011 5:29 PM
  • @ Cor
    @ Paul

    I hope that is not related to me these two posts :)

     

    Bye


    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica

    Friday, September 16, 2011 5:50 PM
  • @ Cor
    @ Paul

    I hope that is not related to me these two posts :)

    I don't think so Carmelo. :-)


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 16, 2011 7:15 PM
  •  

    What do you think?

    ;-)

     

     

    Paul,

    My first thought was, does Paul think I'm a newbie to forums and newsgroups

    :-)

     


    Success
    Cor
    Saturday, September 17, 2011 9:54 AM
  • Paul,

    i have posted the question in new thread...

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/b43656db-2dff-4f46-840f-7c74bd162272

    kindly help me...


    Regards, PBL
    Monday, September 19, 2011 7:04 AM