?
Solved

get the names of the tables of an Access database

Posted on 2005-03-26
19
Medium Priority
?
275 Views
Last Modified: 2010-04-05
How can I get the names of the tables of an Access database
and have them display in TListView (or memo).Then on onchange event of the ListView
open the corresponding (selected) table in the grid (next to listview) and at the same time
have a label display the name of the table which was opened???
My database lies in my app.exe folder.
Just for the record, the database is allready opened (on main form create).
(ADOConnection1).This form in which  I am trying to do above is not the mainform.
I hope I do not have to disconnect...
0
Comment
Question by:senad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +1
19 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13637955
If you are using an ADO connection to connect to your database, pass a stringlist to the function below to grab all the table names
ADOConnection.GetTableNames(MyTables, False)  // wont get system tables if you set to false

Adding them to a listview

var
  lsti:TListItem
  iLoop: Integer;
begin
  // loop through the stringlist
  for iloop:= 0 to MyTables.Count - 1 do
  begin
    //add a new listitem to the listview
    lsti:= ListView.Items.Add
    // set the caption to the table name
    lsti.caption := MyTables[iLoop];
  end;
0
 
LVL 15

Assisted Solution

by:mikelittlewood
mikelittlewood earned 400 total points
ID: 13637961
Assuming you have your grid, dataSource and dataSet correctly set up and linked.
Set your listview to be row select,

Write the following code in the OnChange event of the listview
Presuming you are using an ADOQuery liked to your datasource

if ADOQuery.Active then
  Close;
ADOQuery.SQL.Text := 'SELECT * FROM ' + Trim(ListView.Selected.Caption);
Label1.Caption := Trim(ListView.Selected.Caption);
Label1.Invalidate;
Open;
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13637970
Also a good idea to put the code

if ListView.Selected = nil then exit;

before everything, just to make sure something is selected
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13638017
<I hope I do not have to disconnect>

No, just keep closing the ADOQuery, change the SQL, and reopen the query to change the data in the grid
0
 
LVL 12

Accepted Solution

by:
esoftbg earned 1200 total points
ID: 13638473
var
  FN:     string;
  SLTab:  TStringList;
  ConnStr: string;
begin
  FN := ExtractFilePath(Application.ExeName) + 'MTV.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  ADOConnection.Close;
  ADOConnection.ConnectionString := ConnStr;
  ADOConnection.Open;
  SLTab := TStringList.Create;
  try
    AdoConnection.GetTableNames(SLTab);
    Memo.Lines.Assign(SLTab);
  finally
    SLTab.Free;
  end;
end;
0
 
LVL 22

Author Comment

by:senad
ID: 13639581
Actually,I was looking for this :

procedure TForm11.FormCreate(Sender: TObject);
var
  tables: TStringList;
  i: integer;
  FN:     string;
  ConnStr: string;

begin
  FN := ExtractFilePath(Application.ExeName) + 'Mydatabase.MDB';
  ConnStr := 'Data Source=' + FN + ';Provider=Microsoft.Jet.OLEDB.4.0';
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := ConnStr;
  ADOConnection1.Open;
  tables := TStringList.Create;
  try
  AdoConnection1.GetTableNames(Tables);
      for i:=0 to tables.Count-1 do
      ListView1.Items.Add.Caption := tables[i];
  finally
    tables.free;
  end;
end;



procedure TForm11.ListView1Change(Sender: TObject; Item: TListItem;
  Change: TItemChange);
begin
{$IFNDEF D5H}
  if (AnsiCompareText(Item.Caption, 'Log') <> 0) then
    begin
{$ENDIF}
      Label1.Caption := Item.Caption;
      ADOTable1.Close;
      ADOTable1.TableName := Item.Caption;
      ADOTable1.Open;
{$IFNDEF D5H}
    end;
{$ENDIF}
end;


But if I may, I have a few questions regarding opening of the tables this way.
Clicking in listview opens the table in the grid,that is ok.
But how do I apply some functionality ?
I have a time field in one of the tables and when it opens it shows
30.12.1899 17:44:42
(It should show only time)
Since in ADOTable1 I can not have any fields I can not set the right format.
Or the field width,name etc...
Is there a way to correct this??
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13639728
procedure TForm1.ADOQueryAfterScroll(DataSet: TDataSet);
var
  HH:            Integer;
  MM:            Integer;
  SS:            Integer;
begin
  HH := HourOf(ADOQuery.FieldByName('CURR_MOMENT').AsDateTime);
  MM := MinuteOf(ADOQuery.FieldByName('CURR_MOMENT').AsDateTime);
  SS := SecondOf(ADOQuery.FieldByName('CURR_MOMENT').AsDateTime);
  Edit_CurrentMoment.Text := IntToStr(HH) + ':' + IntToStr(MM) + ':' + IntToStr(SS);
end;
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13639743
uses
  ...., DateUtils;
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13640214
You change the viewing of the database grid at runtime to alter names and columns

DBGrid1.Columns[x].Width := 200;
DBGrid1.Columns[x].Title := 'Column 1';
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13640226
Sorry that should read DBGrid1.Columns[x].Title.Caption := 'Column 1';

I thought you said you already had your connection to the database open, which is why I didnt write the rest of the code to connect along with the code to get the table names.
0
 
LVL 22

Author Comment

by:senad
ID: 13640479
eso , if I write that then when I open the table that has no such field I get the error...
and what is " Edit_CurrentMoment.Text "??? :-)
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13640576
Edit_CurrentMoment: TEdit;

I made a simple example about your question and do visualization of the Time-Filed into a TEdit component to avoid:  30.12.1899 17:44:42

the above method     ADOQueryAfterScroll()
sets the correct visualization into Edit_CurrentMoment as:
17:44:42

What could be the reason that in your table  sometimes to have that Field, but sometimes to have not it ?
I don't understan this problem ....
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13642086
Oh, I understand .... I am testing only a table, but you have more tables with different set of fields ....
0
 
LVL 22

Author Comment

by:senad
ID: 13642207
Yes,that is the problem...
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13650402
I haven't tested this but the simplest way would be to run through all the fields on the table and then set the format e.g.

var
      li: integer;
begin
      for li:=0 to pred(adotable1.fields.count) do
  begin
        if adotable1.Fields[li].DataType=ftDateTime then
          TDateTimeField(adotable1.Fields[li]).DisplayFormat:='hh:mm:ss';
  end;
end;

0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13659368
But there is a hard problem - not every field of type ftDateTime contains Time, some of them could contain Date and Time ....
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13659633
Ok then just compare the value to >1 i.e.

var
     li: integer;
begin
     for li:=0 to pred(adotable1.fields.count) do
  begin
       if adotable1.Fields[li].DataType=ftDateTime then
       begin
         if adotable1.Fields[li].AsDateTime>1 then
           TDateTimeField(adotable1.Fields[li]).DisplayFormat:='dd/mm/yyyy hh:mm:ss'
          else
           TDateTimeField(adotable1.Fields[li]).DisplayFormat:='hh:mm:ss';
       end;  
  end;
end;

any time only value should have a value of less than 1 where a datetime should be greater than 1

Hope this helps
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13659784
It is not so easy as you explain: it is possible to be a field about real Dates before 1900 and does not represent only a Time, for example - Bulgaria is liberated at 03.03.1878, but in your example it will be represent as Time ....
So there is not information that correctly distinguish fields what they really contain Date & Time, Date only or Time only ....
0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 400 total points
ID: 13659918
Ok try modifying the above code to

        if (adotable1.Fields[li].AsDateTime>=1) or (adotable1.Fields[li].AsDateTime<0)  then
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question