How to add a field to a table.
Q: How can I add a field to a table? I'd like to make this text field required and not allow empty strings.
A: It's easiest to use SQL to add a field to an existing table, but this technique won't address zero-length strings, while DAO can. First, an example of the DAO method (set a reference to the DAO library):
Public Sub addFieldToTbl()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Set db = CurrentDb()
Set tbl = db.TableDefs("tblDepartments")
Set fld = tbl.CreateField("Organization", dbText, 30)
tbl.Fields.Append fld
fld.Properties("Required").Value = True
fld.Properties("AllowZeroLength").Value = False
CleanUp:
Set fld = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in addFieldToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
Next, here's an example of executing a SQL statement in a VBA procedure to add a text column to the table:
Public Sub addColToTbl()
On Error GoTo ErrHandler
CurrentDb().Execute "ALTER TABLE tblDepartments " & _
"ADD COLUMN Organization Text (30) NOT NULL;", _
dbFailOnError
Exit Sub
ErrHandler:
MsgBox "Error in addColToTbl( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
As you can see, there's no way to indicate that zero-length strings aren't allowed within the SQL statement.
Top
How to assign a primary key to a table, a foreign key to another table and establish a relationship between these two tables.
Q: I have two tables, tblPersonnel and tblDepartments. How can I assign a primary key to DeptID in the tblDepartments table and a foreign key on the field of the same name in the tblPersonnel table, then create a relationship between these two tables?
A: When one creates the foreign key constraint, one creates the relationship between the two tables, so there's no additional code needed to create an official relationship. To create the primary key on DeptID, set a Reference to the DAO library and use the following procedure:
Public Sub createPKIndex()
On Error GoTo ErrHandler
Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Set db = CurrentDb()
Set tbl = db.TableDefs("tblDepartments")
Set idx = tbl.createIndex("PrimaryKey")
Set fld = idx.CreateField("DeptID")
idx.Fields.Append fld
idx.Primary = True
tbl.Indexes.Append idx
CleanUp:
Set fld = Nothing
Set idx = Nothing
Set tbl = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in createPKIndex( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
To set the foreign key constraint (i.e., the relationship), use the following procedure:
Public Sub createFKIndex()
On Error GoTo ErrHandler
CurrentDb().Execute "ALTER TABLE tblPersonnel " & _
"ADD CONSTRAINT Personnel_Depts_FK " & _
"FOREIGN KEY (DeptID) " & _
"REFERENCES tblDepartments;", dbFailOnError
Exit Sub
ErrHandler:
MsgBox "Error in createFKIndex( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
The foreign key constraint, Personnel_Depts_FK, is the name of the relationship that is created when this code executes. Since DeptID is a field in both tables, it doesn't need to be used in the syntax for the foreign key constraint for the tblDepartments table. However, if the tblPersonnel table had a differently named field for the foreign key than the actual primary key in the tblDepartments table, then it would be required in the syntax. For example, if the tblPersonnel table had the foreign key field named DID, then the above syntax would need to be changed to the following:
CurrentDb().Execute "ALTER TABLE tblPersonnel " & _
"ADD CONSTRAINT Personnel_Depts_FK " & _
"FOREIGN KEY (DID) " & _
"REFERENCES tblDepartments (DeptID);", dbFailOnError
Top
How to back up files from one directory to another.
Q: How can I backup up my Excel spreadsheet files in one directory to another?
A: One may backup files by indicating an extension or by indicating "ALL" in the following procedures:
Public Sub backupFiles()
On Error GoTo ErrHandler
Dim sSrcDir As String
Dim sTargetDir As String
Dim sExt As String
Dim sMsg As String
Dim fSuccess As Boolean
sSrcDir = "C:\Data\"
sTargetDir = "F:\Backup\"
sExt = "xls"
'sExt = "all" ' Use this to copy all files to the
' target directory.
fSuccess = copyFiles(sSrcDir, sTargetDir, sExt)
If (Not (fSuccess)) Then
sMsg = "not "
End If
MsgBox "All indicated files were " & sMsg & "copied to the" & _
vbCrLf & sTargetDir & " directory.", vbInformation + vbOKOnly, _
"File Copy Status"
Exit Sub
ErrHandler:
MsgBox "Error in backupFiles( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Public Function copyFiles(sSourceDir As String, sTargetDir As String, _
sFileExt As String) As Boolean
On Error GoTo ErrHandler
Dim fso As New FileSystemObject
Dim fl As File
Dim fls As Files
Dim Fldr As Scripting.Folder
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fldr = fso.GetFolder(sSourceDir)
Set fls = Fldr.Files
For Each fl In fls
If ((Right$(fl.Name, 4) = "." & sFileExt) Or _
(UCase$(sFileExt) = "ALL")) Then
If (Dir(sTargetDir & fl.Name) = "") Then
FileCopy sSourceDir & fl.Name, sTargetDir & fl.Name
End If
End If
Next
copyFiles = True
CleanUp:
Set fls = Nothing
Set Fldr = Nothing
Set fso = Nothing
Exit Function
ErrHandler:
MsgBox "Error in copyFiles( )." & vbCrLf & vbCrLf & _
"Error #" & _
Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Function
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
