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 28th, 2005, 11:04 AM
Friend of Wrox
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help needed in constructing create table statement

Well i am tring to construct create table statement for tables in my access db. I need an expert show me how i can
force ShowFields module to return field and feild type value and place them in starBase line before i writing that statement to text file:

strBase = "CREATE TABLE [" & Me![ComboBox] & "] ????

The program workes like this. I created a command button and combo box inside a form. I select a table from combo box and press the button to generate the table statement for that table.I would be happy if some expert show me how to complete this task.Thanks


Private Sub CreateTableButton_Click()

  Dim fso As New Scripting.FileSystemObject
  Dim io As Scripting.TextStream

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field
  '''Declreaing our variables
  Dim strBase As String

  Dim strFile As String
  Dim strName As String

  Set db = CurrentDb()

  Set rst = db.OpenRecordset(Me![ComboBox])

'' This is a call for module that prints the feild name and type but i want some how it brings it here and put it in
'' the next line of code ???????????
Call ShowFields("Customers")

'' IN the next line i want to put the field names and type before i insert it to a text file

'construct sql statement and add to file text:????????????????????????????????????????????? ???
strBase = "CREATE TABLE [" & Me![ComboBox] & "] ===============> CREATE Table Statement???
  strName = "c:\" & Me!ComboBox & " Data.sql"

'write the statements to a file:
  If Len(strFile) > 0 Then
    Set io = fso.CreateTextFile(strName)
    io.Write strFile
  End If

  On Error Resume Next
  Set io = Nothing
  Set fso = Nothing
  Set rst = Nothing
  Set db = Nothing
  Exit Sub
  Debug.Print Err, Err.Description
  Resume ExitHere

End Sub

''This module displays field name and type in a massage box
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

Similar Threads
Thread Thread Starter Forum Replies Last Post
complicated if else statement help needed cmt9000 Classic ASP Basics 1 December 13th, 2006 06:34 PM
Nested IIF Statement Help Needed Corey Access 4 November 17th, 2005 10:19 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
Constructing Create Table Statement method Access VBA 1 April 27th, 2005 02:35 PM

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