• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Manage an ADO database in VB 6

I am using ADO with VB 6.  I want to be able to check programmatically whether a specified table ("Tasks") is already in the database and, if it is not, to add it programmatically.  Of course I then want to programmatically add fields to the new table.  I have so far tried using OpenSchema with adSchemaTables (Set rs = aDBCn.OpenSchema(adSchemaTables) but that is not returning an records to process.
0
edwardr
Asked:
edwardr
  • 4
  • 2
1 Solution
 
DhaestCommented:
Check whether table exist in db

'under project_References, check Microsoft ADOX ext

Dim conn As ADODB.Connection, cmd As ADODB.Command, adu As ADOX.Catalog
'change path here
Const dbpath = "D:\Mydocs\My Documents\test_1.mdb"

Private Sub Command1_Click()
Dim tabla As ADOX.Table
Set tabla = New ADOX.Table
List1.Clear
For Each tabla In adu.Tables
If Left(tabla.Name, 5) <> "Tasks" Then List1.AddItem tabla.Name
Next
Print adu.Tables.Count
End Sub

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
Set adu = New ADOX.Catalog

conn.Open "provider=Microsoft.jet.oledb.4.0; data source=" & dbpath
Set adu.ActiveConnection = conn
End Sub
0
 
DhaestCommented:
Alternative:

ADO Connections have a schema property that you can query.
You need to create a results set to hold the schema, something like


Code:
Dim rsSchemas
set rsSchemas = utilitiesDBcmd.OpenSchema(adSchemaTables)

'then I assume something like (haven't tested it)
Do While Not rsSchemas.EOF
   If rsSchemas("TABLE_NAME") = prod_rpts Then
         'code if true
   else
        ' code if false
   end if
   rsSchemas.MoveNext
loop

http://www.devguru.com/Technologies/ado/quickref/connection_openschema.html
0
 
edwardrAuthor Commented:
Thanks.  However, it's not doing me any good.

Here's the connection string for the connection:
Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=F:\Brentonn\MCARTSI\Projects\GUTE\SrcBin\BUTE+.MDB;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Here's my code (that does not get any table names to process):
     Set rs = aDBCn.OpenSchema(adSchemaTables)
   
    bTasksTableFound = False
    If Not rs Is Nothing Then
        If rs.RecordCount > 0 Then
            Do While Not rs.EOF
                If rs("TABLE_NAME") = "Tasks" Then
                    bTasksTableFound = True
                    Exit Do
                End If
                rs.MoveNext
            Loop
        End If
    End If
    If Not bTasksTableFound Then
        Call AddTasksTable(aDBCn)
etc

I also tried this without success (I thnk there's something missing, namely populating anu):

Dim cmd As ADODB.Command
Dim adu As ADOX.Catalog

Dim tabla As ADOX.Table
Set tabla = New ADOX.Table

For Each tabla In adu.Tables
    If Left(tabla.Name, 5) <> "Tasks" Then
        Exit For
    End If
Next

Edward
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
edwardrAuthor Commented:
I have it now.  Thanks for your help.

Here's my code:

    Dim thisCat As ADOX.Catalog
    Set thisCat = New ADOX.Catalog
    Set thisCat.ActiveConnection = aDBCn
   
    Dim tabla As ADOX.Table
    Set tabla = New ADOX.Table
   
    For Each tabla In thisCat.Tables
        If UCase(Left(tabla.Name, Len("Tasks"))) = UCase("Tasks") Then
            bTasksTableFound = True
            Exit For
        End If
    Next

    If Not bTasksTableFound Then
        Call AddTasksTable(aDBCn)
etc

Edwatd
0
 
DhaestCommented:
Stupid that I overlooked the connection ...
Glad it's solved (so I can stop searching why the code was not complete).
0
 
DhaestCommented:
Recommendations: Delete
Asker has found the problem himself
0
 
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now