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

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 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.