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)

 

[kjv1611]

What about using a form with record locks set to where the user cannot change the data? Or just have a SELECT query (standard query) for which to build your form on, and that query be of all the data in the table? That way, the user could not update any of the fields, but could view them all.

--

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

 

[JoeAtWork]

Well you could use DAO to get a list of all tables (TableDefs) or queries (QueryDefs). However, that only gives you the list of objects, you would need to build the "browser" yourself, perhaps using a treeview or listview.

 

[drek01]

please give me some more information. here is what i have for the browser, which only browse upto database not objects(tables).
please give me more information, and some code of possible.
Private Sub cmdShowFiles_Click()


Dim msaof As MSA_OPENFILENAME
Dim sFileName As String
On Error GoTo Err_CmdShowFiles_Click

txtSource.Value = ""
msaof.strFilter = MSA_CreateFilterString("Access files (*.mdb,*.mde)", "*.mdb;*.mde")
sFileName = MSA_GetOpenFileName(msaof)
If Trim(Len(sFileName)) <> 0 Then
txtSource.Value = msaof.strFullPathReturned
sFileName = ""
End If

Exit_CmdShowFiles_Click:
Exit Sub
Err_CmdShowFiles_Click:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_CmdShowFiles_Click


End Sub

 

[kjv1611]

Do a help search on the TableDefs and QueryDefs that JoeAtWork is talking about. I would think you can get what you're looking for from the help file with that.

--

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

 

[JoeAtWork]

drek01 - there isn't any API or Access method that I know of that browses through tables. So you would have to create your own by making a form and putting controls on it that you can use for navigation (perhaps a listview or even a simple listbox that shows all the table names).

I don't have any code because I've never needed to do this, but if you search for TableDef or QueryDef you should find many, many code examples. Everything else is just standard coding.

 

[drek01]

Thanks Joe for some heads up. I have code here and also try to implement listbox(cmbtable) to get the tables. i know there are flaws in code itself, and some error like, (cmbtable.clear). help me to improvise the code and fix my problem. PLEASE.

HERE IS MY CODE.


Private Sub cmdShowFiles_Click()


Dim cnn As ADODB.Connection 'Don't instantiate in the declaration or ...
Dim rs As ADODB.Recordset '... you won't be able to reclaim the allocated memory without rebooting
Dim adocmd As ADODB.Command
Dim invalid As Integer
Dim sDatabase As String



Dim msaof As MSA_OPENFILENAME
Dim sFileName As String
On Error GoTo Err_CmdShowFiles_Click

txtSource.Value = ""
msaof.strFilter = MSA_CreateFilterString("Access files (*.mdb,*.mde)", "*.mdb;*.mde")
sFileName = MSA_GetOpenFileName(msaof)
If Trim(Len(sFileName)) <> 0 Then
txtSource.Value = msaof.strFullPathReturned
sFileName = ""
End If

Exit_CmdShowFiles_Click:
Exit Sub
Err_CmdShowFiles_Click:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_CmdShowFiles_Click


End Sub


Private Sub Command5_Click()
' command button to browse the same database and ("possibly" 'which i am trying to do) list tables in box.

' code to open the CommonDialog box and let user choose a .mdb file

Dim msaof As MSA_OPENFILENAME
Dim sFileName As String

txtSource.Value = ""
msaof.strFilter = MSA_CreateFilterString("Access files (*.mdb,*.mde)", "*.mdb;*.mde")
sFileName = MSA_GetOpenFileName(msaof)
If Trim(Len(sFileName)) <> 0 Then
txtSource.Value = msaof.strFullPathReturned
sFileName = ""
End If


'put the file name into sDatabase

Set cnn = New ADODB.Connection 'this is where you instantiate
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= txtsource.value ;Persist Security Info=False" 'substitute your connection string
cnn.OpenSchema adSchemaTables
Set rs = New ADODB.Recordset
Set rs = cnn.OpenSchema(adSchemaTables)

cmbtable.Clear 'clear the combobox out
cmbtable.Sorted = True 'alpha sort the table names to make it easier for your user
Do While Not rs.EOF
If Not Left$(rs.Fields("TABLE_NAME").Value, 1) = "~" Then
cmbtable.AddItem rs.Fields("TABLE_NAME").Value
End If
rs.MoveNext
Loop
rs.close
Set rs = Nothing 'reclaim the memory here
cnn.close
Set cnn = Nothing

End Sub

 

[kjv1611]

Are you getting any certain errors, or is it just not "working?" Do you have any idea yet, yourself, as to where the error(s) is/are in the code?

--

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

 

[drek01]

looks like, when i compile it, it says 'method or datamemeber not found';"cmbtable.clear" i try to change it to ".clearitem"

cmbtable.Clear 'clear the combobox out
cmbtable.Sorted = True

and when i try to exclude those line, it doesnot give me anything, it browse the databse file, just file and it says authentication failed.

May be i didnot implement the cmbtable( combobox) in wrong way. since
"Private Sub Command5_Click()" browses files only but what i was trying to do that, was to open tables for that selected mdb files and list the tables in the cmbtable."
am i doing this the wrong way. what the row souce of cmbtable has to be.

help me out here.

 

[kjv1611]

Ok. I think that first, if we can get this section fixed:

looks like, when i compile it, it says 'method or datamemeber not found';"cmbtable.clear" i try to change it to ".clearitem"

cmbtable.Clear 'clear the combobox out
cmbtable.Sorted = True

You'll be more on your way to reaching your goal - I'm not necessarily saying it'll all be "hunky dory" just yet.

Method or Data Member not found can be that you are calling the object by an incorrect name, or that the particular action/property for that item you are trying to use is not accessible or just does not exist.

Are you sure that "cmbtable" is exactly the correct control name? Are you sure it isn't "Combo1" or "cmbTable"? If you find it's a typo, then that part is an easy fix. Also, be sure you really did give that name to the control. To verify, right-click on your combo box, and choose properties, then look at the "Other" tab. The first field should be "Name" - make sure that is exactly what you think it is.

--

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

 

[drek01]

the name of combobox is cmbtable, does there have to be something on rowsource. and one more thing if i am not confusing you, i have command button to list the tables on it(supposedly).

Private Sub Command5_Click()

am i doing it right?

 

[kjv1611]

Ok, then it's probably not a typo of the Combo Box name, but may rather be that what you are trying to do is just not possible, at least in that context.

To see if that is the case, instead of:

cmbtable.Clear   'clear the combobox out

Try:

cmbtable = vbNullString

That should clear it out, anyway.

--

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

 

[kjv1611]

And I'm not even sure why you should need this line:

cmbtable.Sorted = True

Do you not have that sorted until this point in the code? Or, are you wanting it to add the entered value and then resort the underlying table/recorsource?

If the underlying table/recorsource is laready sorted, then I believe all you have to use is:

cmbtable.Requery

--

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

 

[kjv1611]

Try those fixes and post back.

--

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

 

[drek01]

it gave me error saying "authentication failed," after i click on command button and select the mdb file.

 

[kjv1611]

Ok, at least we got past the first errors, then.

I'm not sure on the authentication error right now. Hopefully someone else can get that part for ya. If not, I'll take a shot at it after a half hour, or either tomorrow.

--

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

 

[drek01]

as i mentioned earlier does it has to do anything with rowsource of combobox.

 

[drek01]

could you please let me know asap. i will be in serious problem.

 

[kjv1611]

What then do you have set to the row source of your combo box? It should be a table or a query, or possibly a recordset of some sort.

--

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

 

[drek01]

there is nothing on rowsource, on rowsource type its table.