473,320 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Really Basic Access Code Generator

jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[Attachment](
  2.     [AttachmentID] [int] IDENTITY(1,1) NOT NULL,
  3.     [Attachment] [nvarchar](255) NOT NULL,
  4.     [Type] [nvarchar](50) NOT NULL,
  5.     [Category] [nvarchar](50) NULL,
  6.     [Supplier] [nvarchar](50) NULL,
  7.     [Reference] [nvarchar](50) NULL,
  8.     [Description] [nvarchar](255) NULL,
  9.     [Suggested] [bit] NULL,
  10.     [Internal] [bit] NULL,
  11.     [CreateDate] [datetime] NULL,
  12.     [LastUpdate] [datetime] NULL,
  13.     [LastUpdateBy] [nvarchar](50) NULL,
  14. CONSTRAINT [Attachment$PrimaryKey] PRIMARY KEY CLUSTERED 
  15. (
  16.     [AttachmentID] ASC
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  18. ) ON [PRIMARY]
And create this:
Expand|Select|Wrap|Line Numbers
  1. sSQL = sSQL & "", [Attachment].[AttachmentID]""
  2. sSQL = sSQL & "", [Attachment].[Attachment]""
  3. sSQL = sSQL & "", [Attachment].[Type]""
  4. sSQL = sSQL & "", [Attachment].[Category]""
  5. sSQL = sSQL & "", [Attachment].[Supplier]""
  6. sSQL = sSQL & "", [Attachment].[Reference]""
  7. sSQL = sSQL & "", [Attachment].[Description]""
  8. sSQL = sSQL & "", [Attachment].[Suggested]""
  9. sSQL = sSQL & "", [Attachment].[Internal]""
  10. sSQL = sSQL & "", [Attachment].[CreateDate]""
  11. sSQL = sSQL & "", [Attachment].[LastUpdate]""
  12. sSQL = sSQL & "", [Attachment].[LastUpdateBy]""
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.



The magic happens in this function:
Expand|Select|Wrap|Line Numbers
  1. Public Function reportTableColumns(ByRef sDatabaseName As String, ByRef sTableName As String, ByRef sFormat As String, ByRef bIncludeType As Boolean, ByRef bCRLF As Boolean)
  2.  
  3.     ' Gets a list of Table Column Names and Formats per request
  4.     Dim oWrk As Workspace
  5.     Dim oDB As Database
  6.     Dim oTD As DAO.TableDef
  7.     Dim oField As DAO.Field
  8.     Dim sOutput As String
  9.  
  10.     If sDatabaseName = "" Then
  11.         Set oDB = dbLocal
  12.     Else
  13.         If fileExists(sDatabaseName) Then
  14.             Set oWrk = DBEngine.Workspaces(0)
  15.             Set oDB = oWrk.OpenDatabase(sDatabaseName)
  16.         End If
  17.     End If
  18.  
  19.     If Not oDB Is Nothing Then
  20.         For Each oTD In oDB.TableDefs
  21.             If oTD.Name = sTableName Then
  22.                 For Each oField In oTD.Fields
  23.                     sOutput = sOutput & Replace(Replace(sFormat, "{0}", oField.Name), "{T}", sTableName)
  24.                     If bIncludeType Then sOutput = sOutput & ";" & oField.Type
  25.                     If bCRLF Then sOutput = sOutput & vbCrLf
  26.                 Next oField
  27.                 'Exit For
  28.             End If
  29.         Next oTD
  30.     End If
  31.     reportTableColumns = sOutput
  32.  
  33. End Function
  34.  
Looking forward to your feedback.
Attached Images
File Type: jpg AccessCodeGenerator.jpg (96.9 KB, 321 views)
Attached Files
File Type: zip AccessCodeGenerator.zip (48.0 KB, 55 views)
Mar 7 '15 #1
0 1253

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Dado | last post by:
Do you know where to find some peace of software/code which I can implemented in my desktop java application which will protect it from illegal using? I imagine something like: buyer can install...
3
by: Neal D. Becker | last post by:
I am converting optimization code from legacy C to python. Generators are a HUGE convenience, because the original code structures have the optimizer as the main code calling your function, while...
2
by: Vitaliy | last post by:
Hello there, I have written a code generator for our internal use. Right now it is implemented as an independent application. The generated code (which is really a block of code containing 4-5...
0
by: Mike | last post by:
Our Product is 100% free, we are not trying to sell anything here. MyGeneration is a .NET Code Generator that allows you to create templates in either VBScript or JScript, it also serves up all...
4
by: Me | last post by:
Checkout http://www.nAlliance.com for a Visual Basic .Net & T-SQL code generator. Save lots of time, and never worry about using a DataAdapter again.
0
by: Alicia Marinache | last post by:
Hello I do have a sudden problem with my Visual Studio (.NET Framework 1.1, Visual Studio 2003 version). All was fine and well until 2 days ago when unexpectedly I started to receive the following...
0
by: genc ymeri | last post by:
Hi over there, I'm building a webservice (C#/>Net 1.1) and I'm trying to add a web reference , a WSDL file from a location "c:\inetpub\WSDLfile.wsdl" . (This is the WSDL file of another webserver...
1
by: Sehboo | last post by:
I have a database with several tables. I need to create pages, and most of the pages would talk to only one table. I am wondering if there is any wizard/code generator which I can use to...
1
by: wildman | last post by:
It's been over 10 years that I don't look at Access. I've been working in asp.net for the last 5 developing intranet sites. Just got a project where we are trying to avoid building an...
2
by: vasavivenu | last post by:
HI, When i am going to make Exe file my visual basic application shows "unexpected error occurred in code generator or linker" this error how can overcome this one. Thanks in Advance,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.