Import Objects With Vbcode

Import Objects with VBA code.

Normally, you can manually import tables, queries, or other objects from another database by choosing File > Get External Data > Import in Microsoft Access. However, this task can also be automated using VBA code. The question of "How to do this using code?" often comes up in Microsoft Access user forums. I believe this solution will be helpful to those seeking a programmatic approach. Therefore, I’m sharing the code examples here for importing tables, queries, and forms separately.

Importing All Tables.

The next method imports all Tables from a Source database into the active database, except the Microsoft Access System Tables.

Public Function Table Import() 
'----------------------------------------------------------------- 
'Function to Import Microsoft Access Tables from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'----------------------------------------------------------------- 
Dim wrkSpace As Workspace, db As Database, tbldef 
Dim strFile As String 
Dim ObjFilter As String  
'if conflict with existing object name then ignore 
' and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0)  
'Check for Table Definitions in the Source database 
'and import all of them except System Tables.  
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
For Each tbldef In db.TableDefs 
strFile = tbldef.Name  
'Filter out Microsoft Access System Tables. 
ObjFilter = left(strFile, 4) 
If ObjFilter <> "MSys" Then   
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acTable, strFile, strFile, False
End If  
Next  
End Function 

Importing All Queries.

Next Function Imports all the Queries from the Source database into the current database.

Public Function QueryImport() 
'------------------------------------------------------------------ 
'Function to Import Microsoft Access Queries from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'------------------------------------------------------------------ 
Dim wrkSpace As Workspace, db As Database, QryDef 
Dim strFile As String  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0) 
'Check for Query Definitions in the Source database 
'and import all of them. 
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
For Each QryDef In db.QueryDefs
 strFile = QryDef.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acQuery, strFile, strFile, False 
Next  
End Function 

Importing All Forms.

The ImportForms() Function Imports all the Forms from an external Microsoft Access database into the current Database.

Public Function ImportForms() 
'---------------------------------------------------------------- 
'Function to Import Microsoft Access Forms from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'---------------------------------------------------------------- 
Dim FRM As Variant, wrkSpace As Workspace 
Dim db As Database, strForm As String 
Dim ctr As Container  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0) 
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
Set ctr = db.Containers("Forms") 
For Each FRM In ctr.Documents 
strForm = FRM.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acForm, strForm, strForm, False 
Next  
End Function 

Exporting All Forms

The ExportForms() Function Exports all the Forms into an external Microsoft Access database.

Public Function ExportForms() 
'---------------------------------------------------------------- 
'Function to Export Microsoft Access Forms into another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'---------------------------------------------------------------- 
Dim cdb As Database 
Dim ctr As Container, doc, strFile As String  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
'Export all Forms from the current database into  
'the Target database 
Set cdb = CurrentDb 
Set ctr = cdb.Containers("Forms")  
For Each doc In ctr.Documents 
strFile = doc.Name 
  DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\tmp\Targetdb.mdb", acForm, strFile, strFile, False 
Next  
End Function 

With a few modifications to this code, they can be used for transferring objects between two external databases.

Next >> Create Excel File from Access.

Read More

Share Previous Version Database

Share Previous-version Secured Database.

With one exception, the issues involved when sharing a secured database across more than one version of Microsoft Access are the same as the issues for sharing an unsecured database across more than one version.

The one exception concerns how to handle the workgroup information file that is used with the secured database. You have two choices:

1. Tell users who will be upgrading to Microsoft Access 2000 to join the appropriate workgroup information file with the oldest version of Microsoft Access that will be sharing the secured database.

   Microsoft Access 2000 can use workgroup information files created with previous versions, but previous versions can only use workgroup information files created with Microsoft Access 2000 or a previous version.

   Important: If users will be sharing a secured database from Microsoft Access 95 or 97, you should compact the current workgroup information file with Microsoft Access 2000 before using it. Compacting the file by using Microsoft Access 2000 does not change the file format, so the file can continue to be used by any Microsoft Access 95 or 97 users who are not upgrading.

2. If the shared database is Microsoft Access version 2.0, convert the workgroup information file that will be used with the secured database and then tell only users who are upgrading to Microsoft Access 2000 to join the converted workgroup information file. All users who are not upgrading from version 2.0 must continue to use the workgroup information file produced with that version.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Convert Old Version Workgroup File

Convert Workgroup File to New Version

To take advantage of security and performance improvements, you should re-create the Workgroup Information File as described below:

1. Create a new workgroup information file, making sure to enter the exact, case-sensitive name, company name, and workgroup ID that was used to create the original file. Failure to re-enter the exact entries that were used to create the original file will create an invalid Admins group.
2. Re-create any group accounts, making sure to enter the exact, case-sensitive user name and PID for each user.
3. Tell other Microsoft Access 2000 users in the workgroup to use the workgroup Administrator to join the new workgroup information file.

   Click Next to see how to Share a previous-version secured database across several versions of Microsoft Access.

   Goto Main

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Convert MS Access Old Versions

Convert Microsoft Access 95/97 Secured Database.

When upgrading from Microsoft Access 95 or 97, you need to convert your secured database, but you don't need to convert the Workgroup Information File to use it with Microsoft Access 2000. However, you should compact the Workgroup Information File before using it.

1. Convert the secured database.
2. Compact the secured database.
3. Exit Microsoft Access.
4. Before compacting the Workgroup Information File (typically named 'System.mdw') that was used with the secured database, temporarily join another Workgroup Information File.
5. Start Microsoft Access without opening a database.
6. Compact the Workgroup Information File that was used with the secured database.
7. Tell users to join the compacted Workgroup Information File before opening the secured database.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Run Permissions Property

Run Permissions Property

You can use the 'RunPermissions' property in a multi-user environment with a secure workgroup to override the existing user permissions. This allows you to view a query or run an append, delete, make-table, or update query. For example, as a user, you have read-only permission for queries, while the owner of the queries has read/write permissions. If the owner sets the 'RunPermissions' property to specify the owner's permissions, you can run an append query to add records to a table.

Setting:

The 'RunPermissions' property uses the following settings:

1. Setting Description
2. Owner's: All users are allowed the owner's permissions to view or run the query.
3. User's (Default): Users have only their own permissions to view or run the query.

You can set this property by using the query's property sheet.

You can also set the RunPermissions property in the SQL view of the Query window by using the WITH OWNERACCESS OPTION declaration in the SQL statement.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Default Permissions For New Query

Change Default Permissions

You can change the default permissions that allow others to view data returned from queries or, in the case of action queries, to run the queries, even if they are otherwise restricted from viewing the underlying table or query.

Changing the default permissions affects only new queries.

1. On the Tools menu, click Options
2. Click the Table/Queries tab.
3. Click the Run Permissions option you want to use.

If you select Owner: all users have the query owner's permission to view or run the query.

Only the query owner can save changes to the query.

Only the query owner can change the ownership of the query.

If you select Users, the permissions defined for that classification of users are in effect instead, and any user with Administer permissions for the query can save changes to the query or change its ownership.

Click Next to see how to set the Run Permissions Property of Queries.

Goto Main

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

Earlier Post Link References:

-->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Permit To View Run Query

Permit View/Run Query.

In a secure workgroup, you can assign others permission to view the data your query returns, or in the case of an action query, to run the query, even if they are otherwise restricted from viewing the query's underlying table or query.

1. Open the query in Design view. Select the query by clicking anywhere in the Query Design view outside the design grid and the field lists.
2. Click Properties on the toolbar to display the query's property sheet.
3. Set the Run Permissions property to Owner's.

The following are true for this setting:

• All users have the query owner's permission to view or run the query.
• Only the query owner can save changes to the query.
• Only the query owner can change the ownership of the query.

Note: You can also set default permissions for all new queries. Click Options on Tools menu. Click the Tables/Queries tab, and then click the Run Permissions option you want to use.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Transfer Ownership Of Database

Transfer Ownership of Database.

Start Microsoft Access by using a secure workgroup that contains the user account that you want to own the database and its objects.

1. Log in by using that account.
2. Create a new blank database.
3. Import all the objects from the database that have the ownership you want to change into the new database.

Note: To import a database, you must have Open/Run permission for the database and Read Design permission for its objects. To import Tables, you must also have Read Data permission. If you have permissions for some tables, queries, forms, reports, and macros but not others, Microsoft Access imports only those objects for which you have permissions.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

View Or Transfer Ownership

Transfer Ownership of Objects

If you have the Administer permission for a Table, Query, Form, Report, or Macro, you can change the ownership of the object to another user or group.

1. Open the database.
2. On the Tools menu, click Security, and then click User and Group Permissions.
3. On the Change Owner tab, Microsoft Access displays a list of all tables, queries, forms, reports, and macros currently shown in the Database window, along with the current owner of each object.
4. Click an object type in the Object Type box, or use the existing object type.
5. From the Object list, click one or more objects with ownership that you want to change. To select more than one object, either hold down CTRL and click the objects or drag through the ones you want to select.
6. In the New Owner box, click the user or group account that you want to be the new owner of the object or objects.
7. Click the Change Owner button.

Note: If you change ownership of a table, query, form, report, or macro to a group account, all users who belong to the group automatically receive the permissions associated with ownership of the object.

<-- fb="" for="" h3="" images="" posts="" without="">Earlier Post Link References: -->

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Assign Default Permissions

Assign Default Permissions for Objects.

1. Open the database that contains the Tables, Queries, Forms, Reports, and Macros.
2. On the Tools menu, click Security and then click User and Group Permissions.
3. On the Permissions tab, click Users or Groups, and then click the user or group that has the permissions you want to assign in the User/Group Name box.
4. Click the type of object in the Object Type box and click in the Object Name list.

   The selection varies depending on the type of object you've selected.

5. Select the default permissions that you want to assign for that object type and then click Apply.
6. Repeat steps 4 and 5 to assign default permissions for additional object types for the current user or group.
7. Repeat steps 3 through 5 for any additional users or groups, and then click OK to save.

Default permissions can be assigned only by an administrator account (a member of the Admins group in the workgroup where the database was created) or by the database owner. Some permissions automatically include others. For example, the Modify Data permission for a table also grants Read Data and Read Design, since these are required to modify data. Similarly, Modify Design and Read Data both imply Read Design. For macros, Read Design also implies Open/Run.

Organizing user accounts into groups makes it easier to manage security. For example, rather than assigning permissions to each user for each object in your database, you can assign permissions to a few groups, and then add users to the appropriate group. When users log on to Microsoft Access, they inherit the permissions of any group to which they belong.

Click Next to see how to view or transfer ownership of objects.

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More