VB.NET and Access 2000 DB's

[JadeWolf324]

im trying to re-write an application that is rather dated (VB6 and Access 97)

but im not sure how to go about sending my data to the DB and Retrieving it

from the DB in VB.NET.

does anyone know of some really good tutorials online i can look at for this?

or perhaps if someone wanted to be really awsome could write me an example

code.

either way i will be super grateful!

- D :happy:

[+virtorio MVC]

I recently had to do a project using VB.NET and Access. I haven't the time to write any example code but here are a few classes that should get you started, and they are quite easy to use:

in System.Data.OleDb

OleDbConnection = makes connection to database

OleDbCommand = Allows you to use SQL

OleDbDataReader = Good for reading SQL SELECT queries

[JadeWolf324]

is access and SQL conisdered the same thing in VB.NET programming?

[+virtorio MVC]

  JadeWolf324 said:

is access and SQL conisdered the same thing in VB.NET programming?

I have dodged Access programming all the way though, so I am not sure how similar it is, but in VB.NET you have to use the classes provided by the .NET framework.

In the OleDbCommand class, you can put SQL commands into it, like so:

  Quote

Dim command as OleDbCommand = new OleDbCommand("SELECT * FROM MyTable", myConnection)

Dim reader as OleDbDataReader = command.ExecuteReader()

SQL is SQL, no matter what language you use.

[jugomkd]

Hi this is short example how to connect to MS Access DB and retrieve the records from ... follow me

  Quote

this is part of the book MS VB.NET step by Step

1. Start Visual Studio .NET, and create a new Visual Basic Windows Application project named My ADO Form

2. On the View menu, click the Server Explorer command

3. Click the Connect To Database button in Server Explorer

4. Click the Provider tab in the dialog box

5. Click Microsoft Jet 4.0 OLE DB Provider on the Provider tab

6. Click the Next button to display the Connection tab of the Data Link Properties dialog box

7. Click the ellipsis button next to the Select Or Enter A Database Name field, select the your.mdb database

8. Click the Test Connection button on the Connection tab

9. Click OK in the Test Connection Succeeded message box to continue, and then click OK in the Data Link Properties dialog box

10. Open the Data Connections node, the ACCESS node, and finally the Tables node in Server Explorer

Creating a Data Adapter

1. Open the Toolbox, and click the Data tab

2. Drag the OleDbDataAdapter control from the Data tab of the Toolbox to the form

3. Read the opening statement about data adapters, and then click Next

4. Click Next to continue configuring the data adapter

5. Click Next to accept the Use SQL Statements option

6. Click the Query Builder button to build your SELECT statement graphically

7. Click (your intended columns name that want to appear), click Add, and then click Close

8. In the table, click the check boxes next to the columns you want to use for further i.e. nameID and secondName

9. Click OK to complete the SELECT statement

10. Click the Finish button

Working with Datasets

1. Click the form to make sure that it?s active

2. Click the Generate Dataset command on the Data menu

3. In the New box, set the name of the new dataset to (your choice)

4. Verify that the Add This Dataset To The Designer check box is selected so that Visual Studio will add the new dataset to the component tray

5. Click OK to create a dataset for the Instructor field and add it to your projecUsing Bound Controls to Display Database Information on a Formb>

1. Use the TextBox control to draw a text box object in the middle of the form

2. Use the Button control to draw one button object in the lower left corner of the form

3. Click the text box object on the form, and then open the Properties window

4. Open the DataBindings category, click the Text property, and then click the drop-down arrow

5. Click the plus sign (+) to expand thyourb> dataset, and then expand thyour b>table beneath it

6. Click the intended field to select it as the field that will be displayed in the text box. Be sure to click the "name" text and not the icon; otherwise, the your field won?t be selected.

9. Restore the Properties window, double-click the button, and then type the following program code in the button1_Click event procedure of the Code Editor:

dataSet1.Clear()
OleDbDataAdapter1.Fill(your item)

that's all

i hope it works ... i've done this hunderts times and it works great for me

kind regards

[kl33per]

  virtorio said:

SQL is SQL, no matter what language you use.

Ha, I wish. SQL varies with different DBMSs. MsSQL, MySQL, and others, all have slightly different implimentations of SQL, along with custom datatypes and commands.

[jugomkd]

  Quote

all have slightly different implimentations of SQL, along with custom datatypes and commands

it could be true but not in whole i.e. MS SQL Server has T-SQL that has some additional power but generally when you use "Insert" "Select" and "Update" SQL is same for all database whatever server or file DB ..... so if you work within SQL Server it's true that SQL is different and if you make stored procedures but within VB.NET IDE or whatever else process is same ... FoxPro has many differency inside its SQL structure (language) but if you want to select item from .. you use same SQL statement "SELECT * FROM" as with MS Access DB

kind regards

[JadeWolf324]

  jugomkd said:

Hi this is short example how to connect to MS Access DB and retrieve the records from ... follow me

is this for writing to or reading a Access DB? i cant quite understand? but that has been VERY helpful. alot more

than anything else.

[jugomkd]

This was to select items and show them in TExtBox windows form ..... by the way example doesn't stop here but it continues further with prev/next butons also first/last etc. but mainly you got the idea ...when you ones have your connection established and dataset filled with records from your db you can do whatever you like with 'em ... means Select, Insert, Update and whatever else ... i hope you understand this ... my english is not very well .... lol

Kind regards