By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

How Open Database to search for a table using vba to display its data properties.

P: 3
Hi Everyone,
I am working on a piece of code, that displays the properties(Name, Datatype, size & Description) of the table in the database.
Now I want to further Enhance the code.
I Have created a form in MS Access, on that form, I have 2 buttons & a text box.
One button is to select a mdb file, whom properties i want to display. Second button then stores that properties in a Excel file.
when I open the file using first button, the path of the file gets stored in the TextBox. Now what i want is that second button that generates the properties to take that path & open that database. But I couldn't able to link that path in the text box to open that database connection.

The code for both buttons is like this:
*********************************
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Command0_Click()
  4. Dim mesPath, mestab, messec As String
  5.  
  6. messec = ""
  7. Call CreateSchemaFile("\\ohsstor2\home\sluthra\My Documents\schemas\")
  8. End Sub
  9.  
  10. Private Sub Command6_Click()
  11. Dim AccessPath As String
  12. AccessPath = BrowseForfile("C:\", "MS Access Files (.mdb)|*.mdb")
  13. Text4.Value = AccessPath
  14.     If AccessPath = "" Then: MsgBox "No path provided - stopping": Exit Sub
  15. End Sub
  16.  
  17. Private Function CreateSchemaFile(sPath As String)
  18.  
  19. Dim Msg As String ' For error handling.
  20. On Error GoTo Err_ShowDescrip
  21.    Dim ws As Workspace, db As DAO.Database
  22.    Dim tblDef As DAO.TableDef, fldDef As DAO.Field
  23.    Dim i As Integer, Handle As Integer
  24.    Dim fldName As String, fldDataInfo As String
  25.    Dim dbpath As String
  26.  
  27.  
  28.  
  29.  
  30.    ' -----------------------------------------------
  31.    ' Set DAO objects.
  32.    ' -----------------------------------------------
  33.     dbpath = "Text4.value"
  34.     szConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & "; "
  35.     SQL1 = "Select * from NADB;"
  36.     Set rst = CreateObject("ADODB.Recordset")
  37.     rst.Open SQL1, szConnStr, 1, 3
  38.     'Set db = CurrentDb()
  39.    ' -----------------------------------------------
  40.    ' Open schema file for append.
  41.    ' -----------------------------------------------
  42.  
  43.     sPath = "\\ohsstor2\home\sluthra\My Documents\schemas\"
  44.     sTblQryName = "NADB"
  45.     sSectionName = sTblQryName
  46.     bIncFldNames = True
  47.  
  48.    fname = "schema_" & sTblQryName & ".ini"
  49.  
  50.    Handle = FreeFile
  51.    Open sPath & "schema_" & sTblQryName & ".xls" For Output Access Write As #Handle
  52.    ' -----------------------------------------------
  53.    ' Write schema header.
  54.    ' -----------------------------------------------
  55.    Print #Handle, "Table : "; sSectionName
  56.    'Print #Handle, "ColNameHeader = " & IIf(bIncFldNames, "True", "False")
  57.    Print #Handle, "Created BY : SUNNY LUTHRA"
  58.    Print #Handle, "Date : "; Now
  59.    Print #Handle, "" & "Column Name" & Chr(9) & "Data Type" & Chr(9) & "Size" & Chr(9) & "Description"
  60.    ' -----------------------------------------------
  61.    ' Get data concerning schema file.
  62.    ' -----------------------------------------------
  63.       Set tblDef = db.TableDefs(sTblQryName)
  64.    With tblDef
  65.       For i = 0 To .Fields.Count - 1
  66.          Set fldDef = .Fields(i)
  67.          With fldDef
  68.             fldName = .Name
  69.             Select Case .Type
  70.                Case dbBoolean
  71.                   fldDataInfo = "Bit" & Chr(9)
  72.                Case dbByte
  73.                   fldDataInfo = "Byte" & Chr(9)
  74.                Case dbInteger
  75.                   fldDataInfo = "Short" & Chr(9)
  76.                Case dbLong
  77.                   fldDataInfo = "Integer" & Chr(9)
  78.                Case dbCurrency
  79.                   fldDataInfo = "Currency" & Chr(9)
  80.                Case dbSingle
  81.                   fldDataInfo = "Single" & Chr(9)
  82.                Case dbDouble
  83.                   fldDataInfo = "Double" & Chr(9)
  84.                Case dbDate
  85.                   fldDataInfo = "Date" & Chr(9)
  86.                Case dbText
  87.                   fldDataInfo = "Char " & Chr(9) & Format$(.Size)
  88.                Case dbLongBinary
  89.                   fldDataInfo = "OLE" & Chr(9)
  90.                Case dbMemo
  91.                   fldDataInfo = "LongChar" & Chr(9)
  92.                Case dbGUID
  93.                   fldDataInfo = "Char" & Chr(9) & "16"
  94.             End Select
  95.            flddescrip = .Properties("Description")
  96.  
  97.             Print #Handle, "" & fldName & _
  98.                              Chr(9) & fldDataInfo & Chr(9) & flddescrip
  99.          End With
  100.                Next i
  101.    End With
  102.    MsgBox sPath & fname & " has been created."
  103.    CreateSchemaFile = True
  104. CreateSchemaFile_End:
  105.    Close Handle
  106. Exit_ShowDescrip:
  107. Exit Function
  108. Err_ShowDescrip:
  109. If Err = 3270 Then
  110. flddescrip = ""
  111. Resume Next
  112. Else
  113. MsgBox (Err & ": " & Error$), , "ShowDescrip()"
  114. End If
  115. End Function
  116.  
  117.  
  118.  
  119. Private Function BrowseForfile(pstrPath, pstrFilter)
  120. Set objDialog = CreateObject("UserAccounts.CommonDialog")
  121. objDialog.Filter = pstrFilter
  122. objDialog.InitialDir = pstrPath
  123. objDialog.Flags = &H80000 + &H4 + &H8
  124. intResult = objDialog.ShowOpen
  125. BrowseForfile = objDialog.FileName
  126.  
  127.             If intResult = 0 Then
  128.             MsgBox "No file selected - Exiting"
  129.             End If
  130. End Function
  131.  
**********************************
Can Anyone Knows How to set the database path here:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & "; "
So that instead of defining the path of data source.
Data Source should automatically takes the value in the text box or the file I open using file dialog box.
Or Anyone Having working code for opening an mdb file & displaying the properties of the tables. & then stores it in Excel file.
Jan 8 '08 #1
Share this Question
Share on Google+
1 Reply


JKing
Expert 100+
P: 1,206
Hi there,

Could you not just alter your CreateSchemaFile Function to take both an spath and a dpath. This way when you call the function you can pass in the returned value of BrowseForFile as the dpath.

Jking
Jan 8 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.