Menu
I am trying to extract the schema of an .mdb database, so that I can recreate the database elsewhere.
How can I pull off something like this?
930
Solution 1: Use Microsoft Excel Step 2: Look for the Data tab on the main ribbon. Step 3: Select the Access Data icon in the Get External Data section. Step 4: Browse for MDB file and click Open, select the table from the database which you desire to add in Excel, and click OK.
A database schema is a collection of metadata that describes the relationships between objects and information in a database. An easy way to envision a schema is to think of it as a box that holds tables, stored procedures, views, and related data assets. A schema defines the infrastructure of this box.
It is possible to do a little with VBA. For example, here is a start on creating script for a database with local tables.
Dim db As Database Dim tdf As TableDef Dim fld As DAO.Field Dim ndx As DAO.Index Dim strSQL As String Dim strFlds As String Dim strCn As String Dim fs, f Set db = CurrentDb Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile("C:\Docs\Schema.txt") For Each tdf In db.TableDefs If Left(tdf.Name, 4) <> "Msys" Then strSQL = "strSQL=""CREATE TABLE [" & tdf.Name & "] (" strFlds = "" For Each fld In tdf.Fields strFlds = strFlds & ",[" & fld.Name & "] " Select Case fld.Type Case dbText 'No look-up fields strFlds = strFlds & "Text (" & fld.Size & ")" Case dbLong If (fld.Attributes And dbAutoIncrField) = 0& Then strFlds = strFlds & "Long" Else strFlds = strFlds & "Counter" End If Case dbBoolean strFlds = strFlds & "YesNo" Case dbByte strFlds = strFlds & "Byte" Case dbInteger strFlds = strFlds & "Integer" Case dbCurrency strFlds = strFlds & "Currency" Case dbSingle strFlds = strFlds & "Single" Case dbDouble strFlds = strFlds & "Double" Case dbDate strFlds = strFlds & "DateTime" Case dbBinary strFlds = strFlds & "Binary" Case dbLongBinary strFlds = strFlds & "OLE Object" Case dbMemo If (fld.Attributes And dbHyperlinkField) = 0& Then strFlds = strFlds & "Memo" Else strFlds = strFlds & "Hyperlink" End If Case dbGUID strFlds = strFlds & "GUID" End Select Next strSQL = strSQL & Mid(strFlds, 2) & " )""" & vbCrLf & "Currentdb.Execute strSQL" f.WriteLine vbCrLf & strSQL 'Indexes For Each ndx In tdf.Indexes If ndx.Unique Then strSQL = "strSQL=""CREATE UNIQUE INDEX " Else strSQL = "strSQL=""CREATE INDEX " End If strSQL = strSQL & "[" & ndx.Name & "] ON [" & tdf.Name & "] (" strFlds = "" For Each fld In tdf.Fields strFlds = strFlds & ",[" & fld.Name & "]" Next strSQL = strSQL & Mid(strFlds, 2) & ") " strCn = "" If ndx.Primary Then strCn = " PRIMARY" End If If ndx.Required Then strCn = strCn & " DISALLOW NULL" End If If ndx.IgnoreNulls Then strCn = strCn & " IGNORE NULL" End If If Trim(strCn) <> vbNullString Then strSQL = strSQL & " WITH" & strCn & " " End If f.WriteLine vbCrLf & strSQL & """" & vbCrLf & "Currentdb.Execute strSQL" Next End If Next f.Close If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
