Adam Turner wrote:[color=blue][color=green]
>>You forgot to answer this part:
>>"Perhaps, you could post some of your best coding/programming examples[/color]
>[quoted text clipped - 3 lines][color=green]
>>Web Searches for "adam turner" program and "adam turner" code did not
>>reveal these to me. "[/color]
> Sure...here's a current project that should be up everyone's alley (it's
>somewhat lengthy):
>
>Private Sub cmdDatabase_Click()
>'Reference the following
>'*Microsoft ADO Ext. 2.1 for DDL and Security
> Dim cat As ADOX.Catalog
> Set cat = New ADOX.Catalog
> cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=C:\Documents and Settings\aturner\Desktop\Batch Tester5\
>BogusTestBatch.mdb;"
>
>End Sub
>Private Sub cmdCreateTables_Click()
> Dim MyDatabase As Database
> Dim Submission As TableDef
> Dim SubmitClient000015 As TableDef
> Dim ProductClient000015 As TableDef
> Dim sDataType As String
> Dim cn As ADODB.Connection
> Dim cmd As Command
> Dim rstFields As ADODB.Recordset
>
> Dim sFieldName, sType As String
> Dim iLength, iOfferTableID, iDisplayed As Integer
> Dim iSubmission, iSpecial, iProduct As Integer
>
> Set cn = New ADODB.Connection
> Set cmd = New ADODB.Command
>
> With cn
> .ConnectionString = "Provider=SQLOLEDB;Integrated Security=SSPI;Data
>Source=gfssvr15;Initial Catalog=GFS;"
> .CursorLocation = adUseClient
> .Mode = adModeRead
> .Open
> End With
>
> With cmd
> .CommandText = "SELECT o.FieldName, o.Type, o.Length, o.OfferTableID, o.
>Displayed, oc.ClientID " & _
> "FROM DBO.OfferDictionary o(NOLOCK) " & _
> "JOIN DBO.OfferClient oc(NOLOCK) ON o.OfferID = oc.
>OfferID " & _
> "WHERE o.OfferID = 22624 " & _
> "ORDER BY o.OfferTableID"
> .CommandType = adCmdText
> .ActiveConnection = cn
> End With
>
> Set rstFields = cmd.Execute
>
> iSubmission = rstFields.Fields(3).Value
> iSpecial = iSubmission + 1
> 'iProduct = iSubmission + 2
>
> 'open database
> Set MyDatabase = OpenDatabase(App.Path + "\BogusTestBatch.mdb")
>
> Call fGetClientTableName(rstFields.Fields(5).Value) 'Pass clientid to
>check for client level tables and return table name
>
> 'create the table
> Set Submission = MyDatabase.CreateTableDef("Submission")
> Set SubmitClient000015 = MyDatabase.CreateTableDef("SubmitClient000015")
> Set ProductClient000015 = MyDatabase.CreateTableDef("ProductClient000015")
>
> On Error Resume Next
> 'delete the table if it already exists
> MyDatabase.TableDefs.Delete Submission.Name
> MyDatabase.TableDefs.Delete SubmitClient000015.Name
> MyDatabase.TableDefs.Delete ProductClient000015.Name
>
> Do While Not rstFields.EOF
> sFieldName = rstFields.Fields(0).Value 'Fields(0).Value =
>Fieldname
> sType = rstFields.Fields(1).Value 'Fields(1).Value = Type
> iLength = rstFields.Fields(2).Value 'Fields(2).Value = Length
> iOfferTableID = rstFields.Fields(3).Value 'Fields(3).Value =
>OfferTableID
> iDisplayed = rstFields.Fields(4).Value 'Fields(4).Value =
>Displayed
>
> Select Case iOfferTableID 'add fields to the tables
> Case iSubmission 'ORDER BY OfferTableID in SELECT statment is the
>key
> With Submission
> If sFieldName = "RecordID" Then
> .Fields.Append .CreateField("RecordID", PrimaryKey,
>10)
> Else
> .Fields.Append .CreateField(sFieldName,
>fConvertDatatypes(sType), iLength)
> End If
> End With
> 'pAddRecords (iDisplayed)
> Case iSpecial
> With SubmitClient000015
> .Fields.Append .CreateField(sFieldName, fConvertDatatypes
>(sType), iLength)
> End With
> 'pAddRecords (iDisplayed)
> Case Else 'there's not always a product table
> With ProductClient000015
> .Fields.Append .CreateField(sFieldName, fConvertDatatypes
>(sType), iLength)
> End With
> 'pAddRecords (iDisplayed)
> End Select
>
> 'NewTable.Indexes.Refresh
> MyDatabase.TableDefs.Append Submission
> MyDatabase.TableDefs.Append SubmitClient000015
> MyDatabase.TableDefs.Append ProductClient000015
>
> rstFields.MoveNext
> Loop
>
> 'Debug.Print rstFields.RecordCount
>
> MyDatabase.Close 'close database
> Set MyDatabase = Nothing
>
> cn.Close 'close connection
> Set cn = Nothing
>
> 'Call MsgBox("Tables Created Successfully", vbOKOnly)
>
>End Sub
>Private Sub pAddRecords(iDisplayed As Boolean)
> 'Displayed needs populated
> 'must get retailer from offerdictionary
> 'get sku or model if available
>End Sub
>Private Function fConvertDatatypes(sDataType As String)
> Select Case sDataType
> Case "Text"
> fConvertDatatypes = vbString
> Case "Int", "TinyInt", "Integer"
> fConvertDatatypes = vbInteger
> Case "Date"
> fConvertDatatypes = vbDate
> Case "Currency"
> fConvertDatatypes = vbCurrency
> Case "Byte"
> fConvertDatatypes = vbByte
> Case "Long"
> fConvertDatatypes = vbLong
> End Select
>End Function
>Private Function fGetClientTableName(clientid As Integer)
> MsgBox (clientid)
>End Function[/color]
Ofcourse this code is incomplete and would be a lot easier to read in the VB6
with the pretty highlighted keywords...but it basically grabs selected tables
from the SQL Server and auto generates an .mdb file with 3 new tables.
Why? Honestly, I've been a programmer for the #1 fulfillment company in the
US (Global Fulfillment Services) for the past year and it's taken me that
long to learn their in-house server/table structure. It is severely intricate
and insanely brilliant. The entire infrastructure is a work of art. (No
thanks to me. I just use it to write stand alone apps.)
If anyone has any questions on this code, feel free to question or criticize.
I'm always open for "constructive" critcizm.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1 
