HOW TO: Use Sample Automation Code to Transfer Tables to a Secured Access 2000 MDB File (319481)

SUMMARY

In some programs, such as Microsoft Project, you can transfer data to a secured Microsoft Access database if you want, but you would have to go through many operations to do so. This article demonstrates how to use automation code to transfer tables from a non-secured Access database to a secured Access database without having to manually join the workgroup and to start Microsoft Access.

back to the top

Steps and Sample Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. This demonstration uses Microsoft Word to make the Automation request.

1. In Microsoft Word, create a module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

2. The sample code in this article uses both the Microsoft Data Access Objects (DAO) and Microsoft Access 9.0 Object Libraries. For this code to run properly, you must reference both the Microsoft Data Access Objects and Microsoft Access 9.0 Object Libraries. To do so, click References on the Tools menu, and make sure that both the Microsoft DAO 3.6 Object Library and Microsoft Access 9.0 Object Library check boxes are selected.
3. Type the following procedure:

   Sub TransTables()
   
      Dim db As DAO.Database
      Dim dbs AS String
      Dim Wk As String
      ' Dims the array. If you have more than 100 tables, you must 
      ' increase this array.
      Dim TblList(100) 
      Dim tblname
      Dim i
      Dim x
      Dim accObj As New Access.Application
      Dim AccPath As String
      Dim myuser As String, psWord As String
      Dim tblList2
      Dim myapp as String
      
      On Error GOTO ErrorTrap
      ' Substitute the correct path and file name for your unsecured database.
      Set db = OpenDatabase("c:\db1.mdb") 
   
      For i = 0 To db.TableDefs.Count - 1 Step 1
          TblList(i) = db.TableDefs(i).Name
      Next i ' This loop places the table names into your array.
      db.Close
   
      ' You may have to correct this path to point to your MSACCESS executable
      ' file.
      myapp = "C:\program files\Microsoft Office\Office10\MSACCESS.EXE"
   
      ' Use the path and name of a secured MDB on your system.
      dbs = "C:\SecuredData.mdb"
      ' This is the secured workgroup file.
      WK = "C:\MySecured.mdw"
      myuser = "test"           ' Use a valid username
      psWord = "test"           ' and a correct password.
   
      
       x = Shell(myapp & " " & dbs & " /user " & myuser & _
       " /pwd " & psWord & " /wrkgrp " & WK, vbMinimizedNoFocus)
   
      DoEvents
   
      Set accObj = GetObject(, "Access.Application")
   
      For tblList2 = LBound(TblList) To UBound(TblList) - 1
      
         tblname = TblList(tblList2)
         If Left(tblname, 4) <> "MSys" And tblname <> Empty Then
          accObj.DoCmd.TransferDatabase acImport, "Microsoft Access", _
            "C:\db1.mdb", acTable, tblname, tblname
         End If
      Next
      MsgBox "tables imported"
      accObj.CloseCurrentDatabase
      accObj.Quit
   
      Set accObj = Nothing
      x = ""
      Exit Sub
   
      ErrorTrap:
        Msgbox "Transfer not completed"
        ' If you get this error the typical areas to search are the paths
        ' supplied to the variable names and the User name and password.
   
   End Sub
   					

4. To test this function, type the following line in the Immediate window, and then press ENTER:

   TransTables
   						

   NOTE: After the code has finished running, the table owners are assigned to the user name that is supplied by this sample code.

back to the top