How to create a desktop shortcut to open a database with a command line parameter.
Q: I need to create a Windows shortcut to open my database, but I also need it to use the /Runtime parameter for the command line. I see many VB examples, but they all simply copy an existing shortcut. They don't create a "new" shortcut file. Can it be done?
A: Yes. One may use the Windows scripting shell and the environment variables to determine the location of the file and the executable. Paste the following two functions into a public module:
Public Function createDBShortcut()
On Error GoTo ErrHandler
Dim sEXEPath As String
Dim sShortcut As String
Dim sDBPath As String
Dim sParams As String
sEXEPath = "Microsoft Office\OFFICE11\MSACCESS.EXE"
sShortcut = "DBName Shortcut"
sDBPath = "D:\MyDB.mdb" ' Don't need double quotes around
' path for illegal chars.
sParams = " /runtime" ' MUST have double quotes around
' paths w/illegal chars.
Call createDesktopShortcutWParams(sEXEPath, sShortcut, sDBPath, sParams)
Exit Function
ErrHandler:
MsgBox "Error in callCreateDesktopShortcut( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function
Public Function createDesktopShortcutWParams(sExecutablePath As String, _
sShortcutName As String, sFilePath As String, sParams As String)
On Error GoTo ErrHandler
Dim WShell As Object
Dim WShortcut As Object
Dim sDir As String
Dim sTarget As String
Set WShell = CreateObject("WScript.Shell")
sDir = WShell.SpecialFolders("Desktop")
Set WShortcut = WShell.CreateShortcut(sDir & "\" & sShortcutName & ".lnk")
WShortcut.WorkingDirectory = WShell.ExpandEnvironmentStrings("%ProgramFiles%")
sTarget = WShell.ExpandEnvironmentStrings("%ProgramFiles%\") & sExecutablePath
WShortcut.TargetPath = sTarget
WShortcut.Arguments = " """ & sFilePath & """" & sParams
WShortcut.IconLocation = sTarget & ", 0"
WShortcut.WindowStyle = 4
WShortcut.Save
CleanUp:
Set WShortcut = Nothing
Set WShell = Nothing
Exit Function
ErrHandler:
MsgBox "Error in createDesktopShortcutWParams( )." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Function
Top
How to create a listing of the Access and Jet errors.
Q: Is there a place where I can read all of the Access errors and their descriptions?
A: Yes. One can create a table filled with many of the Access and Jet error numbers and their descriptions. The following code is copied from the Access 97 Help topic (with disambiguation for the DAO library for later versions of Access, but one still needs to set a Reference to DAO 3.6 to use this function in Access 2K and later):
The following procedure creates a table containing many of the error codes and strings used or reserved by Microsoft Access and by the Microsoft Jet database engine. Not all error codes are included in the resulting table, as some exist outside the range of error codes evaluated by this procedure (0 to 3500).
Public Function AccessAndJetErrorsTable( ) As Boolean
Dim dbs As Database, tdf As TableDef, fld As DAO.Field
Dim rst As DAO.Recordset, lngCode As Long
Dim strAccessErr As String
Const conAppObjectError = "Application-defined or object-defined error"
On Error GoTo Error_AccessAndJetErrorsTable
' Create Errors table with ErrorNumber and ErrorDescription fields.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("AccessAndJetErrors")
Set fld = tdf.CreateField("ErrorCode", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("AccessAndJetErrors")
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then
' Skip codes that generate application or
' object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to
' Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString.AppendChunk strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
MsgBox "Access and Jet errors table created."
AccessAndJetErrorsTable = True
Exit_AccessAndJetErrorsTable:
Exit Function
Error_AccessAndJetErrorsTable:
MsgBox Err & ": " & Err.Description
AccessAndJetErrorsTable = False
Resume Exit_AccessAndJetErrorsTable
End Function
Top
How to delete a field from a table in VBA.
Q: How can I delete a field from a table using VBA code?
A: To delete a field, use the following code:
Public Sub testDropCol()
MsgBox "Successfully dropped column = " & dropCol("tblStuff", "trash")
End Sub
Public Function dropCol(sTableName As String, sCol As String) As Boolean
On Error GoTo ErrHandler
CurrentDb().Execute "ALTER TABLE " & sTableName & _
" DROP " & sCol, dbFailOnError
dropCol = True
Exit Function
ErrHandler:
'--------------------------------------------------------------
' Determine whether item not found in this collection.
'--------------------------------------------------------------
If ((Err.Number <> 3265) And (Err.Number <> 3381)) Then
MsgBox "Error in dropCol( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
dropCol = False
End Function
In this example, the table name is tblStuff and the field to be deleted is Trash. Of course, if this field is part of an index or part of a relationship (constraint) between two tables, then this code won't work. The index or relationship (constraint) must be dropped before the field can be dropped.
Top
How to delete a file in VBA.
Q: How can I delete a file in VBA?
A: To delete a file, one would think the command would be something simple, such as "Delete." No. It's more violent in VBA. Use the following syntax:
Kill "C:\Data\DeleteThis.txt"
Top
How to delete a table in VBA.
Q: How do I delete a table in VBA?
A: Use the following procedure:
Public Sub dropTbl(sTableName As String)
On Error GoTo ErrHandler
CurrentDb().Execute "DROP TABLE " & sTableName & ";", _
dbFailOnError
Exit Sub
ErrHandler:
MsgBox "Error in dropTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Top
How to determine a file's size.
Q: How can I find out the size of a file?
A: Use the following procedure in a standard module:
Public Function getFileSize(sFilePath As String, Optional sSize As String) As Long
On Error GoTo ErrHandler
Dim nByteSize As Currency
Dim nFileSize As Currency
Const KILO As Long = 1024
nByteSize = FileLen(sFilePath)
If (UCase$(sSize) = "M") Then
nFileSize = nByteSize / KILO / KILO
ElseIf (UCase$(sSize) = "K") Then
nFileSize = nByteSize / KILO
Else
nFileSize = nByteSize
End If
getFileSize = nFileSize
Exit Function
ErrHandler:
MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function
If no argument is passed for the sSize parameter, then the size returned will be in bytes. If "M" is used for this parameter, then the size returned will be in MB. If "K" is used for this parameter, then the size returned will be in KB.
Top
How to determine the default file format.
Q: How can I find out the current database's default file format in VBA?
A: Use the following procedure in a standard module:
Public Function getDefaultFileFormat() As Long
On Error GoTo ErrHandler
getDefaultFileFormat = GetOption("Default File Format")
Exit Function
ErrHandler:
MsgBox "Error in getDefaultFileFormat( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function
The function will return 8 for Access 97 file format, 9 for Access 2000 file format, or 10 for Access 2002 - 2003 file format.
Copyright © 2004 - 2007 Q-Built Solutions. All rights reserved.
Top
|
