Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

It is possible to do a great deal with Access through VBScript.

Example: Delete existing relationship and create a new one

  Option Explicit
  Dim strPath
   
  'Current path
  strPath = Left(Wscript.ScriptFullName,Len(Wscript.ScriptFullName)-Len(Wscript.ScriptName))
   
  'Call sub with path and DB name
  Call ChangeRelation(strPath & "LTD.mdb")
   
  Sub ChangeRelation(strDB)
  Dim fs, ac, db, rel
  Dim strDbTemp
  Dim strRelName, strTable, strForeignTable, strField
   
  'You need the value for built-in constants
  Const dbRelationDontEnforce=2
   
      'Scripting object for manipulating files.
      Set fs=CreateObject("Scripting.FileSystemObject")
   
      If Not fs.FileExists(strDB) then
          'No file
          Exit Sub
      Else
          'Don't mess with an open MDB
          strDbTemp = Left(strDb, Len(strDb) - 3) & "ldb"
   
          If fs.FileExists(strdbtemp) = True Then 
              Exit Sub
          End If
      End If
      
      
      'Back-up the db before messing, don't overwrite (False)
      fs.CopyFile strDB, strPath & "BACKUP" & "LTD.mdb", False
   
      'Open Access Application
      Set ac=CreateObject("Access.Application")
      
      'Open password protected database
      Set db = ac.DBEngine.OpenDatabase(strDb, False, False, ";PWD=Pass")
      
      'You will need:
      'Name of the relationship to change
      strRelName="tblLTDtblDoc"
      'Name of the table
      strTable="tblLTD"
      'Name of the foreign table
      strForeignTable="tblDoc"
      'Name of the ID/Key field that will be used.
      strField="LTDKey"
      
      'First delete the existing relationship ...
      db.Relations.Delete strRelName
   
      'Then create the new relationship ...
      Set rel = db.CreateRelation(strRelName, strTable, strForeignTable, dbRelationDontEnforce)
   
      'Add the field ...
      rel.Fields.Append rel.CreateField(strField)
      rel.Fields(strField).ForeignName = strField
   
      'And, finally, append to the relationships collection.
      db.Relations.Append rel
   
      If (rel.Attributes And dbRelationDontEnforce) Then
          msgBox "Success!"
      Else
          msgBox "Fail!"
      End If
  End Sub