Connecting Access with VB.Net in Visual Studio 2005 or 2008

[Zaid Pirwani]

Hi, I need help connecting an access file with my project I am working on in Visual Studio 2008 (I also have Visual Studio 2005). I know how to connect to SQL databases but access is totally new to me. My friend told me that just add it through the "Add data source wizard" and then drag the columns you need from the "Data Sources" side bar. I did that the connection was established and I the navigation bar was also created automatically, when I ran the program, it was showing the records from my access file, I was able to add or delete records but the changes I made to the database are just temporary and are not saved in the database (THIS IS WHERE I NEED HELP):confused: Please answer this question, I am stuck because of this problem, and please answer it as descriptively as possible because I am new to coding and don't know much. :confused:I don't know ADODB or anything else like that but am willing to try anything just want to be able to access, edit and update any access file from within my program. :rose:Thanks for your help in advance.:rose:

[Christian Graus]

I thought you just create a connection string with the path to your access file ?

Christian Graus No longer a Microsoft MVP, but still happy to answer your questions.

[Ravi Kumar Tyagi]

Use the following connection string using the OleDb dataadapter to make the connection Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DOT_NET\DB\db1.mdb;Persist Security Info=False and after update the data please close the conenction and release the created object. Please let me know if u want sample code for the same.

[Zaid Pirwani]

Thankyou very much:rose: PLEASE SEND ME THE SOURCE CODE I have made the connection, it is working, but please make a sample program for me, which would be able to recieve, add, change, update and delete the records from access files. Your help is greatly needed as I am stuck on my project. :( :rose:Thanks for all your current and any future help I may recieve from you.:rose:

modified on Wednesday, July 30, 2008 11:31 AM

[Vimalsoft Pty Ltd]

Good Morning Muhammad Zaid Pirwani The Problem when using the Wizard is that you will never understand what is wrong when something is wrong. i suggest you start writting code and connect to your Database your self. its simple. You are going to do the Following In your Project as you know you have to import the Following

Imports System.Data
Imports System.Data.OleDb

And your code for Database Access , should be Something like this

'Connection String for the Access Database
Dim strCon As String = "Data Source=C:\Valuation Roll\ValuationRoll_CD.mdb; Provider=Microsoft.Jet.OLEDB.4.0;persist security info=False;User ID=Admin"

Dim con As  new OleDbConnection(strCon) 'Declaring a Connection Object

Dim cmdselect As new OleDbCommand 'Declaring a Command object

Dim da As new OleDbDataAdapter 'Declaring an Adapter
    
    Dim dsdata As New DataSet

    cmdselect.CommandText = "SELECT \* FROM mytable"

    cmdselect.CommandTimeout = 0

    cmdselect.CommandType = CommandType.Text

    cmdselect.Connection = con

    da.SelectCommand = cmdselect

    Try

        con.Open()

        dsdata.Clear()

        da.Fill(dsdata, "mytable")

        con.Close()

    Catch

        Throw

    End Try

And you can Bind your Control , mybe a Datagrid like this

Try

        datagrid1.DataSource = ds

        datagrid1.DataMember = "mytable"


    Catch ex As Exception
        MessageBox.Show(ex.Message)


    End Try

and Walla its Simple , Check my Articles about doing Database work without relying on Wizards. Wizards dont give you the Flexebility and they will never allow you to learn, as you say you are new to coding , you have to start coding and stop using Wizards Hope this Helps

Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za

[Zaid Pirwani]

I have applied your method and it is working I am retrieving data from the database THANK YOU VERY MUCH:rose::rose::rose::rose: Still working on how to change, update, add and delete records from the database though:confused: where can I see your articles your sites are showing the "cpanel" page and not the actual content. Will ask if get some other problem Again Thanks:rose::rose::rose:

[Zaid Pirwani]

Could you please give me a sample program or tell me where I can get one which would be able to retrieve, change, delete and add records in an access database. I would be very grateful if you would assist me in this matter. Thanks for your any help in advance.:rose::rose::rose:

[Vimalsoft Pty Ltd]

Good Morning Here is the Article where i show how to Update ,Delete and Add http://www.codeproject.com/KB/cs/N-Tier22.aspx[^] http://www.codeproject.com/KB/cs/NTier.aspx[^] Hope it helps :)

Vuyiswa Maseko, Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding VB.NET/SQL7/2000/2005 http://vuyiswamb.007ihost.com http://Ecadre.007ihost.com vuyiswam@tshwane.gov.za