Database Conectivity

[ashermj]

This is a very simple question yet I don't seem to be able to work it out. I have conected to a database using an adodb conection. All I want to do is to be able to display the table names in that database on a form.

[parksie]

What sort of database are you using?

[ashermj]

Access 2000 using Jet 4.0

[parksie]

Not sure if this is totally legal, but it works on Access 2000

Dim x As New Recordset
x.Open "SELECT [Name] FROM MSysObjects WHERE [Type]=4;", CurrentProject.Connection, adOpenDynamic, adLockReadOnly
x.MoveFirst
While Not x.EOF
    Debug.Print x!Name
    x.MoveNext
Wend
x.Close

[ashermj]

Thanks for that, have just tried it but I'm getting an error.

Record(s) can't be read; no permission on 'MSysObjects'

[parksie]

Bugger

The reason that worked for me was probably because I did that inside Access, due to the fact that I haven't managed to get round to reinstalling VB yet What user are you logging on as?

[ashermj]

I'm logged on as Admin

It's a real pain you would have thought there would be some straight forward way of seeing the table names.

[parksie]

I couldn't find anything in the documentation about it *grr* *grr*

Most RDBMSs have a SQL statement that returns a recordset with the table names in, equivalent to MySQL's "SHOW TABLES;" statement.

I expect there *is* a method...they're just keeping quiet about it Maybe a look on the MS Knowledge Base might be productive.

[chrisjk]

Try this:

Add a listbox called "lstTables" to a form. Add a reference to Microsoft DAO 3.6 Object Library (or whichever version you have the latest of) and paste this code into the Form_Load event.

Dim dbTest As Database
Set dbTest = OpenDatabase("MyDB.mdb", False, False, ";pwd=" & "MyPassword")
Dim tblObj As DAO.TableDef
Dim intI As Integer

lstTables.Clear
For Each tblObj In dbTest.TableDefs
    If UCase(Left(tblObj.Name, 4)) <> "MSYS" Then
        lstTables.AddItem tblObj.Name
    End If
Next

dbTest.Close

[parksie]

Oh well...I was trying to avoid DAO but it seems there's no way round it It's Access 2000 so it needs to be 3.6 or later.

[chrisjk]

Originally posted by parksie
Oh well...I was trying to avoid DAO but it seems there's no way round it.

It works, I use it. Nuff said.

It's Access 2000 so it needs to be 3.6 or later.

He might have the pre-release 3.7 (you never know, could happen).

[ashermj]

I've just tried that and it works fine.

Thanks very much for everybodies help.

[chrisjk]

Good ol' DAO wins round one

[abhijit]

HI there,
I don't subscribe to the view abotu using DAO. You could use ADOX just as effectively.

Cheers!
Abhijit