(c) 2002 Visual Studio Magazine
Fawcette Technical Publications

Issue: January 2002
Section: Database Design
Author: Andy Clark

VB6	Use ADOX to Generate a Table
Listing 1	This code copies a table definition using the ADOX objects. This creates a new table and copies each column in the original table's Columns collection into the new table. The new column definitions are identical to the original table, other than limiting field sizes to 65,536 to work around an Access limitation.

Private Sub GenTable(pTbl As ADOX.Table)
Dim colOrig As ADOX.Column
Dim colNew As ADOX.Column
Dim tblNew As New ADOX.Table

tblNew.Name = pTbl.Name
For Each colOrig In pTbl.Columns
	Set colNew = New ADOX.Column
	colNew.Name = colOrig.Name
	colNew.Attributes = colOrig.Attributes
	colNew.DefinedSize = _
		colOrig.DefinedSize
	If (colNew.DefinedSize > 65535) Then _
		colNew.DefinedSize = 65535
	colNew.NumericScale = _
		colOrig.NumericScale
	colNew.Precision = colOrig.Precision
	colNew.Type = colOrig.Type
	tblNew.Columns.Append colNew
Next colOrig

mcatJet.Tables.Append tblNew
Exit Sub

End Sub

VB6, SQL Server 7/2000	Build the Insert Statement
Listing 2	This code builds an Insert statement from an ADO recordset. StrInsert starts with INSERT and is filled with the field names. StrValue receives the values from the fields themselves. At the end, the code combines and executes the two strings.

With rs
	Do While Not (.EOF)
		strInsert = "INSERT INTO [" & _
			pstrTableName & "] ("
		strValues = vbCrLf & " VALUES ("

		For Each fld In .Fields
			Select Case fld.Type

				Case adDBTimeStamp
					If Not(IsNull(fld.Value)) Then
						strInsert = strInsert & _
							fld.Name & ", "
							strValues = strValues & _
								"'" & fld.Value & "', "
					End If
...
			End Select
...
	strInsert = strInsert & strValues
	mconJet.Execute strInsert
	.MoveNext
	Loop
	mconJet.Close
End With
