Designing databases on the AS/400 can be tedious. Creating source members, editing the DDS statements, and compiling the files can be a time-consuming process. Microsoft Access has a graphical interface for creating databases. Wouldn't it be nice if you could utilize the interface of Access to create databases on the AS/400? With this utility, you can accomplish just that.
Microsoft Access is very nice for defining databases. Its graphical interface makes database generation a point-and-click experience. You can define tables, fields, keys, and indexes visually, so it's easy to see what you're getting.
The utility is a Visual Basic (VB) program that transfers a Microsoft Access database (MDB) to the AS/400. It translates the definitions of the tables and, optionally, keys and data into SQL statements that are run on the AS/400. If you want, it will create an OS/400 SQL collection for the data. (An OS/400 SQL collection is a library with system-maintained files and journals that define and support the database.)
The program was written with the Visual Basic 3.0 Professional Edition. To compile the source code, you must have this program. However, once you've compiled the code, you can use the executable without having VB. If you are using Access 2.0, you'll also need the Jet 2.0/VB 3.0 Compatibility Layer, since, without this layer, VB 3.0 works only with Access 1.1 databases. You can get this utility from Microsoft's FTP site (ftp.microsoft.com). Download the file COMLYR.EXE from the SOFTLIBMSLFILES directory.
The program interfaces to the AS/400 with ODBC, so you need to have an ODBC driver with update capability for it to run. The ODBC driver included with Client Access for Windows will work for this, as will StarWare's StarSQL ODBC driver. For more information on installing and configuring an ODBC driver for use with the AS/400, see "Configuring the Client Access ODBC Driver," MC, April 1996.
The program consists of the form MCDATA.FRM and the module MCDATA.BAS. Because of the length of the code, Figures 2 and 3 are only excerpts of MCDATA.FRM and MCDATA.BAS. To run the code, you need to download the full code. (For information on how to obtain the code, see the accompanying sidebar.) Then, create a new VB project and add these files to it. To create an .EXE file, save the project and select Make EXE from the VB file menu. To run it in the VB environment, save the project and press the F5 key.
Once the program is running, you will see the screen shown in 1. You can use several options to customize the data transfer. If you want the utility to create an OS/400 SQL collection, enter the name you want for it in the Destination Collection field and choose the Create Collection checkbox. If you are running a version of OS/400 prior to V3R1, select the checkbox that says OS/400 V3R0M05 or earlier?. Selecting this box will cause the utility to generate SQL that is compatible with OS/400 versions before V3R1. In the Source Database field, enter the path and file name of the Access database that you want to transfer. (VB gurus may want to add a file-open common dialog box here.) If you want to transfer the data as well as the definition, select the Copy Data option. If you want to create the keys for the tables, select the Create Table Keys option.
Once the program is running, you will see the screen shown in Figure 1. You can use several options to customize the data transfer. If you want the utility to create an OS/400 SQL collection, enter the name you want for it in the Destination Collection field and choose the Create Collection checkbox. If you are running a version of OS/400 prior to V3R1, select the checkbox that says OS/400 V3R0M05 or earlier?. Selecting this box will cause the utility to generate SQL that is compatible with OS/400 versions before V3R1. In the Source Database field, enter the path and file name of the Access database that you want to transfer. (VB gurus may want to add a file-open common dialog box here.) If you want to transfer the data as well as the definition, select the Copy Data option. If you want to create the keys for the tables, select the Create Table Keys option.
When all the options are as you want them, click the Go! button, and the database will be transferred to your AS/400. It can be a slow process, so the program displays status messages as it performs each operation. If you want to abort the transfer, select the Exit button at any time, and the utility will stop.
When you create your databases with Access for transfer to the AS/400, you need to make sure that you use valid AS/400 table names and field names. This will make it easier to refer to those objects when they are transferred to your AS/400. The utility does not do any checking to ensure that these names follow AS/400 naming conventions.
The utility uses the data access objects (DAOs) of Visual Basic. The DAOs are just that-objects. They have properties and methods, just like other objects in VB (for more information on objects in VB, see "Using OLE with AS/400 Data," MC, July 1995). A partial DAO hierarchy is shown in 4 (page 47). Although more objects are available in VB, the figure shows only the objects used in this example. Specifically, you use the Database and TableDef objects to get information about the tables in the Access database. The Database object is a pointer to a database opened with the OpenDatabase function. TableDef objects hold the definitions of tables in the database.
The utility uses the data access objects (DAOs) of Visual Basic. The DAOs are just that-objects. They have properties and methods, just like other objects in VB (for more information on objects in VB, see "Using OLE with AS/400 Data," MC, July 1995). A partial DAO hierarchy is shown in Figure 4 (page 47). Although more objects are available in VB, the figure shows only the objects used in this example. Specifically, you use the Database and TableDef objects to get information about the tables in the Access database. The Database object is a pointer to a database opened with the OpenDatabase function. TableDef objects hold the definitions of tables in the database.
The Database object has a property that is a collection of the TableDefs in that database. A collection in Visual Basic is a property that is like an array-that is, it can have several elements. You can identify the number of entries in a collection with the count property. This allows you to step through the elements of a collection in a loop. The code in 2 steps through the TableDef collection of the database to get the name of each table in the database. Each TableDef in the database also has properties of its own. You use the fields collection of the TableDef object to get information about each of the fields in the table. Each field object also has properties, such as name and data type.
The Database object has a property that is a collection of the TableDefs in that database. A collection in Visual Basic is a property that is like an array-that is, it can have several elements. You can identify the number of entries in a collection with the count property. This allows you to step through the elements of a collection in a loop. The code in Figure 2 steps through the TableDef collection of the database to get the name of each table in the database. Each TableDef in the database also has properties of its own. You use the fields collection of the TableDef object to get information about each of the fields in the table. Each field object also has properties, such as name and data type.
To access ODBC databases with VB, you can use a couple of different methods. One method, called passthrough, executes the statements as they are entered. That is, passthrough mode passes the statement through to the ODBC data source without the Jet engine trying to interpret it. The other mode, called nonpassthrough, uses the Jet engine to interpret the SQL statement before it is passed on to the AS/400. Since the AS/400 SQL processor is best at accessing AS/400 data in the quickest manner possible, I used passthrough. The only disadvantage of passthrough is that recordsets returned are read-only.
All AS/400 SQL statements used in this example are performed using either the EXECUTE or the EXECUTESQL database methods. Both of these methods can be used to run SQL statements on the specified database. The difference is that the EXECUTE does not return the number of rows affected by the statement. It also can be used in both passthrough and nonpassthrough modes. The EXECUTESQL method, on the other hand, can be used only in passthrough mode. It returns a value that is the number of rows affected by the statement.
When you select the Go! button, two databases open: the Microsoft Access database and the ODBC data source for the AS/400. If you selected the Create Collection option, an SQL statement is issued to create the collection on the AS/400. The table definition and data are then copied with the CopyFiles function. This routine walks through each table in the database and calls the CopyTable function with the name of the table to be copied. The CopyTable function calls two other functions: CopyStructSQL to copy the definition and, if necessary, CopyData to copy the data.
The CopyStructSQL function ultimately generates the AS/400 SQL statement that is used to create the table. First, however, it checks to be sure that the table doesn't already exist in the library specified. If it does, you are asked whether to delete the existing table. If you choose to delete the table, the SQL statement DROP is issued to do that.
The CopyStructSQL function generates the SQL statement Create Table by walking through each of the fields in each table's TableDef. Each TableDef has two significant associated collections: the Fields collection and the Indexes collection. The Fields collection lists all the fields associated with the TableDef, and the Indexes collection lists all the indexes associated with it. The PrimaryKey element of the index collection holds information about the primary key for the table.
For each field in the Fields collection of the TableDef object, the GetFieldTypeSizeText function is called. This function takes the Access data type and size and translates the values into the appropriate AS/400 SQL text. The data type translations used are shown in 5. I tried to duplicate the data type translations as closely as possible, but because the AS/400 data types don't exactly match the Access data types, sometimes direct translation was not possible. You may want to change the translations made by modifying the GetFieldTypeSizeText function to get data types that more closely match those required by your organization.
For each field in the Fields collection of the TableDef object, the GetFieldTypeSizeText function is called. This function takes the Access data type and size and translates the values into the appropriate AS/400 SQL text. The data type translations used are shown in Figure 5. I tried to duplicate the data type translations as closely as possible, but because the AS/400 data types don't exactly match the Access data types, sometimes direct translation was not possible. You may want to change the translations made by modifying the GetFieldTypeSizeText function to get data types that more closely match those required by your organization.
The CopyStructSQL function creates the primary keys if the primary key option is selected. Because the SQL syntax for creating keys improved in V3R1 with the addition of the Primary Key constraint, I designed this utility to take advantage of this enhancement. This allows the utility to define databases that more closely resemble the Access database. If you select the OS/400 V3R0M05 or earlier? option, the Primary Key constraint is not available. Instead, a unique index is created over the table using the key fields of the Access table. This acts like a key, but technically it is different, because the index is a separate object and the physical file is not keyed.
If you select the option to copy the table data to the AS/400, the CopyData function is called. This function retrieves the data from the Access database in a snapshot (for more information on snapshots, see "ODBC Performance Basics," MC, August 1995). For every record in the snapshot, it generates an AS/400 Insert Into SQL statement. The value in each field is changed to an AS/400 format and appended to the statement. Generating a single Insert Into SQL statement for each record is not the most efficient way to insert data into AS/400 tables using ODBC. To improve the performance of inserting records, you may want to modify this routine to use SQL blocked inserts, which insert multiple records in a single operation.
This utility is useful in two ways: It allows you to transfer your Access databases to the AS/400, and it provides a tutorial for using VB to access the AS/400 database. Although it offers a good place to start as it is, there are many ways you can customize and improve it. One thing you might want to do is add the capability to transfer all indexes, not just the primary keys. Expand to your heart's content!
Brian Singleton is an associate technical editor for Midrange Computing. He can be reached by E-mail at
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
Transfer Access Databases to the AS/400
Getting the Code
Unfortunately, the code for this utility is too long to be printed in the magazine. We have excerpted some of the more important code into the figures shown. There are a couple of easy ways you can get the entire code listing.
One way is to access our Web site at www.as400.com. Point your browser to this location. From here, select "Midrange Computing Magazine." This will bring you to the magazine's home page. On this page is an option to "Download Published Programs." Follow this link to access any of the magazine code published on the Web site, including the code for this utility. Instructions for downloading and creating the utility are on the Web site.
Another way to get the code listing is to use our fax-back service. The phone number is 800-94FAXME (800-943-2963). Simply dial this number, listen to the instructions, and request document #8001. The code listing will be faxed back to you immediately.
Transfer Access Databases to the AS/400
Figure 1: The Database Transfer Utility
Transfer Access Databases to the AS/400
Figure 2: Partial MCDATA.FRM Form Code
Sub Copyfiles ()
'----------------------------------------------
' Duplicate the database structure and optionally
' the data
'----------------------------------------------
Dim nIdx As Integer
Dim nTblCount As Integer
nTblCount = dbSource.TableDefs.Count - 1
For nIdx = 0 To nTblCount
' Ignore Access system tables
If Mid$(UCase$(dbSource.TableDefs(nIdx).Name), 1, 4) <> "MSYS" Then
If Not CopyTable(UCase$(dbSource.TableDefs(nIdx).Name)) Then
If MsgBox("There was a problem encountered while transferring the data. Do you wish to continue with the next table?", MB_ICONQUESTION + MB_YESNO + MB_DEFBUTTON2, "Data transfer problem") = IDNO Then
Exit Sub
End If
End If
End If
Next nIdx
End Sub
Function CopyTable (sTableName As String) As Integer
' Copies a single table (structure and optionally data)
lblStatus = "Copying " & sTableName & " structure"
DoEvents
If Not CopyStructSQL(dbSource, dbDest, sTableName, sTableName, True) Then
MsgBox "Structure copy of " & sTableName & " failed.", MB_ICONEXCLAMATION, "Table Not Created"
CopyTable = False
Exit Function
End If
If chkCopyData <> 0 Then
lblStatus = "Copying " & sTableName & " data"
DoEvents
If Not CopyData(dbSource, dbDest, sTableName, sTableName) Then
CopyTable = False
MsgBox "Data copy of " & sTableName & " failed.", MB_ICONEXCLAMATION, "Data Not Copied"
Exit Function
End If
End If
CopyTable = True
End Function
Transfer Access Databases to the AS/400
Figure 3: The MCDATA.BAS Module Code
Function CopyData (from_db As Database, to_db As Database, from_nm As String, to_nm As String) As Integer
Dim nCounter As Long
Dim ssSource As Snapshot
Dim idx As Long
Dim nCount As Long
Dim nRec As Long
Dim sSQL As String
On Error GoTo CopyErr
Set ssSource = from_db.CreateSnapshot(from_nm)
ssSource.MoveLast
ssSource.MoveFirst
nRec = ssSource.RecordCount
nCount = ssSource.Fields.Count - 1
DoEvents
Do While Not ssSource.EOF
nCounter = nCounter + 1
frmMCData!lblCounter = "Copying record " & nCounter & " of " & nRec
DoEvents
sSQL = "insert into " & to_lib & gsSepChar & to_nm & " values("
For idx = 0 To nCount
If IsNull(ssSource(idx)) Then
sSQL = sSQL & " NULL,"
Else
Select Case ssSource(idx).Type
Case DB_TEXT, DB_MEMO
sSQL = sSQL & "'" & UCase$(HandleQuote((ssSource(idx)))) & "',"
Case DB_CURRENCY, DB_DOUBLE
sSQL = sSQL & ssSource(idx) & ","
Case DB_DATE
sSQL = sSQL & "'" & Format$(ssSource(idx), "yyyy-mm-dd-hh.nn.ss") & "',"
Case Else
sSQL = sSQL & ssSource(idx) & ","
End Select
End If
Next
sSQL = Mid$(sSQL, 1, Len(sSQL) - 1) & ")"
to_db.Execute sSQL, DB_SQLPASSTHROUGH
ssSource.MoveNext
Loop
frmMCData!lblCounter = ""
CopyData = True
Exit Function
CopyErr:
MsgBox to_nm & " - " & Error$
Resume Next
End Function
Function CopyStructSQL (from_db As Database, to_db As Database, from_nm As String, to_nm As String, create_ind As Integer) As Integer
On Error GoTo CSSQLErr
Dim nIdx As Integer
Dim tbl As New TableDef 'table object
Dim fld As Field 'field object
Dim ind As Index 'index object
Dim sName As String 'filename string
Dim sSQL As String
Dim nTemp As Long
Dim sPrimaryKey As String ' Holds the primary key
Dim sIndexSQL As String
sPrimaryKey = from_db.TableDefs(from_nm).Indexes("PrimaryKey").Fields
'search to see if table exists
For nIdx = 0 To to_db.TableDefs.Count - 1
If UCase(to_db.TableDefs(nIdx).Name) = UCase(to_lib & gsSepChar & to_nm) Then
If MsgBox(to_nm + " already exists, delete it?", 4) = IDYES Then
frmMCData!lblStatus = "Dropping the existing table..."
DoEvents
sSQL = "Drop table " & to_lib & gsSepChar & to_nm
nTemp = to_db.ExecuteSQL(sSQL)
Else
CopyStructSQL = False
Exit Function
End If
Exit For
End If
Next
'create the fields
sSQL = "create table " & to_lib & gsSepChar & to_nm & " ("
frmMCData.lblStatus = "Creating the fields"
DoEvents
For nIdx = 0 To from_db.TableDefs(from_nm).Fields.Count - 1
frmMCData!lblCounter = "Field " & nIdx
DoEvents
sSQL = sSQL & from_db.TableDefs(from_nm).Fields(nIdx).Name & " "
sSQL = sSQL & GetFieldTypeSizeText((from_db.TableDefs(from_nm).Fields(nIdx).Type), (from_db.TableDefs(from_nm).Fields(nIdx).Size)) & " "
' If the field is part of the key, make it not null
If InStr(UCase$(sPrimaryKey), UCase$(from_db.TableDefs(from_nm).Fields(nIdx).Name)) Then
sSQL = sSQL & " NOT NULL "
End If
sSQL = sSQL & ","
Next
frmMCData!lblCounter = ""
'frmMCData!lblStatus2 = ""
frmMCData!lblStatus = "Creating new table in database"
DoEvents
' Add the primary key
If frmMCData!chkV3R0 = 0 Then
sSQL = sSQL & SetPrimaryKeyv3r1(sPrimaryKey)
' Finish off the SQL string
sSQL = Trim$(sSQL) & ")"
nTemp = to_db.ExecuteSQL(sSQL)
Else
' Trim the trailing comma
sSQL = Mid$(Trim$(sSQL), 1, Len(Trim$(sSQL)) - 1) & ")"
nTemp = to_db.ExecuteSQL(sSQL)
' Build the index
sIndexSQL = "Create unique index " & to_lib & "." & to_nm & "1" & " on " & to_lib & "." & to_nm & " " & SetPrimaryKeyv2r3(sPrimaryKey)
nTemp = to_db.ExecuteSQL(sIndexSQL)
End If
frmMCData!lblStatus = ""
CopyStructSQL = True
Exit Function
CSSQLErr:
MsgBox Error$, MB_ICONEXCLAMATION, "Program Error"
CopyStructSQL = False
Exit Function
End Function
Transfer Access Databases to the AS/400
Figure 4: Partial VB Data Access Object Hierarchy
Transfer Access Databases to the AS/400
Figure 5: Data Types Translated by the Utility
|
