|
Subject:
|
Constructing Create Table Statement
|
|
Posted By:
|
method
|
Post Date:
|
4/27/2005 8:46:09 AM
|
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
rs.Close
db.Close
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
|
|
Reply By:
|
BrianWren
|
Reply Date:
|
4/27/2005 2:35:45 PM
|
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.
|
|
