Database Conectivity

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.

What sort of database are you using?

Access 2000 using Jet 4.0

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

Code:

---

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

---

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

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

Bugger :rolleyes:

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?

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.
:(

I couldn't find anything in the documentation about it *grr* :mad: *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.

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.

Code:

---

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

---

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.

Quote:

---

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.

Quote:

---

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).

I've just tried that and it works fine.

Thanks very much for everybodies help.

Good ol' DAO wins round one;)

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

Cheers!
Abhijit