HOW TO: Use Sample Automation Code to Transfer Tables to a Secured Access 2002 .mdb File (319397)

SUMMARY

Some programs, such as Microsoft Project, may have difficulty saving data to a secured Microsoft Access database. The purpose of this article is to demonstrate automation code that can be used to transfer tables from a nonsecured Access database to a secured Access database without having to manually join the workgroup, and then start 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.
For the purpose of this demonstration, Microsoft Word is used as the program that demonstrates how to make the Automation request.

NOTE: The sample code in this article uses the following references:

• Microsoft DAO 3.6 Object Library
• Microsoft Access 10.0 Object Library

For this code to run properly, you must reference both libraries. To do this, click References on the Tools menu in the Visual Basic Editor, and then make sure that both libraries have the check box selected.

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

   Option Explicit
   					

2. Type the following procedure:

   Sub TransTables()
   
      Dim db As DAO.Database
      Dim dbs AS String
      Dim Wk As String
      Dim TblList(100) 'Dims the array if you have more then 100 tables. 
                       'You must increase this array.
      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
      Set db = OpenDatabase("c:\db1.mdb") 'Substitute the correct path and file
                                          'name for your not secure database.
   
      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
   
      ' Correct this path to correctly point to your Access 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 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
   					

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

   TransTables
   						

   NOTE: As soon as the code has run, the table owners are assigned to the user name that is supplied by this sample code.

back to the top