Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 28th, 2005, 11:04 AM
Friend of Wrox
Join Date: Mar 2005
Location: , , .
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

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 05:51 PM.

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