Looping through Unknown Tables.

[honki]

Good Evening everyone.

I'm really not sure if this is a stupid question or not, but I have searched and searched, but cannot find any "Lamens" ways of accomplishing this task.

I have been ordered by the powers to be, again, to give up my valuable private time, and again, learn how to and carry out the creation of a piece of software that will link to a database, list the tables in that particular database, and assign various information to various variables, to create degrees of confusion. *Whew*

***ken boss. Sorry, just had to get it off my chest.

Anyway, now thats out of the way, this is what I'm trying ever so hard to acheive.

I am using ActiveX Dataobjects 2.5

I have a textbox, inputed with a location and database file name, creatind the connection string.

I click a button, it connects to the desired database.
Below this textbox, I would like to list all the TABLES within that database, creating the second step.

I'm just not sure how to retrieve the "unknown" table identities.

These will be required for the third step to create a recordset, for each of those tables, so the user can move and select data at his/her whim.

Ignore the "StreamReader" Related stuff, and assume this is coded in VB6.

Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim SR As StreamReader
        SR = File.OpenText("C:\Documents and Settings\Owner\My Documents\Visual Studio Projects\DataImport\DataImport\ProfileList.txt")
        Dim x As String
        Dim y() As String
        Dim i As Integer = 0
 
        While SR.Peek <> -1
            x = SR.ReadLine()
            y = Split(x, ",")
 
            ReDim Preserve DataProf(i)
            DataProf(i).ID = y(0)
            DataProf(i).CString = y(1)
            DataProf(i).Table = y(2)
            i = i + 1
        End While
        SR.Close()
 
        Dim CSt As String
        For i = 0 To UBound(DataProf)
            CSt = DataProf(i).CString
            CS = New ADODB.Connection
            CS.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= " & CSt
            CS.Open()
 
'---------Here is where the code will go to populate the TBL() array with the table ID's.
            Dim TBL() As String
 
            RS = New ADODB.Recordset
            'RS.Open("TBL() ARRAY GOES HERE!", CS, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockPessimistic, ADODB.CommandTypeEnum.adCmdTable)
            If RS.RecordCount > 0 Then RS.MoveFirst()
 
 
 
            CS.Close()
            CS = Nothing
        Next
 
 
 
    End Sub

Does anybody know a relatively simple way of retrieving the table names within the database???

Thankyou so much in advance.

-Honki.

[sevenhalo]

Your "***ken boss" is right (sorry, no love this time). You're using .Net and still using legacy ADO. Using someof your own private time to learn .Net as a .Net programmer isn't asking much.

Check the forums for ADO.Net. A few people have links in their signature with tutorials (Techgnome and jmchinnley come to mind).

[honki]

I find using legacy ADO more robust and controllable. Generally bulletproof if all errors are handled correctly.

I have been looking into ADO.net, but the boss has asked for the code to be "oldschool" so we could possibly utilise it later in a VBA environment.

Just assume the DB lines of code, are coded in VB6.

[mendhak]

All errors are handled only as long as the programmer handles them. .NET has a much better exception management system which allows the application to work gracefully. It even has transaction objects. I fell in love the first time I saw that.

[honki]

Sure, I can appreciate that, I have played with ADO.net....
But I still am required (to avoid double handling) code this in a compatible language to use it in other and much older applications.

Does anybody actually know how to solve my problem?

[Dave Sell]

SELECT sysobjects.name [Table Name]
FROM sysobjects
WHERE sysobjects.name LIKE '%ias_%'

SELECT syscolumns.name [Field Name], sysobjects.name [Table Name]
FROM syscolumns, sysobjects
WHERE syscolumns.id = sysobjects.id
AND syscolumns.name LIKE '%operationid%'

[honki]

Thanks Dave.
I really don't understand the code. I'm not so familiar with "SELECT" statements.
Could you explain it?

[Dave Sell]

sysobjects is a table in each database that contains all the table names.

syscolumns is a table in each database that contains all the field names.

Using these 2 tables, as in my sample can reconstruct all the tables in the database.

[honki]

Thanks.

These databases have no such tables and I didn't write these databases.
They are databases provided from other applications, and the software I am writing is to be able to access information directly from them (I have permission BTW).

For example, you have sent me a database you created, with tables you created.
I can run my program, and I can list all the Table names within your database.

Is this possible?

Just like in Excel where you can import external data.
You first select the Database,
Then select the Table,
Then select the fields you wish to import.

[Dave Sell]

It's possible to list the tables as I have shown, along with their fields. Importing data ad-hoc will not be so straightforward. It might be possible for some applications, but not possible for others, depending if their tables have foreign-key constraints, for example.

[szlamany]

You are comfortable with LEGACY ADO but do not know SELECT statements?

That seems like a contradiction...

[FunkyDexter]

What DBMS are you connecting to? The sysobjects and syscolumns tables Dave mentioned are from SQLServer and WILL be there if that's what you're using. If you're using a different DBMS then Dave's suggestion probably won't work but something similar will - you just need to find out whether there's a system table that contains the table names.

You'll be able to get the column names by issuing a blank select (ie with a WHERE 1 = 0 clause) and iterating the fields collection.


And as has been mentioned by others, if you want to do this you're REALLY going to have to learn how to do a SQL select. I personally find W3Schools provide the best tutorials on this kind of thing but you could also check the tutorials section of the forum, there's bound to be some good articles in there.

[Dave Sell]

MsAccess has similar tables, but you might have to alter the IDE to specifically view system tables. By default Access has them invisible I think.