How To Browse DataBase table.

[drek01]

hey guys i want to have a table browser, i know how to implement file browser but i want table browser of database( access Database).
i know this will browse database file but won't open tables( or objects) of that database. so any body please help me out.

Private Sub cmdImport_Click()
Dim invalid As Integer
On Error GoTo Err_cmdImport_Click

Dim msaof As MSA_OPENFILENAME
Dim strTable As String

msaof.strFilter = MSA_CreateFilterString("Access files (*.mdb,*.mde)", "*.mdb;*.mde")
strTable = MSA_GetOpenFileName(msaof)

 

[drek01]

when i paste your code and made required changes and also use the APi browser as AccessGuru refrenced, ( i tried without deleteing modfile module and without deleting modfile module), when ever i click on browse button or close button it gives me error saying
" The expression on click you entered as event property setting produced the following error: Member already exists in an object modules from which this object module derives."

Any suggestions.

 

[drek01]

is there any modification need to be done in form ( command button browse,cmdloadtables, txtbox, and listbox) beside changing there names.

 

[drek01]

i think i must have missed the way to organize the code, or the way to put text box, list box and borwser.

any suggestion. how should the command button and boxes( list and text box)form has to be implemented

 

[MajP]

I just made a form with a cmd button "cmdBrowse" and a list box "listTables" nothing else. Then I put the above code into the form's module. I dropped the API code into a public module.

 

[AccessGuruCarl]

Hello drek01

" The expression on click you entered as event property setting produced the following error: Member already exists in an object modules from which this object module derives."

You are getting this error because you have duplicate names somewhere.....

I'm quessing in another module... However it could be a duplicate object on a form... Double check your names for all textboxes, listboxes, ect... If you don't find it there, then start looking at your modules...

Or

Create a new db...
Paste API code from mvps.org site into a new module!
Create a new form, add a command button and a listbox...

Name the button: cmdBrowse
Name the listbox: listTables

Next, paste MajP code from last post...
Save the form, open it and click the button.
Browse for mdb file, and select it. The list box will populate with all the tables and queries!

Tested... and works fine for me!

Hope this helps...

Good Luck



AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[drek01]

thank you Guru,
it was able to browse table but after that it gave me compiler error saying method or data member not found.
".AddItem"
i am looking it on help menu. haven't found close one yet.
let me know

 

[AccessGuruCarl]

Hello drek01,

Did you create a new db?

What version of Access are you using?

Did the listbox display any tables or queries?

Try setting a break point in the loadTables sub and running the code. Step through it, and see were it fails.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[drek01]

i am using access2000, listbox doesnot dislay any tables or query. actually after i browse for database and select it, it gives me same copiler error," Method or data memeber not found" : .ADDitem

it failed on adding tables, on list box i guess, thats what seems like.
let me know, if you have any idea.
thanks

 

[MajP]

you probably misnamed the listbox. For example if your listbox is named "lstTable" but in code you say
listTable.additem

you will get a "..data member not found" error because is "listTable" does not exist only "lstTable"

if you misspell a method you get the same error:
listTable.additm
"method or data member not found" because there is no such method as "additm" only "additem".

Intellisense makes this an easy check. If I type:
me.listTable.
then all of the methods should appear. If they do not appear then it is not an object on the form.

 

[drek01]

thanks MajP, i did check all the typos, everything is okay, and you were right, i thought that i mentioned it earlier in my previous post, that additem method doesnot apprear when i type me.listtables, so i guess it is not an object on the form.

AnyHelp Guys.
Thanks
Derek

 

[drek01]

BTW, ia m using VBA, the code is VB, isn't it.
if that's a problem.

 

[AccessGuruCarl]

Hello drek01

Everything should work!!!

I've built a demo you can download and try.

My IP changes so download quick...

http://74.67.163.88/beta/BrowseTables.mdb

Hope this helps....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[drek01]

it gave me the same error, "method or datamember not found"
'.ADDITEM'

i looked online, looks like access2000(VBA) doesnot have .additem method, so tryig to find alternative way.

i am using access 2000, and all the user has access2000 as well.

so let me know if you come up with any alternatives. this is killing me man. THanks a lot Guru though.

 

[kjv1611]

drek01, if I were in your boat, I would next try just going into VBA, and re-typing the line:


listTables.AddItem rs.Fields("TABLE_NAME").Value & "; Table"


(not copy, but retype).

Then, when you get to the end of listTables, and you put the period there, look through the pop-up help list of all the possibilities. See if there is one there (with a green little icon by it - for methods) that looks like it might be something similar. I would guess that it is possible, they just changed the name - not that that's 100% the case, but I think it's a possibility.

If that doesn't work, then you may can do it with setting one of the properties equal to the new value... Seems I vaguely remember something to that extent, but I cannot remember the exact property/context of the statement at the moment.

Another option would be to do a Google search of something like:

"Excel 2000 VBA add item to list" or change "list" to "listbox" - you might get something that way.

If that doesn't work, then you might could instead create a local table within the database that looks at the other databases, and use that table as the source for the list box, and use the VBA code to add the items to that table, as apposed to the list box (and requerying the list box once the items have been added to the table).

--

"If to err is human, then I must be some kind of human!" -Me

 

[drek01]

i tried to do as you said, i didnot find any close method.
i guess as you said creating temporary table and then adding tables to the temp table, might me good idea. i am trying to do that.
help me out, i am very new to VB and VBA.

so lets see.
thanks KVJ

 

[kjv1611]

drek01...

Well, my thought was to just create a static table OR a temporary table. If this database is used for only this purpose, I don't see the harm in just creating a standard table (not hidden). Then you don't have to worry about creating the table in code. Just set it up to store the data exactly the way you want. Then in code, you can reference that table as a recordset, or via SQL statements to add/change/remove other Access Db details as you would the listbox items with the previous method.

--

"If to err is human, then I must be some kind of human!" -Me

 

[drek01]

Since its huge database,and this is used for so many other purpose. i guess we just have to write code for temp table. help me out with that plz.

 

[kjv1611]

I know there are MANY examples of this all over the web, and even here at tek-tips. So rather than my redoing some code to show you, I'll at least post this one link that I found. It looks pretty helpful. Someone else may be able to point to a better method, or either better way of carrying out this method.

Here is the link I found, and it looks pretty easy to follow:

http://www.fontstuff.com/vba/vbatut02.htm

Maybe you can take a glance at it, and create a "temp" table based off of the code on this page, and see how that works. As a matter of fact, I think this guy may be using a recordset with the temp table, but I did just glance at it.

Let us know if this works for you, and if you run into any problems while doing this.

--

"If to err is human, then I must be some kind of human!" -Me

 

[drek01]

It seems like it selects the field from table, while i needed was to select the database and display the tables.

and it looks like it loads the data into temp table but how does it displays in my list box.

ANyone got any ideas.

 

[kjv1611]

You set your list box's recordset to the table.

--

"If to err is human, then I must be some kind of human!" -Me