Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 27th, 2005, 08:46 AM
Friend of Wrox
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default Constructing Create Table Statement

I am trying generate Create Table Statement and write it to
a text file. So far i managed to display the columns names and types .But i want to add definition of the primary key and alternate keys as well. This is the part that i need an expert to help me .Finally i want write that generated create table statement to text file. Thanks

my code is below:
Private Sub FieldTypeButton_Click()
''' type the table name inside " "
''' calls the module
Call ShowFields("Customers")

End Sub

Public Sub ShowFields(pTable As String)

Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim NL As String
Dim strHold As String
Dim x As Variant

NL = Chr(13) & Chr(10) ' Define newline.

Set db = CurrentDb

Set rs = db.OpenRecordset(pTable)

n = rs.Fields.Count
ReDim x(n - 1, 1) As String
For i = 0 To (n - 1)
x(i, 0) = rs.Fields(i).Name
j = rs.Fields(i).Type
x(i, 1) = Choose(j, "Boolean", "Byte", "Integer", "Long", "Currency", _
"Single", "Double", "Date", "Binary", "Text")

Next i
Set db = Nothing
For i = 0 To (n - 1)
'here the feild name and field type is generated
strHold = strHold & x(i, 0) & "-" & x(i, 1) & IIf(i < (n - 1), NL, "")
Next i
MsgBox strHold, vbOKOnly, "Fields in table " & pTable

End Sub

Old April 27th, 2005, 02:35 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

In place of NL, use the built in VB constant vbCrLf. It is exactly the same thing.

Un-indented code is really hard to read . . .

Your ReDim would be clearer as
    ReDim x(0 To n - 1, 0 To 1) As String
The best way to generate text files (in my opinion) is to add a reference in your project to the Windows Scripting host, then create an instance of a FileSystemObject that that refernce provides. Then create a text-stream object, and use the obj.WriteLine method to write lines into the file. (Or use the .Write method, which does not generate a new line automatically.)

I believe that to find the primary key and so on, you will need to look through (I believe) the indexes collection of the database, armed with your table name.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create generic XSL Template to create table Venkatachalapathy XSLT 5 March 11th, 2008 07:49 AM
constructing a HTML table from xml data using xslt rameshnarayan XSLT 0 September 19th, 2005 06:53 AM
Create table statement syntax error in access 2000 method Access 1 August 8th, 2005 02:10 AM
Help needed in constructing create table statement method Access VBA 0 April 28th, 2005 11:04 AM
Problem in ALTER TABLE statement deepakkhopade SQL Server 2000 4 November 1st, 2004 07:51 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.