Results 1 to 4 of 4

Thread: database script

  1. #1
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15

    Unanswered: database script

    Hi all

    Is there any way to generate database script. so that i can use the script to create database on different machine.

    Actually i want to upgrade my previous database on different machine with the current database changes (table structure change, new tables, queries etc.). Also i have to retain the old data.

    I'm using Access 2000.
    Any pointers to this will be helpful.

    Thanks in Advance
    -Venky

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I list all changed in a table, then I run through a recordset of that data and loop through the following code to make backend table changes:

    Select Case rs!AddEdit
    Case "Add"
    If IsNull(rs!FieldName) Then 'Add the table by copying from this FixIt database
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    'Error 3265 - table does not exist
    On Error Resume Next
    Set tdf = dbs.TableDefs(rs!tablename)
    If Err = 3265 Then
    DoCmd.SetWarnings 0
    DoCmd.CopyObject DataDir & rs!dbName, rs!tablename, _
    acTable, rs!tablename
    DoCmd.SetWarnings -1
    End If
    Err = 0
    Else
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    'Error 3265 - table does not exist
    On Error Resume Next
    Set tdf = dbs.TableDefs(rs!tablename)
    If Err <> 3265 Then
    Set fld = tdf.CreateField
    fld.Name = rs!FieldName
    fld.Type = rs!fieldType
    If IsNull(rs!FieldSize) = 0 Then fld.Size = rs!FieldSize
    If IsNull(rs!Attributes) = 0 Then fld.Attributes = rs!Attributes
    If IsNull(rs!IsRequired) = 0 Then fld.Required = rs!IsRequired
    If rs!fieldType = 10 Then
    If IsNull(rs!defaulttext) = 0 Then fld.DefaultValue = rs!defaulttext
    End If
    If IsNull(rs!DefaultValue) = 0 Then
    If rs!fieldType = 8 And rs!DefaultValue = 1 Then
    fld.DefaultValue = "Date()"
    Else
    fld.DefaultValue = rs!DefaultValue
    End If
    End If
    'On Error Resume Next 'Keep going if it already exists
    'Error 3262 - Couldn't lock table; currently in use by Admin
    'Error 3191 - Can't define field more than once
    'Error 3367 - Can't append. An object with that name already exists in the collection.
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    End If
    Err = 0
    End If
    Case "Copy" 'copy data from one field to another
    'set fld = tdf.
    '3078 - table does not exist
    On Error Resume Next
    Set rs2 = dbs.OpenRecordset(rs!tablename)
    If Err <> 3078 Then
    While Not rs2.EOF
    If Nz(rs2(rs!FieldName), "") <> "" Then
    rs2.Edit
    rs2(rs!NewName) = rs2(rs!FieldName)
    rs2.Update
    End If
    rs2.MoveNext
    Wend
    End If
    Err = 0
    Case "Del"
    'Cannot delete a field programatically
    Case "ren" 'rename a field
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    On Error Resume Next
    Set tdf = dbs.TableDefs(rs!tablename)
    If Err <> 3265 Then
    'Look for fld
    'On Error GoTo SkipIt
    Set fld = tdf.Fields(rs!FieldName)
    On Error Resume Next
    If IsNull(rs!NewName) = 0 Then fld.Name = rs!NewName
    If rs!fieldType = 10 Then
    If IsNull(rs!defaulttext) = 0 Then fld.DefaultValue = rs!defaulttext
    Else
    If IsNull(rs!DefaultValue) = 0 Then fld.DefaultValue = rs!DefaultValue
    End If
    'If IsNull(rs!NewType) = 0 Then fld.Type = 9 'rs!NewType ' *** cannot do programmatically
    'If IsNull(rs!NewSize) = 0 Then fld.Size = 4 'rs!NewSize ' *** cannot do programmatically
    'If IsNull(rs!Attributes) = 0 Then fld.Attributes = rs!Attributes
    tdf.Fields.Refresh
    End If
    Err = 0
    Case "Upd" 'set default and update all records which are null to that default
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    If rs!fieldType = 10 Then
    If IsNull(rs!defaulttext) = 0 Then stDef = rs!defaulttext
    stCmd = "UPDATE " & rs!tablename & " SET " & rs!FieldName & " = '" & stDef & "' WHERE isnull(" & rs!FieldName & ") = -1;"
    End If
    If IsNull(rs!DefaultValue) = 0 Then
    If rs!fieldType = 8 And rs!DefaultValue = 1 Then
    stDef = "Date()"
    Else
    stDef = rs!DefaultValue
    End If
    stCmd = "UPDATE " & rs!tablename & " SET " & rs!FieldName & " = " & stDef & " WHERE isnull(" & rs!FieldName & ") = -1;"
    End If
    Set qdf = dbs.CreateQueryDef("", stCmd)
    qdf.Execute
    Case "Rel" 'Relationship
    'For relationships, use the TCAddField columns as below:
    ' Code = unique relationship name
    ' dbName = the name of the database as per usual
    ' TableName = name of the primary table
    ' FieldName = name of the field in the primary table
    ' ForeignTable = name of the foreign table
    ' ForeignField = name of the field in the foreign table
    ' RelAttr = number for relationship attributes
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    'Set relNew = dbs.CreateRelation(rs!Code, _
    rs!tablename, rs!NewName, dbRelationUpdateCascade)
    'dbRelationUnique 1 The relationship is one-to-one.
    'dbRelationDontEnforce 2 The relationship isn't enforced (no referential integrity).
    'dbRelationInherited 4 The relationship exists in a non-current database that contains the two linked tables.
    'dbRelationUpdateCascade 256 Updates will cascade.
    'dbRelationDeleteCascade 4096 Deletions will cascade.

    ' Find existing EmployeesOrders relation.
    On Error Resume Next
    For Each rel In dbs.Relations
    If rel.Table = rs!tablename And rel.ForeignTable = rs!ForeignTable Then
    'Error 3262 - Couldn't lock table...; currently in use by user 'Admin' on machine...
    dbs.Relations.Delete rel.Name
    End If
    Next rel

    Set rel = dbs.CreateRelation(rs!Code)
    rel.Table = rs!tablename ' Set Table property.
    rel.ForeignTable = rs!ForeignTable ' Set ForeignTable property.
    ' Set Relation object attributes to enforce referential integrity.
    If rs!RelAttr = 4352 Then
    rel.Attributes = dbRelationDeleteCascade And dbRelationUpdateCascade
    Else
    rel.Attributes = rs!RelAttr
    End If
    Set fld = rel.CreateField(rs!FieldName) ' Create field in Fields collection of Relation.
    fld.ForeignName = rs!ForeignField ' Provide name of foreign key field.
    rel.Fields.Append fld ' Append field to Relation and Relation to database.
    dbs.Relations.Append rel
    Case "PKey" 'Create a primary key for up to 2 fields using an index
    Set dbs = DBEngine.Workspaces(0).OpenDatabase(DataDir & rs!dbName)
    On Error Resume Next
    Set tdf = dbs.TableDefs(rs!tablename)
    If Err <> 3265 Then
    Set idx = tdf.CreateIndex(rs!tablename)
    Set fld = idx.CreateField(rs!FieldName)
    idx.Fields.Append fld
    If Nz(rs!MakeIndex, 0) Then idx.Unique = True
    idx.Primary = True
    If IsNull(rs!NewName) = 0 Then 'Second part of compound key
    Set fld = idx.CreateField(rs!NewName) 'Second part of the Primary Key
    idx.Fields.Append fld
    If Nz(rs!IsRequired, 0) Then idx.Unique = True
    End If
    '3283 Primary key already exists - I cannot delete it programmatically and must be set up manually
    tdf.Indexes.Append idx
    End If
    Case "Qry"
    'Make sure the tables are linked and run the query
    If Relinked = 0 Then
    'Relinked = Relink(DataDir & "main.mdb")
    End If
    DoCmd.SetWarnings 0
    DoCmd.OpenQuery rs!tablename
    DoCmd.SetWarnings -1
    End Select
    If Err = 3262 Then
    SetCompleted = 0
    Err = 0
    ElseIf Err = 3191 Then
    'SetCompleted = 0
    Err = 0
    ElseIf Err = 3283 Then 'primary key already exists
    Err = 0
    ElseIf Err = 0 Then
    SetCompleted = -1
    Else
    SetCompleted = 0
    Err = 0
    End If
    If SetCompleted Then
    rs.Edit
    rs!Completed = -1
    rs.Update
    End If

  3. #3
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    Thanx...it should work for me.

    Access code---i'm not familiar with this

    Anyway, based on ur code i feel the structure of the table changes is
    AddEdit - char[20] - Add, copy, del, ren, upd
    dbName - char[50]
    tablename - char[50]
    FieldName - char[50]
    NewName - - char[50]
    fieldType - char[50]
    NewType - char[50]
    FieldSize - char[50]
    NewSize - char[50]
    Attributes - char[50]
    IsRequired - boolean
    defaulttext - char[50]
    DefaultValue - char[50]
    ForeignTable - char[50]
    Code - char[50]
    RelAttr - char[50]
    ForeignField - char[50]
    MakeIndex - boolean

    U r filling the appropriate fields based on Add, copy, del, ren, upd. Then open this recordset (rs) and do the actions accordingly.

    Can u tell me what is the definitions for dbs and rs. I tried using
    Dim dbs As database
    Dim rs As Recordset

    But doesn't work...Looks like i need to add some reference to the project!!!

    -Venky

  4. #4
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    OK...i got it.

    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset

    Now...Can i run this code on the client machine where there is no Access software installed. Making this as a .mde file will work?

    Thanx
    -Venky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •