Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MICROSOFT ACCESS HOW TOS

Share:

DISPLAY PATH AND FILE INFO

Displaying Path & File Info

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 file 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

Share:

RENAME FILE USING FILESYSTEMOBJECT

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, 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 

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.

Share:

CREATE TEXT FILE FROM MSACCESS

HOW TO CREATE A TEXT FILE FROM MICROSOFT ACCESS

The FileSystemObject Object provides access to a 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, 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

Share:

CREATE EXCEL WORD FILE FROM ACCESS

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 

Next >> Create Text File from Access.

Share:

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:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

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 Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts