IMPLEMENTING MICROSOFT ACCESS SECURITY

• MICROSOFT ACCESS SECURITY OVERVIEW
• Creating a User Account
• Creating a Group Account
  
• Add Users to Security Groups
• Remove Users from Security Groups
• Deleting a User-Account
• Deleting a User-Group-Account
• Create or Change Security Password
• Clear Security Password
• Assign or Remove Permissions
• Assign Default Permissions
• View/Transfer Ownership of Objects
• Transfer Ownership of Database
• Set Permissions to Run/View Query
• Default Permissions for new Query
• RunPermissions Property
• Converting Database Old-Versions
• Converting Old-Version Workgroup File
• Sharing Previous Version Database
  

TIPS & TRICKS

Labels: msaccess How Tos

Displaying Path & File Info

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

Courtesy: Microsoft Access Help Documents.

Labels: msaccess How Tos

Renaming a file and displaying Drive & File Information

Sub ShowFileAccessInfo()
Dim fs, f, s

On Error GoTo ShowFileAccessInfo_Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.Getfile("C:\mytext.txt")
s = f.Name & " on Drive " & UCase(f.Drive) & vbCrLf

'renames the file named c:\mytext.txt as yourtext.txt

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

MsgBox s, 0, "File Access Info"

ShowFileAccessInfo_Exit:
Exit Sub

ShowFileAccessInfo_Err:
MsgBox Err.Description, , "ShowFileAccessInfo"
Resume ShowFileAccessInfo_Exit

End Sub

Next >> Display Path and File Info.

Labels: msaccess How Tos

HOW TO CREATE A TEXT FILE FROM MICROSOFT ACCESS

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

Syntax : Scripting.FileSystemObject

Example:

Sub CreateTextFile()
Dim fs As Object, a

On Error goto CreateTextFile_Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\mytest.txt", True)
a.writeline ("This is a test.")
a.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 (a), 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 Text File using FileSystemObject Example:

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

On Error Goto ReadTextFile_Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.opentextfile("C:\mytest.txt")
txtline = a.readline
a.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

Labels: msaccess How Tos

CREATE EXCEL OR WORD FILE FROM ACCESS

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. The first example creates a Word File and writes some text into it and saves with a name.

Public Sub CreateWordDoc()
Dim WordObj As Object

On Error goto CreateWordDoc_Err

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

Set WordObj = Nothing
CreateWordDoc_Exit:
Exit Sub

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

End Sub

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 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") _
ExcelSheet.Application.Visible = True

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

Set ExcelSheet = Nothing

CreateExcelSheet_Exit:
Exit Sub

CreateExcelSheet_Err:
Msgbox Err.Description,,"CreateExcelSheet"
Resume CreateExcelSheet_Exit

End Sub

Labels: msaccess How Tos

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 VB 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.

Labels: msaccess How Tos