Coding to link Visual basic to MS Access.

Programs can be written in Visual Basic to access Microsoft access databases. Some programming concepts such as subroutines and looping along with knowledge of databases and SQL may be required to write programs to link Microsoft Access database to Microsoft Visual Basic code. The ADODB object is used to create a connection to the database and access the records using ADODB.connection and ADODB.recordset APIs respectively. SQL is used to search the database for records that match the information provided as parameters to the subroutines. Data in the records can also be manipulated in the Visual Basic program code. Coding to link Visual basic to MS Access

Coding to link Visual basic to MS Access

• Issue
• Solution
• Note
• See also: How to connect vb with ms access

Issue

Hello,
I need to know the coding to link visual basic to MS Access!

Solution

You can try this:

Option Explicit   
Dim conn As ADODB.Connection, rec As ADODB.Recordset   
Dim esql As String, esql2 As String, searchvar As String   
Private Sub Command1_Click()   
Text1 = ""   
Text2 = ""   
Text3 = ""   
Command4.Visible = True   
Command1.Visible = False   
Text1.SetFocus   
End Sub   

Private Sub Command2_Click()   
If Not rec.EOF Then   
rec.MoveNext   
Else   
rec.MoveLast   
End If   
GetText   
End Sub   

Private Sub Command3_Click()   
If Not rec.BOF Then   
rec.MovePrevious   
Else   
rec.MoveFirst   
End If   
GetText   
End Sub   

Private Sub Command4_Click()   
On Error GoTo 1   
If Text1 = "" Or Text2 = "" Then   
Command4.Visible = False   
Command1.Visible = True   
Exit Sub   
End If   
rec.AddNew   
rec.Fields(0) = Text1   
rec.Fields(1) = Text2   
rec.Fields(2) = Text3   
rec.Update   
If Not rec.EOF Then rec.MoveNext   
rec.MoveFirst   
GetText   
Command4.Visible = False   
Command1.Visible = True   
Exit Sub   
1   
MsgBox ("duplicate value") & Text3   
End Sub   

Private Sub Command5_Click()   
Text1 = ""   
Text2 = ""   
Text3 = ""   
searchvar = InputBox("enter item to find")   
rec.Close   
rec.Open ("select * from TestRavi where First=" & "'" & searchvar & "'"), conn, adOpenStatic, adLockReadOnly   
If rec.Fields(0) <> "" Then   
Text1 = rec.Fields(0)   
Text2 = rec.Fields(1)   
Text3 = rec.Fields(2)   
Else   
MsgBox ("No matching records found")   
rec.Close   
rec.Open ("select * from testravi"), conn, adOpenDynamic, adLockOptimistic   
GetText   
End If   
End Sub   
'for integers use   
'Dim searchvar2 As Integer   
'searchvar2 = InputBox("enter Number")   
'rec.Open ("select * from TestRavi where First=" & searchvar2), conn, adOpenStatic, adLockReadOnly   
Private Sub Form_Load()   
Set conn = New ADODB.Connection   
Set rec = New ADODB.Recordset   
'conn.Open ("Provider=Microsoft.Jet.OLEDB 4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist Security Info=False")   
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist Security Info=False"   
conn.Open   
esql = "select * from TestRavi"   
rec.Open (esql), conn, adOpenDynamic, adLockOptimistic   
GetText   
End Sub   

Private Sub Form_Unload(Cancel As Integer)   
rec.Close   
conn.Close   
Set conn = Nothing   
command1.visible=false   
End Sub   
Private Sub GetText()   
If rec.BOF = True Or rec.EOF = True Then Exit Sub   
Text1 = rec.Fields(0)   
Text2 = rec.Fields(1)   
Text3 = ""   
End Sub

Note

Thanks to Catgurl323 for this tip on the forum.