Microsoft KB Archive/319397

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

Article ID: 319397

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q319397



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

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

For a Microsoft Access 2000 version of this article, see 319481.

IN THIS TASK
SUMMARY
 * Steps and Sample Code

REFERENCES



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

