Connecting an Access database to VB6

[tyrone8888]

Hey

-Using visual basic 6 to connect to a Microsoft Access 2000 database
- "data" i think its called a data control not completely sure.

I'm quite new to visual basic 6 and would like some help designing a program.

I want to connect a combo box to a databae, for example if the user clicks on the combo box the names of the tables should appear. When the user clicks on the table name its attributes should be displayed in a list box below the combo box.

Can anyone help write the code to accomplish this

Thank you

[Hack]

How are you connecting to the database in the first place?

Are you using ADO code or a data bound control?

(Duplicate thread in Classic VB deleted. Please do not post the same question in multiple forum sections.)

[Dnereb]

The easiest way and of course most despised way by the hard core coders.
but since you are new this is at least the best way to experiment for now.
is using a ADODC controle to bind to a table in your database. (ADO data controle)
you can link the combobox to a specific field of this table.

[tyrone8888]

How are you connecting to the database in the first place?

Are you using ADO code or a data bound control?

(Duplicate thread in Classic VB deleted. Please do not post the same question in multiple forum sections.)

No to sure what you mean by ADO, but the database that i want the program to connect to is Microsoft Access. I'm using the "DATA" from the tool bar. I've used the following code to connect the combo box to the database but i can not select the tables that are displayed

Private Sub Form_Activate()
Dim strTableList As String

'In database if there are any tables that contain data add to
' the combo box

For Each t In datControl.Database.TableDefs
If t.Attributes = 0 Then
cboTables.AddItem (t.Name)
End If
Next
strTableList = strTableList & lstFields

[Hack]

Set a reference to the Microsoft ActiveX Data Objects Library

Paste this into a test project with one form and a Listbox on the form

Option Explicit
 
Private Cn As ADODB.Connection
Private rstSchema As ADODB.Recordset
Private strCn As String
 
Private Sub OpenDB()
      
   Set Cn = New ADODB.Connection
   strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\yourMDB.mdb;" & _
        "Persist Security Info=False"
 
   Cn.Open strCn
End Sub
 
Private Sub Form_Load()
OpenDb      
   Set rstSchema = Cn.OpenSchema(adSchemaTables)
 
 Do Until rstSchema.EOF
      List1.AddItem "Table name: " & rstSchema!TABLE_NAME & " " & "Table type: " & rstSchema!TABLE_TYPE 
      rstSchema.MoveNext
   Loop
   
   ' clean up
   rstSchema.Close
   Cn.Close
   Set rstSchema = Nothing
   Set Cn = Nothing
End Sub

[tyrone8888]

It is required that i use a DAO data control instead of the Microsoft ActiveX Data Objects Library. The code below connects the database to the combo box
Private Sub Form_Activate()
Dim strTableList As String
For Each t In datControl.Database.TableDefs
If t.Attributes = 0 Then
cboTables.AddItem (t.Name)
End If
Next

End Sub

The problem that i experiencing is allowing the user to select the table and to get it fields to be displayed in a list box

I've attempted to solve this problem by creating a command button , so when a table is selected from the combo box by pressing the command button will show the its fields
Heres the code
If Len(cboTables.Text) <> 0 Then
strCurrentTable = cboTables.Text
MsgBox strCurrentTable
datControl.RecordSource = "SELECT * from " & strCurrentTable
End If

Can anyone help me to change this code , so that it appears in the list box instead of a message?

[si_the_geek]

It is required that i use a DAO data control instead of the Microsoft ActiveX Data Objects Library.

Is there a very good reason for that?

DAO is obsolete, and will not work on many modern computers.. if you look at the VB6 help for that control (published in 1998), it explicitly says to not use it. Unless you have no option whatsoever, do not even consider using DAO.

If you want to be in control of what your program does, do not use a data control either - for the reasons see the "bound controls" link in Hacks signature, or the similar one in our Database FAQ's.


See the ADO Tutorial link in my signature for the ADO code equivalent of what you are doing - it's not that complex!

[tyrone8888]

Is there a very good reason for that?

DAO is obsolete, and will not work on many modern computers.. if you look at the VB6 help for that control (published in 1998), it explicitly says to not use it. Unless you have no option whatsoever, do not even consider using DAO.

If you want to be in control of what your program does, do not use a data control either - for the reasons see the "bound controls" link in Hacks signature, or the similar one in our Database FAQ's.


See the ADO Tutorial link in my signature for the ADO code equivalent of what you are doing - it's not that complex!

Thanks alot just read the "Why is using bound controls a bad thing" how do i install or gain access to "Microsoft ActiveX Data Objects Library" Do i need to go to Project>Components?

[si_the_geek]

Close.. Components is for controls, you need to go to Project->References, and select "Microsoft ActiveX Data Objects 2.x library".

For an explanation of how to use it, see the tutorial.

[tyrone8888]

Problem solved

[techgnome]

1) Please, please, please use [vbcode][/Highlight] tags around your code... it preserves the indenting and makes things easier to read.
2) .... Where do you get the error? There's a a nasty rumor flying about that we can read minds, but that's simply not true... well, most of us can't there might be one or two that can.

Once we know where the problem is, we can more acuratly answer the question.

-tg

[tyrone8888]

Set a reference to the Microsoft ActiveX Data Objects Library

Paste this into a test project with one form and a Listbox on the form

Option Explicit
 
Private Cn As ADODB.Connection
Private rstSchema As ADODB.Recordset
Private strCn As String
 
Private Sub OpenDB()
      
   Set Cn = New ADODB.Connection
   strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\yourMDB.mdb;" & _
        "Persist Security Info=False"
 
   Cn.Open strCn
End Sub
 
Private Sub Form_Load()
OpenDb      
   Set rstSchema = Cn.OpenSchema(adSchemaTables)
 
 Do Until rstSchema.EOF
      List1.AddItem "Table name: " & rstSchema!TABLE_NAME & " " & "Table type: " & rstSchema!TABLE_TYPE 
      rstSchema.MoveNext
   Loop
   
   ' clean up
   rstSchema.Close
   Cn.Close
   Set rstSchema = Nothing
   Set Cn = Nothing
End Sub

Been through the tutorial which as been extremely helpful. your code works perfectly fine apart from that I want the user to be able to select a table name from a combo(which is connected to the database) For example student, course, etc. Then when the user clicks on the student name I want the attributes to be shown in the list box. For example iDstu, Name, Surname etc. How can i adjust your code to reflect this?

[tyrone8888]

the code below is my attempt to add the name of the table to the combo box

Private Sub Form_Activate()
'Dim strTableList As String
'Set rstSchema = Cn.OpenSchema(adSchemaTables)

Set Cn = New ADODB.Connection ' We've declared it as a ADODB connection lets set it
' This is the connection string - is setting the provider to the Jet Engine and version 4.0.
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\my documents\newResults.mdb"

cboTables.AddItem "Table name: " & rstSchema!TABLE_NAME & " " & "Table type: " & rstSchema!TABLE_TYPE
rstSchema.MoveNext

[si_the_geek]

I presume that just adds one item to the list - to add multiple you need to use a Do Until .. Loop like in Hack's example.

..and as TG said, when you post code please put it inside VBCode tags so it is displayed in a more readable way - either using the button in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: [vbcode] 'code here [/vbcode]

[tyrone8888]

Here's my attempt to to display the table names in the combo box, this works perfectly fine apart from that it display unnecessary information in the combo such as MSys
Access Objects, MSysACEs,MSysObjects. All i want is thecombo box to display these three tables which are Attainment,Student Module.

[Highlight=VB]Option Explicit

Private Cn As ADODB.Connection ' This is the connection
Private rstSchema As ADODB.Recordset ' This is the recordset
Private strCn As String ' string to be used in the code
Public strSelectedItem As String

Private Sub OpenDB()
   Set Cn = New ADODB.Connection  'Declared it as a ADODB connection
   strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Documents and Settings\newResults.mdb"  'A connection string that is connected to the database
   Cn.Open strCn
End Sub
Private Sub Form_Load()
' retrieve all the information from the database and places it in the combobox
OpenDB
   Set rstSchema = Cn.OpenSchema(adSchemaTables)
 
 Do Until rstSchema.EOF
    cboTables.AddItem " " & rstSchema!TABLE_NAME
      rstSchema.MoveNext
   Loop
  
End Sub

[si_the_geek]

if you look at the help for adSchemaTables/OpenSchema, you can see that the recordset that is returned contains several fields.. one of these is TABLE_TYPE, which can have several values including "Table" (an actual table) "View" (a view), and specifically for Access it can also be "Access Table" (one of the system tables).

To ensure that you only get valid table names, only add items to the list if TABLE_TYPE is Table (or View if you want to allow that).


Note that when you have finished with this recordset, you need to close it properly (as you should do with all recordsets & connections etc), eg:

rstSchema.Close
  Set rstSchema = Nothing

[tyrone8888]

if you look at the help for adSchemaTables/OpenSchema, you can see that the recordset that is returned contains several fields.. one of these is TABLE_TYPE, which can have several values including "Table" (an actual table) "View" (a view), and specifically for Access it can also be "Access Table" (one of the system tables).

To ensure that you only get valid table names, only add items to the list if TABLE_TYPE is Table (or View if you want to allow that).


Note that when you have finished with this recordset, you need to close it properly (as you should do with all recordsets & connections etc), eg:

rstSchema.Close
  Set rstSchema = Nothing

I understand what your saying but i do not understand how to do this. Could you help me

[si_the_geek]

Assuming you want just tables (and not Views too), you can change the loop to this:

Do Until rstSchema.EOF
      If UCase(rstSchema.Fields("TABLE_TYPE").Value & "") = "TABLE" Then
        cboTables.AddItem " " & rstSchema.Fields("TABLE_NAME").Value
      End If
      rstSchema.MoveNext
   Loop
 
   rstSchema.Close    'close recordset
   Set rstSchema  = Nothing
 
 
   Cn.Close   'close connection (you probably want to move this to Form_Unload)
   Set Cn = Nothing

[tyrone8888]

Thanks alot si_the_geek that works absolutely perfectly. Can you tell me how I can allow the user to select the table name from the combo box and its individual fields show in a list box? For example the user clicks the combo box a list of tables called department, employee appear. The user clicks department and its fields which are de_ID, Name and Room show in the list box. If the user clicks on employee the department fields are removed and replaced with the employee fields which are em_ID, Name, Surname and Address

[si_the_geek]

You just do basically the same thing again... but with a different schema recordset, which has appropriate fields. That would be adSchemaColumns, which has (among others) TABLE_NAME and COLUMN_NAME.

The IF statement simply needs to check that the Table_Name matches your selection.

[tyrone8888]

You just do basically the same thing again... but with a different schema recordset, which has appropriate fields. That would be adSchemaColumns, which has (among others) TABLE_NAME and COLUMN_NAME.

The IF statement simply needs to check that the Table_Name matches your selection.

Can you check if my attempt is right? also where do i place the code i was thinkin in the list box.

' retrieve all the information from the combobox and places it in the listbox

OpenDB
   Set rstSchema = Cn.OpenSchema(adSchemaColumns)
 
Do Until rstSchema.EOF
      If UCase(rstSchema.Fields("TABLE_TYPE").Value & "") = "cboTables" Then ' if the table type is equal to table
        lstFields.AddItem " " & rstSchema.Fields("COLUMN_NAME").Value ' Add name of fields to the listbox
      End If
      rstSchema.MoveNext
   Loop
 
   rstSchema.Close    'close recordset
   Set rstSchema = Nothing
End Sub

[si_the_geek]

That's good, but line 5 should be like this:

If UCase(rstSchema.Fields("TABLE_NAME").Value & "") = cboTables.Text Then

Note that you don't need line 1 as the OpenDB sub should only be called once; Also, you should close the connection when the form closes.. to do that put the following into Form_Unload:

Cn.Close
Set Cn = Nothing