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 email address is being protected from spambots. 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
MC Press books written by Brian Singleton available now on the MC Press Bookstore.
 |
||
 |
|
i5/OS and Microsoft Office Integration Handbook Harness the power of Microsoft Office while exploiting the iSeries database. List Price $79.95 Now On Sale
|
Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. 
LATEST COMMENTS
MC Press Online