List all tables in Access to VB

[eves]

Hi all...
I'm having a project where i have to show all the tables that exist in a MS DB into a VB form. I've seen examples of it being done using ASP and vbscripts.

The display format is something like Access where you get to see the different tables. And can i import information from an existing Access DB or Excel into an Access DB using VB. So far i can only link 1 specific table using ADO. And i have no knowledge on other types of links that can be used.I'm a beginner in this esp on database issues. All help would be appreciated... Please advice me on wat i should do for this matter...

Thanks in advanced.
=)

 

[MichaelRed]

to 'list' the tables in an Ms. Access db, you need to look into the MMMSysObjects table in an Ms. Access db. Review the contents of the fields to understand the field content / meaning and you will be able to write a query to return the names of the tables.

As for the advice ... get a third party book (Que | Sybex) and read. Also, extensive use of the HELP facility would improve your capability.

MichaelRed
mred@att.net

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

 

[Hackster]

The real easy way is to, first, open your database.

Then:

Dim td As TableDef

For Each td In YourDbObject.TableDefs
Combo1.Additem td.Name
Next

 

[eves]

Ok..i put the codes at form_load rite..?
and i connected the ado(adodc1) to my db..
the next thing i did was to link the combobox(combo1) to the ado.

do i need to declare anything? like any recordsource for the ado..?

i tried putting recordsource as adcmdtable but they give me an error saying "syntax error in FROM clause", Object required

then they highlighted: For Each td In YourDbObject.TableDefs

I guess i'm pretty lost in this... so where did i go wrong..? =)

 

[eves]

Anyway...i tried this...

Private Sub Form_Load()

Dim db As Database
Dim td As TableDef
Dim dblocation

dblocation = App.Path & "\testdatabase.mdb"

Set db = OpenDatabase("" & dblocation)

For Each td In db.TableDefs
Combo1.AddItem td.Name
Next


End Sub


Then an error msg pop up saying :
- Error in FROM clause

- run-time error '3343':
Unrecognized database format '(Location of my db)'.

 

[eves]

ok... ignore the syntax error...i forgot to remove the ado control...

i've tried with ACCESS 97 db and it works fine but the unrecognized database format comes out when i use ACCESS 2000 ... anything i need to update.?

 

[MichaelRed]

In 2K, the db and recordsets need to be refixed w/ "ADO." Not sure about the tdf. Also - there are NUMEROUS ways to 'Connect" to the dadtabase/table/recordset w/ ADO. There are a "few" examples floating through the MS. Access and VB forums.

MichaelRed
mred@att.net

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

 

[eves]

Hmmm... do u have any example source to guide me to this? =)

 

[MichaelRed]

    Dim dbs As ADODB.Connection
    Dim rst As ADODB.Recordset
    


    strSQL = "Select tblDailyQuo.dtQuo, tblDailyQuo.OpnQuo, tblDailyQuo.Vol, "
    strSQL = strSQL & "tblDailyQuo.AvgVol, tblDailyQuo.AvgPrice, "
    strSQL = strSQL & "tblDailyQuo.DeltVol, tblDailyQuo.DeltPrice, "
    strSQL = strSQL & "tblDailyQuo.VolRatio, tblDailyQuo.PriceRatio, "
    strSQL = strSQL & "tblDailyQuo.LnVolRatio, tblDailyQuo.LnPriceRatio "
    strSQL = strSQL & "From tblDailyQuo "
    strSQL = strSQL & "Where ((tblDailyQuo.Tick = " & Chr(34) & Me.cboSElTicker & Chr(34) & ") "
    strSQL = strSQL & "And "
    strSQL = strSQL & "(tblDailyQuo.dtQuo Between "
    strSQL = strSQL & Chr(35) & Me.txtStartDt & Chr(35) & " "
    strSQL = strSQL & "And "
    strSQL = strSQL & Chr(35) & Me.txtEndDt & Chr(35) & ")) "
    strSQL = strSQL & "Order by tblDailyQuo.dtQuo" & ";"

    Set dbs = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    rst.Open strSQL, dbs, adOpenDynamic, adLockOptimistic

Put any legal query string you want in strSQL, do with the recordset as you will (offer it an apple?)

MichaelRed
mred@att.net

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

 

[eves]

Ok...Thanks lotz guyz...it has been real fun trying out da new stuff...=)
I realised that the DB wasn't recognised cause i was using DAO v2.5

and yeap...i did some filtering on the codes so that they won't show the system tables...=)
hehehe

Thanks! =)