Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

IMPORT OBJECTS WITH VBCODE

Share a previous-version secured database across several versions of Microsoft Access.

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 that have been created with previous versions, but previous versions can only use workgroup information files that have been 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.

IMPORT OBJECTS WITH VISUAL BASIC CODE

Normally, Tables, Queries or other objects from another database can be imported manually by selecting Import from Get External Data option from File menu. But this can be achieved through VBA Code too and this question, HOW TO? is raised in Microsoft Access User's Forums and I thought it is useful to those who look for this solution. Hence, I present the Code here for importing Tables, Queries and Forms separately.

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

Public Function TableImport() 
'----------------------------------------------------------------- 
'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 

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 

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 

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 little modifications to these Codes, they can be used for transferring objects between two external databases.

Next >> Create Excel File from Access.

Share:

1 comment:

  1. Many thanks for these sleek functions.
    I used the import queries which works smoothly.

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Objects Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Class Module Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Ms-Access VBA Class Object Arrays

Last Week we had a brief introduction of a simple Custom Class Object with only two Variables, for Length and Width Values. A Method for ...

Labels

Blog Archive

Recent Posts