This is for those of you that use MS-Access often and would like to simplify some of your code development.
For years, I used to use SQL Server Management Studio (SSMS) to spit out the SQL to create a Table and then I would do some Search and Replace on the code provided by SSMS to take something like this:
And create this:
I knew I should automate the process, but it was one of those things I kept putting off till later. I finally got around to creating a basic version of this about a year ago and it wasn’t pretty but it worked and I’ve been using it a while now. I’ve had a few spare hours this week, so I packaged it up all by itself and here it is. Please let me know if there are bugs or things that need improvement.
What’s inside? …not much. There is one Form and a couple code modules. A Screen shot of the Form is below and the top three fields (and two checkboxes) are used to generate the code in the bottom Field.
[IMGnothumb]http://bytes.com/attachments/attachment/8251d1425688389/accesscodegener ator.jpg[/IMGnothumb]
The magic happens in this function:
Looking forward to your feedback.
For years, I used to use SQL Server Management Studio (SSMS) to spit out the SQL to create a Table and then I would do some Search and Replace on the code provided by SSMS to take something like this:
Code:
CREATE TABLE [dbo].[Attachment]( [AttachmentID] [int] IDENTITY(1,1) NOT NULL, [Attachment] [nvarchar](255) NOT NULL, [Type] [nvarchar](50) NOT NULL, [Category] [nvarchar](50) NULL, [Supplier] [nvarchar](50) NULL, [Reference] [nvarchar](50) NULL, [Description] [nvarchar](255) NULL, [Suggested] [bit] NULL, [Internal] [bit] NULL, [CreateDate] [datetime] NULL, [LastUpdate] [datetime] NULL, [LastUpdateBy] [nvarchar](50) NULL, CONSTRAINT [Attachment$PrimaryKey] PRIMARY KEY CLUSTERED ( [AttachmentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Code:
sSQL = sSQL & "", [Attachment].[AttachmentID]"" sSQL = sSQL & "", [Attachment].[Attachment]"" sSQL = sSQL & "", [Attachment].[Type]"" sSQL = sSQL & "", [Attachment].[Category]"" sSQL = sSQL & "", [Attachment].[Supplier]"" sSQL = sSQL & "", [Attachment].[Reference]"" sSQL = sSQL & "", [Attachment].[Description]"" sSQL = sSQL & "", [Attachment].[Suggested]"" sSQL = sSQL & "", [Attachment].[Internal]"" sSQL = sSQL & "", [Attachment].[CreateDate]"" sSQL = sSQL & "", [Attachment].[LastUpdate]"" sSQL = sSQL & "", [Attachment].[LastUpdateBy]""
What’s inside? …not much. There is one Form and a couple code modules. A Screen shot of the Form is below and the top three fields (and two checkboxes) are used to generate the code in the bottom Field.
[IMGnothumb]http://bytes.com/attachments/attachment/8251d1425688389/accesscodegener ator.jpg[/IMGnothumb]
The magic happens in this function:
Code:
Public Function reportTableColumns(ByRef sDatabaseName As String, ByRef sTableName As String, ByRef sFormat As String, ByRef bIncludeType As Boolean, ByRef bCRLF As Boolean)
' Gets a list of Table Column Names and Formats per request
Dim oWrk As Workspace
Dim oDB As Database
Dim oTD As DAO.TableDef
Dim oField As DAO.Field
Dim sOutput As String
If sDatabaseName = "" Then
Set oDB = dbLocal
Else
If fileExists(sDatabaseName) Then
Set oWrk = DBEngine.Workspaces(0)
Set oDB = oWrk.OpenDatabase(sDatabaseName)
End If
End If
If Not oDB Is Nothing Then
For Each oTD In oDB.TableDefs
If oTD.Name = sTableName Then
For Each oField In oTD.Fields
sOutput = sOutput & Replace(Replace(sFormat, "{0}", oField.Name), "{T}", sTableName)
If bIncludeType Then sOutput = sOutput & ";" & oField.Type
If bCRLF Then sOutput = sOutput & vbCrLf
Next oField
'Exit For
End If
Next oTD
End If
reportTableColumns = sOutput
End Function