Help --> Connect to a DataBase

C

comcrack

Well-known member
Joined
Jun 14, 2003
Messages
52
Location
Canada
Hy

Im triing to connect to a Microsoft access database. This databass is there : c:\dbtest.mdb. After connecting I need to list the tables in a array and than list the columns of one of these tables in an other array.

How can I do that in VB.


Thank You

ComCrack
 
top of your forms code window you need this :
Code: 
Imports System.Data.OleDb
then you can do the following , making sure you specify your .mdbs location and table etc...
Code: 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/bin.mdb"
        Dim strCommand As String = "SELECT * FROM binFrm"
        OpenAccess(strconnection, strCommand)
    End Sub

    Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
        Dim DBCon As New OleDbConnection(strConn)
        DBCon.Open()
        Dim DBCommand As New OleDbCommand(strComm, DBCon)
        Dim DBAdapt As New OleDbDataAdapter(DBCommand)
        Dim DBset As New DataSet()
        DBAdapt.Fill(DBset, "binFrm")
        DataGrid1.DataSource = DBset.Tables("binFrm") /// add the entire access database to a datagrid.
        DBCon.Close()
        DBCommand.Dispose()
        DBAdapt.Dispose()
        DBset.Dispose()
    End Function
hope that helps.
 
you could try something like this :
Code: 
    Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
        Dim i As Integer, x As Integer
        Dim DBCon As New OleDbConnection(strConn)
        DBCon.Open()
        Dim DBCommand As New OleDbCommand(strComm, DBCon)
        Dim DBAdapt(2) As OleDbDataAdapter /// make an array of each table in your database.
        DBAdapt(1) = New OleDbDataAdapter(DBCommand)
        DBAdapt(2) = New OleDbDataAdapter(DBCommand)
        Dim DBset As New DataSet()
        DBAdapt(1).Fill(DBset, "binFrm")
        DBAdapt(2).Fill(DBset, "Addresses")
        For i = 0 To DBset.Tables.Count - 1
            For x = 0 To DBset.Tables.Item(i).Columns.Count - 1
                Dim strTable As String = DBset.Tables.Item(i).TableName
                Dim strColumn As String = DBset.Tables.Item(i).Columns(x).ColumnName
                MessageBox.Show("the tabel: " & strTable & "  Contains the following Columns:" & Chr(10) & strColumn)
            Next
        Next
        DBCon.Close()
        DBCommand.Dispose()
        DBAdapt(1).Dispose()
        DBAdapt(2).Dispose()
        DBset.Dispose()
    End Function
 
I triied this :

Code: 
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/dbtest.mdb"
        Dim strCommand As String = "SELECT * FROM binFrm"
        OpenAccess(strConnection, strCommand)
    End Sub

    Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
        Dim i As Integer, x As Integer
        Dim DBCon As New OleDbConnection(strConn)
        DBCon.Open()
        Dim DBCommand As New OleDbCommand(strComm, DBCon)
        Dim DBAdapt(2) As OleDbDataAdapter /// make an array of each table in your database.
        DBAdapt(1) = New OleDbDataAdapter(DBCommand)
        DBAdapt(2) = New OleDbDataAdapter(DBCommand)
        Dim DBset As New DataSet
        DBAdapt(1).Fill(DBset, "binFrm")
        DBAdapt(2).Fill(DBset, "Addresses")
        For i = 0 To DBset.Tables.Count - 1
            For x = 0 To DBset.Tables.Item(i).Columns.Count - 1
                Dim strTable As String = DBset.Tables.Item(i).TableName
                Dim strColumn As String = DBset.Tables.Item(i).Columns(x).ColumnName
                MessageBox.Show("the tabel: " & strTable & "  Contains the following Columns:" & Chr(10) & strColumn)
            Next
        Next
        DBCon.Close()
        DBCommand.Dispose()
        DBAdapt(1).Dispose()
        DBAdapt(2).Dispose()
        DBset.Dispose()
    End Function
[/VB]

But there is no table which have this name --> binFrm of this line SELECT * FROM binFrm

I change this line for --> SELECT * FROM UserTable

then the msgbox give me :

 "the tabel: binFrm  Contains the following Columns:
N
 
ok but I dont know the tables because I need to chose the database when the program is running, Its why I need to list the tables in the database.
 
Im a little concerned with why you would be accessing a db that you were so unfamiliar with....
Code: 
 oledb.Database.ToString()

will get you the database that you are accessing in the connection as a string.

There are ways to access the schema of a table including its column names and configuration, once you know the table you are interested in....but again, Im not sure, as a programmer how you could utilize a db without some significant knowledge of your resource. Unless that is the goal, in which case this is an inappropriate post, and should be terminated.

Jon
 
Dim dt As New DataTable()
Dim restrictions(3) As Object
restrictions(0) = Nothing
restrictions(1) = Nothing
restrictions(2) = Nothing
restrictions(3) = "TABLE"
_Connection.Open()
dt = _Connection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, restrictions)
_Connection.Close()

_Connection is an object of type OleDbConnection (the connection to your database)
now dt is a table containing all your table names in the database
 
Ok Im not really good in VB and I didnt understand what i had to do to have the tabless names.


Its for my php and asp editor. I need to enter to any database on my computer to take tables name and colunms name to make the programation of the asp easyer.


Thank you

ComCrack
 
Seems like this part is not working right

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:/dbtest.mdb"
Dim strCommand As String = "SELECT * FROM binFrm"
OpenAccess(strConnection, strCommand)
End Sub

Public Function OpenAccess(ByVal strConn As String, ByVal strComm As String)
Dim i As Integer, x As Integer
Dim DBCon As New OleDbConnection(strConn)
DBCon.Open()
Dim DBCommand As New OleDbCommand(strComm, DBCon)
Dim DBAdapt(2) As OleDbDataAdapter /// make an array of each table in your database.
DBAdapt(1) = New OleDbDataAdapter(DBCommand)
DBAdapt(2) = New OleDbDataAdapter(DBCommand)
Dim DBset As New DataSet
DBAdapt(1).Fill(DBset, "binFrm")
DBAdapt(2).Fill(DBset, "Addresses")
For i = 0 To DBset.Tables.Count - 1

Where you have

Dim strCommand As String = "SELECT * FROM binFrm

it fails on

DBAdapt(1).Fill(DBset, "binFrm")
DBAdapt(2).Fill(DBset, "Addresses")

both are showing the same information

If I change it to

Dim strCommand As String = "SELECT * FROM binFrm,addresses

then it will show for every table ALL the tables from both items
how to make it seperate?
 
Back
Top