Retrieve data from Acces2000 Systemtables

[oj]

Hi,
I have a VB6 application which uses an Access2000 database.
I have no problem creating recordset based on "ordinary" tables and linked tables in the database, however when i try to create an ADO recordset based on the systemtable MSysObjects the recordset object is always empty/Nothing.
Example:
Set rs = Db.OpenRecordset("SELECT * from MSysObjects&quot

Is there a trick I can use to read Access systemtables from VB6 ?

Thanks in advance !

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmm, Interrrrrrrresssssttting, Verrrrrry Inttttttttteeeeerrrrrrsssting.

Have not (yet ) run into this. I generally have Ms. Access set to show hidden objects, and do not usually attempt to access the MSys* tables from VB, so it is probably just in the crack between the "hidden" and VB vs VBA. NOt a real Fix, but have you tried to make a query in Ms. Access to show the table and then just use ADO to look at the query?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[sunaj]

Hi,

Just a comment. I had this little program that lists the tables and fields in 2 comboboxes. Most of the MSys tables does not seem to have any fields, but the ones that have can be accessed by a normal SELECT....

Sunaj
---------------------------------------------------------
'This code demonstrates how to get the table
'and field names from an Access database using ADO2.5
'It also shows how to use comboboxes to view the table and field names.

'Make a reference to
'Microsoft ActiveX Data Objects 2.5 Library (msado15.dll) and
'Microsoft ADO Ext. 2.5 for dll and security (MSADOX.DLL)

'Create a form with 2 comboboxes (combo1 and combo2)
'Change the name of the database in the StrCon to
'the name of your database. Copy this code into the form.

Option Explicit
Dim con As ADODB.Connection
Dim Cat As ADOX.Catalog


Private Sub Form_Load()
Dim StrCon As String
Dim i As Integer

'Connection string, remember to change the name of the databse.
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"
'Open connection
Set con = New ADODB.Connection
con.Open StrCon
'Set the catalogs connection
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = StrCon
'Loop through the tables and add them to combo1
For i = 0 To Cat.Tables.Count - 1
'If Left(Cat.Tables(i).Name, 4) <> &quot;MSys&quot; Then _
Combo1.AddItem Cat.Tables(i).Name
Combo1.AddItem Cat.Tables(i).Name
Next i
Combo1 = Combo1.List(0)
combo1_click
End Sub

Private Sub combo1_click()
Dim i As Integer
'When user selects a new table name in combo1,
'show the field names in combo2.
Combo2.Clear
For i = 0 To Cat.Tables(Combo1.Text).Columns.Count - 1
Combo2.AddItem Cat.Tables(Combo1.Text).Columns(i)
Next i
Combo2 = Combo2.List(0)
End Sub

Private Sub Form_Unload(Cancel As Integer)
'close connection
con.Close
End Sub
----------------------------------------------------------

 

[woyler]

You said you trying to open an ADO recorset but you seem to be using DAO syntax. CAn you post your declarations?

 

[oj]

Hi,
yes you're quite right, I've written ADO instead of DAO in my question..
the declarations are :

Dim Db As Database
Dim rs As DAO.Recordset