Microsoft KB Archive/319481

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

Article ID: 319481

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q319481



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK
SUMMARY
 * Steps and Sample Code

REFERENCES



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.   In Microsoft Word, create a module, and then type the following line in the Declarations section if it is not already there: Option Explicit  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.  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(&quot;c:\db1.mdb&quot;)

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 = &quot;C:\program files\Microsoft Office\Office10\MSACCESS.EXE&quot;

' Use the path and name of a secured MDB on your system. dbs = &quot;C:\SecuredData.mdb&quot; ' This is the secured workgroup file. WK = &quot;C:\MySecured.mdw&quot; myuser = &quot;test&quot;          ' Use a valid username psWord = &quot;test&quot;          ' and a correct password.

x = Shell(myapp & &quot; &quot; & dbs & &quot; /user &quot; & myuser & _   &quot; /pwd &quot; & psWord & &quot; /wrkgrp &quot; & WK, vbMinimizedNoFocus)

DoEvents

Set accObj = GetObject(, &quot;Access.Application&quot;)

For tblList2 = LBound(TblList) To UBound(TblList) - 1 tblname = TblList(tblList2) If Left(tblname, 4) <> &quot;MSys&quot; And tblname <> Empty Then accObj.DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, _ &quot;C:\db1.mdb&quot;, acTable, tblname, tblname End If  Next MsgBox &quot;tables imported&quot; accObj.CloseCurrentDatabase accObj.Quit

Set accObj = Nothing x = &quot;&quot; Exit Sub

ErrorTrap: Msgbox &quot;Transfer not completed&quot; ' 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   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

