|
|
|
| Title | Generate CREATE TABLE statements for an Access database in Visual Basic 6 |
|---|
| Description | This example shows how to generate CREATE TABLE statements for an Access database in Visual Basic 6. |
|---|
| Keywords | CREATE TABLE, SQL, Access, database |
|---|
| Categories | Database |
|---|
|
|
|
This program uses DAO to list the tables in an Access database and generate CREATE TABLE statements to build them.
WARNING: This example is serious under-tested. I just haven't had time to test anywhere near all of the combinations possible. It also doesn't explore relations, indexes, etc.
The output for the example database looks like this:
CREATE TABLE Book(
ISBN LONG,
PUBLISHER_ID LONG,
BOOK_TITLE TEXT(50)
);
CREATE TABLE Publisher(
PUBLISHER_ID LONG,
PUBLISHER_NAME TEXT(50)
);
Before starting, add a reference to "Microsoft DAO 3.6 Object Library" (or whatever your version is).
Function MakeAllCreateTableStatements makes a string containing all of the CREATE TABLE statements. It opens the database and loops through the tables in the TableDefs collection. It skips any table whose name begins with MSYS because those are the system tables. For each non-system table, the function calls function MakeCreateTableStatement.
|
|
' Create statements to build all of the database's tables.
Public Function MakeAllCreateTableStatements(ByVal db_name _
As String) As String
Dim db As DAO.Database
Dim table_def As DAO.TableDef
Dim rel As DAO.Relation
Dim result As String
' Open the database.
Set db = DBEngine.Workspaces(0).OpenDatabase( _
db_name, ReadOnly:=False)
' Look through the tables.
For Each table_def In db.TableDefs
' Make the CREATE TABLE statement for this table.
' Skip system tables.
If LCase$(Left$(table_def.Name, 4)) <> "msys" Then
result = result & _
MakeCreateTableStatement(table_def)
End If
Next table_def
db.Close
MakeAllCreateTableStatements = result
End Function
|
|
|
Function MakeCreateTableStatement makes a CREATE TABLE statement for one table. It loops through the table's
|
|
' Make a CREATE TABLE statement to make this table.
Private Function MakeCreateTableStatement(ByVal table_def _
As DAO.TableDef) As String
Dim result As String
Dim fld As DAO.Field
result = "CREATE TABLE " & table_def.Name & "(" & vbCrLf
' Loop through the fields.
For Each fld In table_def.Fields
result = result & MakeCreateField(fld)
Next fld
' Remove the trailing comma.
If InStr(result, ",") > 0 Then
result = Left$(result, InStrRev(result, ",") - 1) & _
vbCrLf
End If
' Return the result.
MakeCreateTableStatement = result & ");" & vbCrLf
End Function
|
|
|
Function MakeCreateField makes the part of the CREATE TABLE statement for a single field. It starts with the field's name and then adds on its type as determined by the field's type. If the field is an auto-increment field or cannot have NULL values, it adds the appropriate key words. (This version does not try to indicate anything about the field being a key.)
|
|
' Return a string representing a field's type.
Private Function MakeCreateField(ByVal fld As DAO.Field) As _
String
Dim result As String
result = " " & fld.Name
Select Case fld.Type
Case dbDate, dbTime, dbTimeStamp
result = result & " DATETIME"
Case dbMemo
result = result & " MEMO"
Case dbByte
result = result & " BYTE"
Case dbInteger
result = result & " INT"
Case dbLong
result = result & " LONG"
Case dbNumeric, dbDecimal, dbFloat
result = result & " FLOAT"
Case dbSingle
result = result & " SINGLE"
Case dbDouble
result = result & " DOUBLE"
Case dbGUID
result = result & " GUID"
Case dbBoolean
result = result & " BOOL"
Case dbCurrency
result = result & " CURRENCY"
Case dbText
result = result & " TEXT(" & fld.Size & ")"
Case Else
result = result & " ????"
End Select
If (fld.Attributes And dbAutoIncrField) Then
result = result & " AUTOINCREMENT"
End If
If fld.Required Then result = result & " NOT NULL"
MakeCreateField = result & "," & vbCrLf
End Function
|
| |
|
| |
|
|
