Connecting Vb to Access

[ilovevbsomuchimmad]

Hi, I know im going to sound stupid but I need help with linking Vb.Net to access. I would prefer the most easy approach as I have not that much knowledge.

 

[HaworthBantam]

Mad,

That's a question with many answers.

I've just moved over to VB.net myself from VB6 in the last month, and linking to and creating "recordsets" from Access databases was one of the first things I had to get to grips with.

So far I've come up with 5 different ways of generating datasets, each with its own advantages depending on how the data is to be used. I'm still experimenting.

The FAQs above are a good start, I think there's two methods described there.

But really you need to play with dot net and discover what best works for you.

 

[AndyLee100]

I have just been doing the same thing in VB.Net.

I am using the classes in the OleDB namespace.

Use Imports System.Data.OLEDB at the top of your classes.

Then you can use oledbdataAdapters, oledbdataReaders, datarows and datasets.

 

[TipGiver]

ilovevbsomuchimmad,

from --> thread796-1332925 it seems that you know how to connect to an access db!

?

 

[RonRepp]

Maybe this will help. This opens the schema of the tables and views in separate subs. The schema are then loaded into a treeview.

Sub OpenTables()
Dim sCon as String="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;" 

Dim Con As ADODB.Connection = New ADODB.Connection
        Dim rsTBL As ADODB.Recordset = New ADODB.Recordset
         Con.ConnectionString = sCon 
        Con.Open(sCon )
        rsTBL = Con.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
        While Not rsTBL.EOF
            'MsgBox(rsTBL.Fields(3).Value)
            If rsTBL.Fields(3).Value = "TABLE" Then
                Dim NodeX As TreeNode = New TreeNode
                NodeX.ImageIndex = 6
                NodeX.SelectedImageIndex = 6
                NodeX.Text = rsTBL(2).Value

                TV1.Nodes.Add(TV1.SelectedNode.Nodes.Add(NodeX))

            End If
            rsTBL.MoveNext()
        End While
        rsTBL.Close()
        rsTBL = Nothing
        Con.Close()
        Con = Nothing
End Sub

Private Sub AddViews()
        Dim Con As ADODB.Connection = New ADODB.Connection
        Dim rsVW As ADODB.Recordset = New ADODB.Recordset
        'On Error Resume Next
        Con.ConnectionString = sCon 
        Con.Open(Con.ConnectionString)
        rsVW = Con.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
        While Not rsVW.EOF
            If rsVW.Fields(3).Value = "System VIEW" Then
                Dim NodeX As TreeNode = New TreeNode
                NodeX.ImageIndex = 12
                NodeX.SelectedImageIndex = 12
                NodeX.Text = rsVW(2).Value
            End If
            rsVW.MoveNext()
        End While

        rsVW.Close()
        rsVW = Nothing
        Con.Close()
        Con = Nothing    End Sub

To Open a Recordset

This example loads a recordset into a dataset, which is then set to a datagrid

If txtSQL.Text = "" Then Exit Sub
        Dim Con As ADODB.Connection = New ADODB.Connection
        Dim RS As ADODB.Recordset = New ADODB.Recordset
        Dim sCon As String = Label1.Text
        Dim DS As DataSet = New DataSet("DSName")
        Dim DR As DataRow
        Dim TBL As DataTable = New DataTable("Table")
        Dim DC As DataColumn
        Dim i As Integer, j As Int16
        SSPI = GetSSPI

        Con.ConnectionString = sCon
        Con.Open(sCon)
        Try
            RS.Open(txtSQL.Text, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, ADODB.CommandTypeEnum.adCmdText)

        Catch ex As Exception
            txtErr.Text = ex.Message
            Exit Sub
        End Try

        For i = 0 To RS.Fields.Count - 1
            DC = New DataColumn(RS.Fields(i).Name)
            TBL.Columns.Add(DC)
        Next
        Do While Not RS.EOF
            DR = TBL.NewRow
            For i = 0 To RS.Fields.Count - 1
                DR(i) = RS(i).Value
            Next
            TBL.Rows.Add(DR)
            RS.MoveNext()
        Loop
        DS.Tables.Add(TBL)
        DG1.SetDataBinding(DS, "Table")
End Sub

The above example assumes that you have 2 textboxes, txtERR and txtSQL.

Let me know if it works for you. I've had problems with the View portion when using SQL, but it works pretty well for Access.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.