Tim, who is a team leader at AmeriData Consulting, has designed and developed client/server applications for six years and is the author of Photo-Based 3D Graphics in C++ (John Wiley & Sons, 1995). Tim can be reached on CompuServe at 70403,3570.
Building client/server applications often involves getting business experts together with modelers, GUI designers, and developers. The activities of these teams are usually divided into phases. In the first phase, a modeling team gathers requirements from key persons within the organization. Process models and/or a data model are then constructed using a computer assisted software engineering (CASE) tool. Once the data model is completed, the modeling team passes off the project to the development team. This begins the second phase, in which the requirements and data model are merged into a functioning information-management system.
The transition between phases is critical because intangibles (user expectations, for instance) can be lost in the shuffle. In this article, I'll present DBA Assistant, a tool designed to smooth the transition between the project phases in two ways:
When there is no clean break between the modeling and development efforts, it is desirable to keep the data model synchronized with the physical database definition as the model and database are refined. In many situations, no convenient synchronization mechanisms exist. DBA Assistant can reduce the synchronization effort.
DBA Assistant also lets the modeling team move the data model into an approximation of a functioning database suitable for efficient prototyping of client/server application screens. The drag-and-drop screen-building features of most GUI development tools (PowerBuilder, Access, Visual Basic, and the like) can only be used if an underlying database is available. Having a representative database allows prototype screens to be prepared for the purpose of exploring and resolving GUI design issues before full-scale development begins.
DBA Assistant, which is written in Microsoft Access Basic, provides a two-way link between a CASE tool (System Architect from Popkin Software, in this case) and a target DBMS. The program provides two essential capabilities:
Most of Access's internal objects have been organized into the DAO hierarchy and can be manipulated using the Access Basic language. My approach utilizes properties of the DAO to describe the physical structure of a selected database. My goal is to collect a description of all the tables in an Access database (including descriptions of each column associated with a database table) along with the data types and column lengths. To accomplish this, I'll focus on two DAO collections--TableDefs and Fields.
The TableDefs collection contains a description of all tables visible to an Access database, including attached tables (for which read permission has been granted). The number of tables in TableDefs is a property of the collection numTables=localDB.tableDefs.count, where localDB is a pointer to the local Access database. Table names can be retrieved by tableName=localDB.tableDefs(i).name, and the entire list of tables can be obtained by varying i from 0 to numTables-1. Once a table is identified, the Fields collection lets you identify its attributes or fields.
After the table name is obtained from TableDefs, you must determine which fields are associated with the table. The number of fields in each table in Table-Defs can be obtained with numFields=localDB.tableDefs(i).fields.count. You get the properties of each field in the table using property=localDB.table Defs(i).fields (j). prop. By varying j from 0 to numFields-1, you can get the properties of each field in a table in TableDefs. For example, to get the name of the first column in the first table, enter fieldName=localDB.tableDefs(0).fields(0).name. You can also get the datatype and length of the field in bytes this way.
Listing One presents the Access Basic function read-AccessDB, which traverses the DAO, extracting the database schema from the DAO TableDefs and Fields collections. The resulting schema information is stored in the dbDictionary Access database table. Once the database dictionary is created by readAccessDB, the CASE-tool-compatible text files and the DDL files are created.
To export to System Architect, you first create the entity.txt file (all files are available electronically; see "Availability," page 3) that lists the entities to appear in the ER model. Next, create the datastrc.txt file, which contains System Architect data structures that link the appropriate attributes for each entity. A third text file, element.csv, is created in comma separated value (CSV) format and lists all the data elements in the entire database, along with the associated table and data type. Finally, table.sql is created; it contains a description of the database using DDL statements. Each of these text files is created in the procedure readAccessDB in Listing One.
The schema-export approach assumes a one-to-one correspondence between physical database tables and entities in a physical ER diagram. Similarly, a one-to-one correspondence is assumed between the columns in a database table and attributes associated with an entity in a physical model. This means that primary and foreign keys appear as attributes in the physical ER model prepared from the exported schema. DBA Assistant assumes that the primary key in each table is indicated by the prefix PK_ followed by the name of the table. (For example, the primary key for the dbDictionary table would be PK_dbDictionary.) When a member of the DAO Fields collection conforms to this assumption, DBA Assistant adds a "@" prefix to the column name while creating the exported-schema text files. System Architect then interprets the column as a primary key and indicates it as such in the physical ER diagram.
At this point, you can generate a DDL statement that defines a database table and all its columns; in other words, it defines an entity and its attributes, together with a portion of the metadata. This requires the SQL Create Table statement, a permutation of the schema information obtained earlier by traversing the DAO (for example, CREATE TABLE table (field1 type [(size)] [index1] [, field2 type [(size)] [index2] [, ...]])). The Access Basic procedure readAccessDB generates the file table.sql at the same time the CASE-compatible text files are generated.
Since Access's set of database data types differ from those which can be selected in System Architect, you need a mapping function to transform the data type of each Access database attribute into a corresponding data type compatible with System Architect. To build this, you simply use a look-up scheme; see the mapSATypeToAccessType function in Listing Two, which returns the Access data type that most closely corresponds to a System Architect-compatible data type supplied as the function's argument.
DBA Assistant is an Access application. To use the tool's schema-export feature, open DBA Assistant. When Figure 3 appears, press the "Export a dB Schema" button; Figure 4 will appear. Choose the Access database from which to export by pressing the upper "Locate" button and selecting the desired .mdb file. Specify the directory in which to place the exported schema text files by pressing the lower "Locate" button. When the "Export the DB" button is pressed, the schema of the selected Access database is exported. When the procedure is complete, the four text files described previously should exist in the same directory as the Access application.
To import definitions into System Architect, first create a new System Architect encyclopedia. Select "Import Data" from the Definition menu; Figure 5 appears. Select "Entity" from the first combo box, then Text. Supply the name of the entity text file produced by DBA Assistant (entity.txt) and click the "OK" button. Repeat this for the data-structure file. Next, elements are imported by choosing ".CSV" format and "Data" element from the appropriate combo boxes. Enter the pathname of the element file created by DBA Assistant (element.csv in Figure 5) and click "OK."
At this point, all of the relevant information has been imported into System Architect and the encyclopedia needs to be updated. Select "Dictionary Update" from the System Architect File menu and answer "yes" to the question: Do you wish to update all definitions? At this point a new ER diagram can be created, and you can create entities by dragging and dropping onto it.
Building an Access database from an ER diagram begins by exporting a physical data model from System Architect. First you export its statement, then import the physical model into Access using DBA Assistant. A new ODBC-compliant database is created from the model by generating and executing the appropriate DDL statements.
You can export the data-model elements from System Architect. ER model attributes marked by the modeling team as primary keys in System Architect are indicated in the element.csv file by an @ sign preceding the element name.
A database is created by reading the exported data model in element.csv into Access using readElementsfile, which reads the ER model definition and populates the dbDictionary table. Next, the entities and attributes defined in dbDictionary generate the tables in the Access database file; see the Access Basic procedure makeAccessTables. These procedures appear in Listing Two.
The procedure mapSATypetoAccessType performs a lookup-style substitution of System Architect C Storage types to Access database data types. Character strings longer than 255 are converted to the Access memo type. You may wish to add other types of mappings for Access types such as Date/Time and Yes/No.
The Access Basic procedure makeAccessTables constructs an SQL Create Table statement from information contained in the internal dbDictionary table. This statement executes in the remote database using the ODBC connection created between DBA Assistant and the remote Access database file identified by the user. This procedure could easily be extended to accept an ODBC data-source name instead of an Access .mdb file name. In this way, databases could be created in any ODBC-compliant DBMS.
The System Architect data-element export file contains only data elements. Any additional elements designated as keys in the model are exported in the System Architect entities export file. To transfer the keys, the entity.csv file is entered into the DBA Assistant. When "Create Database" is pressed, the data element and entity .csv files are processed to remove extra carriage returns. This converts the entity export from System Architect into a valid .csv file. This file is then imported into Access via TransferText. The resulting table has the name "entity" and contains one record for each entity defined in the System Architect model. The first column indicates the entity name; the second contains the description of all the attributes associated with this entity. Unique keys have an @ prefix, and foreign keys are indicated by the keyword "FKFROM."
The Access Basic CreateKeys procedure reads the entity table, identifies all keys of both types, and saves the key definitions in the dbKey Access table. Once the keys are saved, the additional key columns are added to the existing Access table definitions using the SQL Alter Table statement.
An associative entity or class defined in the data model may have attributes that consist entirely of keys. In this case, the associative entity would not have been defined in System Architect's data-element export file because technically, none of the entity's attributes are data elements--they are keys. Consequently, the entity name indicated in the System Architect entity export file may not exist in the partially created database. In this case, issuing an SQL Alter Table statement to the database for a table which does not exist will result in an error. Fortunately, you can detect this error and respond appropriately. The procedure first issues an Alter Table statement. If a "table doesn't exist" error results, it constructs a SQL Create Table statement using only the single key column. Subsequent key columns defined in the model for this entity will be added using alternative table statements as additional records in the dbKey table are processed. Since the key attributes are not contained in the data element's exported file, there is no way to determine the key column's data type from the information exported from System Architect. Consequently, DBA Assistant assumes that all keys are of type long integer. A workaround is to encode the data-type information in the data-element name; for example, add a _Date suffix to the data-element name for keys of type Date. Then, a mapping function can be constructed in Access to identify the suffix and create the keys with the desired type information.
Finally, for each key column indicated in the dbKeys table, CreateKeys creates a simple database index to speed performance during queries. Data-integrity constraints can be placed on each index to indicate that the column is, say, mandatory, or must be unique. DBA Assistant creates the simplest type of index using the SQL Create Index statement as in CREATE INDEX IndexName ON TableName (ColumnName);. A test data model (element.csv) lets you create databases.
DBA Assistant is useful for bridging the gap between modeling efforts and creating the physical database upon which client/server applications are constructed. The use of tools such as DB Assistant can result in more-effective management of client/server development projects because the amount of time and effort required to synchronize a physical ER Model to a corresponding database is reduced.
System Architect
Popkin Software & Systems
11 Park Place
New York, NY 10007
800-732-5227
Microsoft Access
Microsoft Corp.
One Microsoft Way
Redmond, WA 98052
206-882-8080
Figure 1: Path from a DBMS to a CASE tool.
Figure 2: Path from a CASE tool to a DBMS.
Figure 3: Opening DBA Assistant screen.
Figure 4: DBA Assistant Export screen.
Figure 5: System Architect Import Data screen.
Sub readAccessDB (myDB As Database, theControl As Control, rootDirectory)
' save the structure of an Access database using the DAO hierarchy
' Access --> CASE Tool
Dim crlf, dbName, numTables, counter, sql, tableName, foreignKeys
Dim sqlTableName,createTable,theTypeCode,theLength,theName,theColumnName
Dim thePureColumnName, sqlCol
Dim theDescription
crlf = Chr(10)
Dim localDB As Database
Set localDB = dbengine.workspaces(0).databases(0)
Dim i As Integer, j As Integer, cols As Integer
Dim theType
DoCmd Hourglass True
If Mid(rootDirectory, Len(rootDirectory), 1) <> "\" Then
rootDirectory = rootDirectory & "\"
End If
Open rootDirectory & "entity.txt" For Output As #1
Open rootDirectory & "datastrc.txt" For Output As #2
Open rootDirectory & "element.csv" For Output As #3
Open rootDirectory & "table.sql" For Output As #4
Print #3, "Name,Description,Domain,Comments,Length,Business Unit,
Column Name,Database,Table,Version,C Storage Type,C Storage Occurrences,
Storage Class,Storage Picture,Display Picture"
dbName = myDB.name
numTables = myDB.tabledefs.count
counter = 0
sql = "delete from dbDictionary;" ' clear the dictionary
localDB.Execute (sql)
For i = 0 To numTables - 1
tableName = myDB.tabledefs(i).name
theControl = "Exporting table: " & tableName
DoEvents
foreignKeys = ""
If (tableName <> "Paste Errors") And (Mid(tableName, 1, 4) <> "MSys")
Then 'ignore system tables
Print #1, "<<" & tableName & ">>"
Print #2, "<<" & tableName & ">>"
Print #1, tableName
cols = myDB.tabledefs(i).fields.count
sqlTableName = "[" & tableName & "]"
createTable = "drop table " & sqlTableName & ";"
Print #4, createTable
createTable = "create table " & sqlTableName & " ("
Print #4, createTable
For j = 0 To cols - 1
theTypeCode = myDB.tabledefs(i).fields(j).type
theLength = myDB.tabledefs(i).fields(j).size
theName = myDB.tabledefs(i).fields(j).name
theColumnName = myDB.tabledefs(i).fields(j).name
theDescription = " " ' none for now. see TechNet article Q109136
theType = "Undefined"
Select Case theTypeCode
Case DB_SINGLE
theType = "Float"
Case DB_TEXT
theType = "Char"
Case DB_DOUBLE
theType = "Float"
theLength = 8
Case DB_MEMO
theType = "Char"
theLength = 1024
Case DB_DATE
theType = "Char"
theLength = 8
Case DB_LONG
theType = "Long"
theLength = 4
Case DB_BYTE
theType = "Byte"
Case DB_integer
theType = "integer"
theLength = 2
Case DB_BOOLEAN
theType = "Char"
theLength = 1
Case DB_CURRENCY
theType = "Float"
theLength = 4
Case Else
theType = "Undefined:" & theTypeCode
End Select
' identify the primary key to system architect by adding the @ prefix
theColumnName = theName
thePureColumnName = theName
If Len(theName) > Len(tableName) And Mid(theName, 4,
Len(tableName)) = tableName And Mid(theName, 1, 3) = "PK_"
Then theColumnName = "@" & theName
If Len(theName) > Len(tableName) And Mid(theName, 4,
Len(tableName)) = tableName And Mid(theName, 1, 3) = "FK_"
Then foreignKeys = foreignKeys & theName
Print #3, """" & theName & """,,,,""" & theLength & """,
Engineering,""" & theColumnName & """,""" & dbName & """,
""" & tableName & """,,""" & theType & """,,,,"
sql = "insert into dbDictionary (tableName,columnName,columnType,
width,description) "
sql = sql & "values (""" & tableName &""",""" & thePureColumnName &""",
""" & theType & """," & theLength & ",""" & theDescription & """);"
localDB.Execute (sql)
If j < cols - 1 Then
Print #2, """" & theColumnName & """ + "
Else
Print #2, """" & theColumnName & """"
End If
sqlCol = Mid(thePureColumnName, 1, 18)
createTable = "[" & sqlCol & "]" & " " & UCase$(theType)
If theType = "Text" Then
createTable = createTable & " (" & theLength & ")"
End If
If j <> cols - 1 Then createTable = createTable & ","
Print #4, createTable
counter = counter + 1
Next j
createTable = ");"
Print #1,
Print #2,
Print #4, createTable
Print #4, "Create unique index PKI_" & sqlTableName & " on " &
sqlTableName & "(" & "PK_" & sqlTableName & ");"
Print #4, "Grant all on " & sqlTableName & " to public;"
Print #4, ' sql statement
End If
Next i
localDB.Close
Close #1
Close #2
Close #3
Close #4
DoCmd Hourglass False
End Sub
Sub readElementsFile (elementsFilePath, theControl As Control)
Dim sql, recordCounter, errCounter
Dim myDB As Database
Dim i As Integer, j As Integer, cols As Integer
' DoCmd Hourglass True
Set myDB = dbengine.workspaces(0).databases(0)
sql = "delete from dbDictionary" 'clear the existing db definition
myDB.Execute sql
Open elementsFilePath For Input As #1
Dim aName, description, domain, Comments, length, BusinessUnit,
columnName,aDatabase,aTable,Version, CStorageType, CStorageOccurrences,
StorageClass, StoragePicture, DisplayPicture, a1, a2, a3
recordCounter = 0
Open "DBAssist.log" For Output As #2
Do While Not EOF(1)
Input #1,aName,description,domain,Comments,length,BusinessUnit,columnName,
aDatabase,aTable,Version,CStorageType,CStorageOccurrences,
StorageClass,StoragePicture,DisplayPicture,a1,a2,a3
If recordCounter > 0 Then
' If Mid(columnName, 1, 1) = "@" Then
' columnName = Mid(columnName, 2, Len(columnName))
' End If
If Len(aTable) = 0 Or Len(columnName) = 0
Or Len(CStorageType) = 0 Then
Print #2, "Table, Column or Storage Type not defined. record: ",
recordCounter, aName, description
errCounter = errCounter + 1
Else
theControl = aTable & ", " & columnName
DoEvents
sql = "insert into dbDictionary "
sql = sql & "(columnName,width,tableName,columnType,description,
sysArchColName) "
sql = sql & "values (""" & columnName & """," & length & ",
""" & aTable & """,""" & CStorageType & """,
""" & description & """,""" & aName & """ );"
myDB.Execute sql
End If
End If
recordCounter = recordCounter + 1
Loop
myDB.Close
Close #1
Close #2
DoCmd Hourglass False
If errCounter > 0 Then theControl = "Errors occurred. check DBAssist.log"
End Sub
Sub readEntity (theControl As Control)
' create indices from information in the entity table
Dim sql, recordCounter, errCounter
Dim i As Integer, j As Integer, cols As Integer
' DoCmd Hourglass True
Dim atsign, keyCounter, tableName, columnName, keyType, keyName
Dim localDB As Database
Set localDB = dbengine.workspaces(0).databases(0)
localDB.Execute "delete from dbKey"
Dim sn As Recordset
sql = "select * from entity"
Set sn = localDB.OpenRecordset(sql, DB_OPEN_SNAPSHOT)
If sn.recordcount = 0 Then
MsgBox "Entity table is empty"
Exit Sub
End If
keyCounter = 1
Dim theLine, firstquote, numItems As Integer
recordCounter = 0
sn.MoveFirst
Dim tableDescription As String
Do While Not sn.EOF
tableName = sn("name")
If Not IsNull(sn.description) Then
tableDescription = sn.description
numItems = getNumItems(tableDescription, "+")
For j = 1 To numItems
theLine = getItem(tableDescription, "+", j)
atsign = InStr(theLine, "@")
If atsign > 0 Then
If atsign > 1 Then 'beginning of new table definition
columnName = Mid(theLine, InStr(theLine, """") + 1, 99)
columnName = Mid(columnName, 1, InStr(columnName, """") - 1)
keyType = "Unique"
If InStr(theLine, "FKFROM") > 0 Then keyType = "Foreign"
keyName = "index" & keyCounter
keyCounter = keyCounter + 1
theControl = "Reading key: " & keyName & " on " & columnName
DoEvents
sql = "insert into dbKey "
sql = sql & "(keyName,tableName,columnName,keyType) "
sql = sql & "values (""" & keyName & """,""" & tableName & """,
""" & columnName & """,""" & keyType & """);"
localDB.Execute sql
Else 'this line is part of a composite key
firstquote = InStr(theLine, """")
columnName = Mid(theLine, firstquote + 1, InStr(firstquote + 1,
theLine, """") - (firstquote + 1))
keyType = "Unique"
keyName = "index" & keyCounter
keyCounter = keyCounter + 1
If InStr(theLine, "FKFROM") > 0 Then keyType = "Foreign"
keyCounter = keyCounter + 1
theControl = "Reading key:" & keyName & " on " & columnName
DoEvents
sql = "insert into dbKey "
sql = sql & "(keyName,tableName,columnName, keyType) "
sql = sql & "values (""" & keyName & """,""" & tableName & """,
""" & columnName & """,""" & keyType & """);"
localDB.Execute sql
End If
Else
' no at sign - check for foreign key
Dim foreignKey, k, firstChar
firstChar = -1
foreignKey = InStr(theLine, """ / FKFROM")
If foreignKey > 0 Then
' check if the first char is a quote
If Mid(theLine, 1, 1) = """" Then
columnName = Mid(theLine, 2, InStr(2, theLine, """") - 2)
Else
' Look for the columnName. Search backward from token until a " is found
For k = foreignKey - 1 To 1 Step -1
If Mid(theLine, k, 1) = """" Then
firstChar = k + 1
Exit For
End If
Next k
columnName = Mid(theLine, firstChar, foreignKey - firstChar)
' Debug.Print columnName
End If
keyType = "Foreign"
keyName = "index" & keyCounter
keyCounter = keyCounter + 1
theControl = "Reading foreign key:" & keyName & " on " & columnName
DoEvents
sql = "insert into dbKey "
sql = sql & "(keyName,tableName,columnName, keyType) "
sql = sql & "values (""" & keyName & """,""" & tableName & """,
""" & columnName & """,""" & keyType & """);"
localDB.Execute sql
End If
End If
Next j
End If
recordCounter = recordCounter + 1
sn.MoveNext
Loop
sn.Close
localDB.Close
DoCmd Hourglass False
End Sub
Option Compare Database 'Use database order for string comparisons
Option Explicit
Sub createAccessTables (accessDBPath, theControl As Control)
' Create an Access database by reading the DB dictionary, generating and
' executing SQL DDL statements
' CASE Tool --> Access
'
On Error GoTo DBErrHandler
Dim localDB As Database, remoteDB As Database
Dim localSN As Recordset, remoteSN As Recordset
Dim localSQL, remoteSql
Set localDB = dbengine.workspaces(0).databases(0)
Set remoteDB = OpenDatabase(accessDBPath)
localSQL = "select * from dbDictionary order by tableName"
Set localSN = localDB.OpenRecordset(localSQL, DB_OPEN_SNAPSHOT)
If localSN.recordcount = 0 Then
MsgBox "DB dictionary is empty. Cannot continue"
Exit Sub
End If
Dim oldTableName, tableName, inType, outType, inLength,outLength,theColumnName
Dim lengthPhrase, sql
Dim createPrimaryKeys, indexColName
Dim theDescription, SAName
localSN.MoveFirst
tableName = localSN.tableName
oldTableName = tableName
'start the new SQL statement
remoteSql = "create table [" & tableName & "]
([" & getKeyName(tableName) & "] Counter, "
createPrimaryKeys = True
Do Until localSN.EOF
If tableName <> oldTableName Then
remoteSql = Mid(remoteSql, 1, Len(remoteSql) - 1)
remoteSql = remoteSql & ");"
theControl = "Creating table: " & oldTableName
DoEvents
sql = "drop table [" & oldTableName & "]"
remoteDB.Execute (sql)
nextStatement:
remoteDB.Execute (remoteSql) 'create table in remote DB
' using standard SQL, create a primary key for this table
If createPrimaryKeys Then
remoteSql = "create unique index " & "pki_primary" &
" on [" & oldTableName
remoteSql = remoteSql & "] ([" & getKeyName(oldTableName) & "])
with primary;"
remoteDB.Execute (remoteSql)
End If
'start the new SQL statement
remoteSql = "create table [" & tableName & "] ([" & getKeyName(tableName)
& "] Counter, "
End If
inType = localSN.columnType
inLength = localSN.Width
theColumnName = localSN.columnName
theDescription = localSN.description
SAName = localSN.sysArchColName
Dim aType, aLength
' SA does not have a Date/Time type. Retrieve from SA logical attribute name
Call mapColNameToType(SAName, aType, aLength)
Call mapSATypeToAccessType(inType, inLength, outType, outLength)
If aType = "Date" And outType = "Text" Then outType = aType
If aType = "Currency" And outType = "Float" Then outType = aType
lengthPhrase = ""
If outType = "Text" Then lengthPhrase = " (" & outLength & ") "
remoteSql = remoteSql & "[" & theColumnName &
"] " & outType & lengthPhrase & ","
oldTableName = tableName
localSN.MoveNext
If Not localSN.EOF Then tableName = localSN.tableName
Loop
Exit Sub
DBErrHandler:
Select Case Err
Case 3376 'Table doesn't exist
Case Else
MsgBox Error(Err)
MsgBox remoteSql
End Select
Resume nextStatement:
End Sub
Sub createKeys (mdbPath, theControl As Control)
On Error GoTo errHandler
Dim remoteDB As Database, localDB As Database
Dim sn As Recordset
Dim sql, indexSql,aTableName,aKeyName,aType,aKeyType,aColumnName,loopCounter
Set localDB = dbengine.workspaces(0).databases(0)
Set remoteDB = OpenDatabase(mdbPath)
indexSql = ""
loopCounter = 1
sql = "select * from dbKey order by tableName, keyType desc"
Set sn = localDB.OpenRecordset(sql, DB_OPEN_SNAPSHOT)
If sn.recordcount = 0 Then
MsgBox "DB key table is empty"
Return
End If
Do While Not sn.EOF
aTableName = sn.tableName
sql = "alter table [" & aTableName & "] Add column "
aKeyName = sn.keyName
aKeyName = "index" & loopCounter
aKeyType = sn.keyType
aColumnName = sn.columnName
aType = "Long"
sql = sql & "[" & aColumnName & "] " & aType
theControl = "Adding " & aKeyName & " to table: " & aTableName
DoEvents
remoteDB.Execute (sql)
' create simple keys
indexSql = "create index " & aKeyName & " on [" & aTableName & "]
([" & aColumnName & "] "
indexSql = indexSql & ")"
remoteDB.Execute (indexSql)
indexSql = ""
loopCounter = loopCounter + 1
sn.MoveNext
Loop
Exit Sub
errHandler:
Select Case Err
Case 3380 'Ignore the error: Column already exists
Case 3375 'Ignore the error: Table already has an index named ...
Case 3376 'Handle the error: Table doesnt exist
' We assume: This table doesn't exist because all its columns are keys.
' Create the table
indexSql = "create table ["& aTableName &"] ([" & aColumnName & "] long )"
remoteDB.Execute (indexSql)
Case Else
MsgBox Error(Err)
MsgBox sql
End Select
Resume Next
End Sub
Function getKeyName (columnName)
Dim keyName
keyName = "PK_" & columnName
getKeyName = keyName
End Function
Sub mapAccessTypeToAccessType (inType, inLength, outType, outLength)
outType = inType
outLength = inLength
End Sub
Sub mapColNameToType (colName, aType, aLength)
' This mapping assumes the column names consist of several words separated
' by spaces. Last grouping of letters is a mneumonic which indicates type and
' length of the variable
Static inTypes(16), outTypes(16), lengths(16)
Dim numValues
numValues = 16
inTypes(0) = "ADD"
inTypes(1) = "CD"
inTypes(2) = "Ct"
inTypes(3) = "DT"
inTypes(4) = "DESC"
inTypes(5) = "FT"
inTypes(6) = "HR"
inTypes(7) = "ID"
inTypes(8) = "NM"
inTypes(9) = "NO"
inTypes(10) = "PCT"
inTypes(11) = "RT"
inTypes(12) = "TYP"
inTypes(13) = "YR"
inTypes(14) = "YN"
inTypes(15) = "Amt"
outTypes(0) = "Text"
outTypes(1) = "Text"
outTypes(2) = "Long"
outTypes(3) = "Date"
outTypes(4) = "Memo"
outTypes(5) = "Long"
outTypes(6) = "Integer"
outTypes(7) = "Text"
outTypes(8) = "Text"
outTypes(9) = "Long"
outTypes(10) = "Double"
outTypes(11) = "Double"
outTypes(12) = "Text"
outTypes(13) = "Integer"
outTypes(14) = "Yes/No"
outTypes(15) = "Currency"
lengths(0) = "50"
lengths(1) = "3"
lengths(2) = ""
lengths(3) = ""
lengths(4) = ""
lengths(5) = ""
lengths(6) = ""
lengths(7) = "15"
lengths(8) = "50"
lengths(9) = ""
lengths(10) = ""
lengths(11) = ""
lengths(12) = "3"
lengths(13) = ""
lengths(14) = ""
lengths(15) = ""
' get the type code from the column name
Dim i As Integer
Dim lastSpace
' find the last space in the columnName
lastSpace = 0
For i = 1 To Len(colName)
If Mid(colName, i, 1) = " " Then lastSpace = i
Next i
If lastSpace = 0 Then
MsgBox "Column name must contain at least one space: " & colName
Exit Sub
End If
Dim inType
inType = Mid(colName, lastSpace + 1, Len(colName))
'Debug.Print inType
Dim found, j As Integer
found = 0
For j = 0 To numValues - 1
If inType = inTypes(j) Then
found = 1
aType = outTypes(j)
aLength = lengths(j)
' Debug.Print aType & ":"; aLength
Exit Sub
End If
Next j
If found = 0 Then
' MsgBox "mapColNameToType. Unknown type: " & inType
aType = ""
aLength = ""
End If
End Sub
Sub mapSATypeToAccessType (inType, inLength, outType, outLength)
' This mapping converts an SA "C Type" to a valid Access Type
' An input type of Char with length > 255 is converted to Access type Memo
Static inTypes(10), outTypes(10)
Dim numValues
numValues = 9
inTypes(0) = "char"
inTypes(1) = "int"
inTypes(2) = "unsigned"
inTypes(3) = "char near *"
inTypes(4) = "char far *"
inTypes(5) = "long"
inTypes(6) = "unsigned long"
inTypes(7) = "float"
inTypes(8) = "double"
outTypes(0) = "Text"
outTypes(1) = "Integer"
outTypes(2) = "Long"
outTypes(3) = "Text"
outTypes(4) = "Memo"
outTypes(5) = "Long"
outTypes(6) = "Long"
outTypes(7) = "Single"
outTypes(8) = "Double"
Dim found, j As Integer
found = 0
For j = 0 To numValues - 1
If inType = inTypes(j) Then
found = 1
outType = outTypes(j)
outLength = inLength
If outType = "Memo" Then outLength = ""
' Debug.Print outType & ":"; outLength
Exit Sub
End If
Next j
If found = 0 Then
MsgBox "Unknown type: " & inType
End If
End Sub
Sub preprocessEntityFile (entityFilePath, processedFilePath)
On Error Resume Next 'turn off error reporting
' remove the extra carriage returns to make a valid .csv file, then
' import the file into access for further processing
Dim recordCounter, errCounter
Dim i As Integer, commasPerRecord As Integer
Dim lineLength As Integer
Dim theLine As String, lineBuffer As String
Dim totalCommas As Integer, commaCount As Integer
Open entityFilePath For Input As #1
Open processedFilePath For Output As #2
commasPerRecord = 12
lineBuffer = ""
totalCommas = 0
Do While Not EOF(1)
Line Input #1, theLine
lineLength = Len(theLine)
commaCount = 0
For i = 1 To lineLength
If Mid(theLine, i, 1) = "," Then
commaCount = commaCount + 1
End If
Next i
totalCommas = totalCommas + commaCount
If totalCommas < commasPerRecord Then
lineBuffer = lineBuffer & theLine
Else
lineBuffer = lineBuffer & theLine
Print #2, lineBuffer
lineBuffer = ""
totalCommas = 0
End If
Loop
Close #1
Close #2
DoCmd SetWarnings False
Dim localDB As Database
Set localDB = dbengine.workspaces(0).databases(0)
localDB.Execute ("drop table entity")
DoCmd TransferText , , "Entity", processedFilePath, True
DoCmd SetWarnings True
localDB.Close
On Error GoTo 0 'turn on error reporting
End Sub
DDJ
Copyright © 1995, Dr. Dobb's Journal