December 2006 ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, December 7, 2006

Implementing Microsoft Access Security

Tips & Tricks

Displaying File Path and File Attributes

Copy and paste the following code into the Standard Module of your project. Replace the text file reference: C:\mytext.txt with one of your own files on disk.

Sub ShowFileAccessInfo2() 
Dim fs, d, f, s  
On Error Goto ShowFileAccessInfo2_Err

Set fs = CreateObject("Scripting.FileSystemObject") 
Set f = fs.Getfile("C:\mytext.txt")  
s = UCase(f.Path) & vbCrLf 
s = s & "Created: " & f.DateCreated & vbCrLf 
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf 
s = s & "Last Modified: " & f.DateLastModified & vbCrLf 
s = s & "File Size : " & f.Size & " Bytes."  

MsgBox s, 0, "File Access Info"  

ShowFileAccessInfo2_Exit: 
Exit Sub  

ShowFileAccessInfo2_Err: 
MsgBox Err.Description,,"ShowFileAccessInfo2" 
Resume ShowFileAccessInfo2_Exit  
End Sub

You may run the program directly from the Debug Window to test it.

Courtesy: Microsoft Access Help Documents.

Go to >> HOW TOs Main Page

RENAME FILE USING FILESYSTEMOBJECT

FILESYSTEM OBJECT.

Renaming a file and displaying Drive & File Information

Copy and paste the following code into a Standard Module in your Project.

Sub ShowFileAccessInfo() 
Dim fs, f, fn  
On Error GoTo ShowFileAccessInfo_Err
Set fs = CreateObject("Scripting.FileSystemObject") 
Set f = fs.Getfile("C:\mytext.txt") 
fn = f.Name & " on Drive " & UCase(f.Drive) & vbCrLf  
'renames the file named c:\mytext.txt as yourtext.txt  

f.Name = "yourtext.txt" 
fn = fn & "New Name: " & f.Name & vbCrLf 
fn = fn & "Created: " & f.DateCreated & vbCrLf 
fn = fn & "Last Accessed: " & f.DateLastAccessed & vbCrLf 
fn = fn & "Last Modified: " & f.DateLastModified  

MsgBox fn, 0, "File Access Info"  

ShowFileAccessInfo_Exit: 
Exit Sub
  
ShowFileAccessInfo_Err: 
MsgBox Err.Description, , "ShowFileAccessInfo" 
Resume ShowFileAccessInfo_Exit  
End Sub

Change the program lines wherever the sample text file reference: c:\mytext.txt is appearing in the code with one of your own text file pathname.

Next >> Display Path and File Info.

CREATE TEXT FILE FROM MSACCESS

Creating Text File from Microsoft Access

The FileSystemObject Object provides access to the computer's file system.

The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:

Syntax: Scripting.FileSystemObject

Example:

Sub CreateTextFile()
Dim fs As Object, txt
On Error goto CreateTextFile_Err
Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.CreateTextFile("C:\mytest.txt", True)
txt.writeline ("This is a test.")
txt.Close

CreateTextFile_Exit:
Exit Sub

CreateTextFile_Err:
Msgbox Err.Description,,"CreateTextFile"
Resume CreateTextFile_Exit
End Sub

In the code shown above, the CreateObject function returns the FileSystemObject (fs). The CreateTextFile method then creates the file as a TextStream object txt, and the WriteLine method writes a line of text to the created text file. The Close method flushes the buffer and closes the file.

Reading From Text File

Reading Text File using FileSystemObject Example:

Sub ReadTextFile()
Dim fs As Object, txt, txtline

On Error Goto ReadTextFile_Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.opentextfile("C:\mytest.txt")
txtline = txt.readline
txt.Close

MsgBox "C:\mytest.txt File contents : " & txtline

ReadTextFile_Exit:
Exit Sub

ReadTextFile_Err:
Msgbox Err.Description,,"ReadTextFile"
Resume ReadTextFile_Exit
End Sub

Next >> Rename File

Monday, December 4, 2006

CREATE EXCEL WORD FILE FROM ACCESS

Introduction

Create Excel File or Word Document from Microsoft Access and write information into them. Every application that supports Automation provides at least one type of object. For example, a word processing application may provide an Application object, a Document object, and a Toolbar object. To create an ActiveX object, assign the object returned by CreateObject to an object variable.

Create an MS-Word File

The first example creates a Word File and writes some text into it and saves it with a name.

Public Sub CreateWordDoc() 
Dim WordObj As Object
  
On Error goto CreateWordDoc_Err

Set WordObj = CreateObject("word.application")
With WordObj
   .Application.Visible = True
   .Application.Documents.Add "Normal", , 0, True
   .ActiveDocument.Content = "THIS IS MY TEST DOCUMENT."
   .Application.ActiveDocument.SaveAs "C:\myDocument2.doc"
   .Application.Quit
End With
Set WordObj = Nothing

CreateWordDoc_Exit:
Exit Sub

CreateWordDoc_Err:
msgbox Err.Description,,"CreateWordDoc"
Resume CreateWordDoc_Exit
End Sub

Creating an MS-Excel File

The Next example creates an Excel Worksheet and writes a line of text in Column A, Row 1, and saves it with a Name. This code starts the application by creating the object, in this case, a Microsoft Excel spreadsheet. Once an object is created, you reference it in code using the object variable you defined. You access properties and methods of the new object using the object variable, ExcelSheet, and other Microsoft Excel objects, including the Application object and the Cells collection.

Public Sub CreateExcelSheet()
Dim ExcelSheet As Object

On Error goto CreateExcelSheet_Err

Set ExcelSheet = CreateObject("Excel.Sheet")

With ExcelSheet
   .Application.Visible = True
   .Application.Cells(1, 1).Value = "This is Column A, row 1"
   .SaveAs "C:\TEST.XLS"
   .Application.Quit
End With

Set ExcelSheet = Nothing

CreateExcelSheet_Exit:
Exit Sub
CreateExcelSheet_Err:
Msgbox Err.Description,,"CreateExcelSheet"
Resume CreateExcelSheet_Exit
End Sub

Next >> Create Text File from Access.

IMPORT OBJECTS WITH VBCODE

Introduction.

Normally, Tables, Queries, or other objects from another database can be imported manually by selecting the Import option from Get External Data option from the 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.

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

Next >> Create Excel File from Access.

LEARN MS-ACCESS TIPS AND TRICKS