how to establish conection between vb and MS Access

[sumukha1]

How to establish a connection between VB 6.0 and MS Access 2000.....please help me what code has to be used since im new to this subject ....im feeling its bit complicated

 

[johnwm]

What have you tried from my answer in thread222-1406082 ?

Have you read faq222-2244 yet? It advises against multiple posting on the same subject.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Andrzejek]

This question comes up often, so I've created a little tutorial here:

http://www.geocities.com/vb_stuff/ADODB.html

Have fun.

---- Andy

 

[zemp]

check the FAQs section as well.

faq222-3735

zemp

 

[tedsmith]

Personally I hate the DataEnvironment and ADO controls!

This code originated from various sources lost in time.
Easy way to directly show in VB an access type table without all the bother of an ADO control or dataenvironment, create a datagrid1 and manually create blank columns you need and assign each column to the field in the access table you want in the datagrid properties.
Otherwise you can use the usual FindFirst etc commands in code Eg. RS.FindFirst Criteria if you just want the output from or change one field.
I have Microsoft Data formatting Object Library 6, DAO 3.6 Object library and ActiveX data objects 2.5 selected in Project references.

Sub MyVBForm_Load
'This sets up the connection from the datagrid to the access table
Dim CN As New ADODB.Connection ' Conection tool
Dim RS As New ADODB.Recordset ' Recordset tool
Criteria = "SELECT * FROM Mytable ORDER by [MyIDField]" 'set some criteria
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" C:\MyFolder\MyAccessDatabase.mdb" ' Open database conection
RS.CursorLocation = adUseClient 'must have this
RS.Open Criteria, CN, adOpenStatic, adLockOptimistic ' Open the recordset
Set DataGrid1.DataSource = RS ' set DataGrid to have the content of the db using the SQL statement above
' The datagrid is now showing Mytable so data can be changed
End Sub

You can alter any data on the table but you must Update the database to make the changes permanent.
You can change the SQL statement to be similar to an Access query if you only want particular records. Remember to use the convention WHERE [MyName]=' " & VBForm.MyName.Text & " ' " when querying a string and # # around a date (must be in USA format) Execute another RS.Open and recordsource statement to show any changes.

You have to save any changes. Use a 'Save' button that appears if you click on the datagrid then -

sub Datagrid1_Click()
'show save button
SaveDataButton.visible=true
end sub

'Then update the database
Private Sub SaveDataButton_Click
'save any changes
RS.Update
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing
SaveDataButton.visible=false
End Sub

Also you need a "new record" button to add a new row

There are a number of variations on this you can find by searching.

 

[BobRodes]

< Dim CN As New ADODB.Connection

See faq222-6008 for reasons to break this into two lines.