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]

couldnot figure it out. let me know if u got any shot.

 

[ZmrAbdulla]

I think the code you are using is from VB6 that has

ListBox.Clear and LisBox.Sorted=True

Here is a modified version. You still need to set sorting avoid system tables from the list etc

Controls used
1)btnBrowse(command button)
2)btnLoadTable(command button)
3)txtDbName(TextBox)
4)lstTables(ListBox)

Option Compare Database
Option Explicit
Public dbname As Variant



Private Sub btnBrowse_Click()
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    With dlgOpen
        .AllowMultiSelect = False
        .Filters.Add "Access Databases", "*.mdb;*.mde", 1
        .Show
    End With
    dbname = dlgOpen.SelectedItems(1)
    Me.txtDbName.Value = dbname
End Sub



Private Sub btnLoadTables_Click()
    Dim CNN As New ADODB.Connection
    CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbname & ";User Id=admin;Password=;"
    CNN.OpenSchema adSchemaTables
    Dim rs As New ADODB.Recordset
    Set rs = CNN.OpenSchema(adSchemaTables)
    lstTables.RowSource = ""
    Do While Not rs.EOF
        If Not Left$(rs.Fields("TABLE_NAME").Value, 1) = "~" Then
            lstTables.AddItem rs.Fields("TABLE_NAME").Value
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    CNN.Close
    Set CNN = Nothing
    dbname = ""
End Sub

________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[drek01]

gave me Error, "User Defined type not defined"

"dlgOpen As FileDialog"

any idea.
appreciate it.

 

[drek01]

is it may be beacuse i am using access 2000, even when i press F1 key , it says keyword not found.
any alternative.

 

[kjv1611]

No, you need to set the correct reference...

I'll look it up to be sure what it is unless someone else posts it first.

--

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

 

[kjv1611]

Well, at least I don't think it's b/c you're using 2000...

It seems it's a member of the Office Reference. So, make sure you have the most recent "Microsoft Office..." reference set in your VBA.



--

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

 

[drek01]

yes i do, i aleady have refrence, i think its msACtiveX Data object 2.1 library

 

[drek01]

Any Idea KJV1611, i really need this to be fixed. please help me out. i am relaying on you.

 

[kjv1611]

No, "msACtiveX Data object 2.1 library" should not be the required reference for FileDialog.

It's either the "Microsoft Office ##.#"

or another, but it's not activex of any sort...

I know I've used it, so, I'll browse a couple db's here and see what it was...

--

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

 

[kjv1611]

Ok, if you want to narrow it down in the mean time, I know it HAS to be one of these, as I'm using it in a database with these:

[ol]
[li]Visual Basic For Applications[/li]
[li]Microsoft Access 11.0 Object Library[/li]
[li]OLE Automation[/li]
[li]Microsoft DAO 3.6 Object Library[/li]
[li]Microsoft ActiveX Data Objects 2.0 Library[/li]
[li]Microsoft Office 11.0 Object Library[/li]
[li]Microsoft Outlook 11.0 Object Library[/li]
[/ol]

Ok, of that list, I would not think it's # 1, 2, 3, or 7 for sure.

So, that leaves 4, 5, or 6.

Of those, I know it's not number 4, as it's not DAO stuff.

So, it's either [highlight]"Microsoft Office ##.# Object Library"[/highlight] or "ActiveX Data Objects #.# Library"

My guess is that it is the Office one, b/c it was part of the Office Objects group in the VBA Object browse. But if not, then it's the ActiveX one after all.



I hope that helps some.

--

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

 

[drek01]

i don't have option for microsoft office 11.0 object library, i have microsoft office 9.0 object library, even i check the refrence it still gives me same error. and
when i check on microsoft activeX dataObject 2.0 library, it gives me message " Name conflicts with existing module, project or library"
i have 2.1 library selected.
but still does not work.

does this has to do anything with my modules i have called "modFileFunction" and its code is like this:

Option Compare Database
Option Explicit


'[start of code]

'FOR THE 'OPEN' AND 'SAVE' DIALOG BOXES.
Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

Type MSA_OPENFILENAME
' Filter string used for the File Open dialog filters.
' Use MSA_CreateFilterString() to create this.
' Default = All Files, *.*
strFilter As String
' Initial Filter to display.
' Default = 1.
lngFilterIndex As Long
' Initial directory for the dialog to open in.
' Default = Current working directory.
strInitialDir As String
' Initial file name to populate the dialog with.
' Default = "".
strInitialFile As String
strDialogTitle As String
' Default extension to append to file if user didn't specify one.
' Default = System Values (Open File, Save File).
strDefaultExtension As String
' Flags (see constant list) to be used.
' Default = no flags.
lngFlags As Long
' Full path of file picked. On OpenFile, if the user picks a
' nonexistent file, only the text in the "File Name" box is returned.
strFullPathReturned As String
' File name of file picked.
strFileNameReturned As String
' Offset in full path (strFullPathReturned) where the file name
' (strFileNameReturned) begins.
intFileOffset As Integer
' Offset in full path (strFullPathReturned) where the file extension begins.
intFileExtension As Integer
End Type

Const ALLFILES = "All Files"

Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustrFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustrData As Long
lpfnHook As Long
lpTemplateName As Long
End Type

Const OFN_ALLOWMULTISELECT = &H200
Const OFN_CREATEPROMPT = &H2000
Const OFN_EXPLORER = &H80000
Const OFN_FILEMUSTEXIST = &H1000
Const OFN_HIDEREADONLY = &H4
Const OFN_NOCHANGEDIR = &H8
Const OFN_NODEREFERENCELINKS = &H100000
Const OFN_NONETWORKBUTTON = &H20000
Const OFN_NOREADONLYRETURN = &H8000
Const OFN_NOVALIDATE = &H100
Const OFN_OVERWRITEPROMPT = &H2
Const OFN_PATHMUSTEXIST = &H800
Const OFN_READONLY = &H1
Const OFN_SHOWHELP = &H10
'================================================= =========


Function IsLoaded(ByVal strFormname As String) As Integer
' Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormname) <> conObjStateClosed Then
If Forms(strFormname).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function


'procedure MSAOF_tF
Private Sub MSAOF_tF(msaof As MSA_OPENFILENAME, of As OPENFILENAME)
'-----------------------------------------------------------------
' This sub converts from the friendly MSAccess structure to the
' win32 structure.




please let me know.

 

[kjv1611]

when i check on microsoft activeX dataObject 2.0 library, it gives me message " Name conflicts with existing module, project or library"
i have 2.1 library selected.
but still does not work.

I think the problem there is you're trying to instantiate that library twice (different versions of same library). Try just the 2.0, then just the 2.1, and see if it works best with one of those.

--

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

 

[drek01]

Yes i did.
gave me same compiler error: user defined-type not found
"dlgOpen As FileDialog".

 

[kjv1611]

This website is about the references, specifically, so it may be of some help, but I'm not sure.

http://allenbrowne.com/ser-38.html

One quote that does seem like it could be saying this is not available in 2000 is this part:

Include other libraries when you have a reason to do so. For example, referencing the Microsoft Office 10.0 Object Library in Access 2002 gives you the FileDialog object so you can show the user a File Open dialog without resorting to API calls. (Note: FileDialog does not work in MDEs or the runtime version, and the msoFileDialogSaveAs option doesn't work at all.)

You might want to do a little checking at

http://www.msdn.com

as to the Microsoft Office 9 Object library to be sure whether it includes the FileDialog or not.

Another way you should be able to check is in your Object Browser in the VBA Window.

---

Anybody else got any ideas?

--

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

 

[drek01]

my this code does browse up to database folder,
Dim msaof As MSA_OPENFILENAME
Dim sFileName As String
txtDbName.Value = ""
msaof.strFilter = MSA_CreateFilterString("Access files (*.mdb,*.mde)", "*.mdb;*.mde")
sFileName = MSA_GetOpenFileName(msaof)
If Trim(Len(sFileName)) <> 0 Then
txtDbName.Value = msaof.strFullPathReturned
sFileName = ""
End If




but i couldnot get this synchronize with your's :


dbname = dlgOpen.SelectedItems(1)
Me.txtDbName.Value = dbname

 

[drek01]

Does your code works for different version of Access?

 

[drek01]

Anyone Else, any help 'll be appreciated.

Derek

 

[ZmrAbdulla]

My sample code has reference to Microsoft Office xx.x library.
I am using Access2002(Office 10). (You might need set reference to Office 9)


________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[AccessGuruCarl]

It appears everything is working except the browse for database...


Take a look at this code:

http://www.mvps.org/access/api/api0001.htm

You may need to delete the module "modFileFunction" as it appears to have similiar code....

But if you replace this with ZmrAbdulla browse code it should work fine!

Good Luck


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

 

[MajP]

I slightly modified Zameer's code to give you two columns showing if it is a query or a table. And then used the API browser that AccessGuru references. This worked fine, and will help avoid the reference issues

Private Sub cmdBrowse_Click()
    Dim strFilter As String
    Dim lngFlags As Long
    Dim dbName As String
    strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", "*.MDA;*.MDB")
    dbName = ahtCommonFileOpenSave(InitialDir:="C:\", _
        filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, DialogTitle:="Select Data Base")
    If Not dbName = "" Then
      Call loadTables(dbName)
    End If
End Sub

Public Sub loadTables(dbName As String)
    Dim CNN As New ADODB.Connection
    CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName & ";User Id=admin;Password=;"
    CNN.OpenSchema adSchemaTables
    Dim rs As New ADODB.Recordset
    Set rs = CNN.OpenSchema(adSchemaTables)
    Me.listTables.ColumnCount = 2
    Me.listTables.RowSourceType = "Value List"
    Me.listTables.RowSource = ""
    Do While Not rs.EOF
        If rs.Fields("TABLE_TYPE") = "TABLE" Then
            listTables.AddItem rs.Fields("TABLE_NAME").Value & "; Table"
        End If
        If rs.Fields("TABLE_TYPE").Value = "VIEW" Then
            Me.listTables.AddItem rs.Fields("TABLE_NAME").Value & "; Query"
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    CNN.Close
    Set CNN = Nothing
    dbName = ""
End Sub