How to get query of created table

[joerozario]

i have created many tables in access for my project.

now i want the (create Tabel) query for all the table.

how to generate?. in sql server this option is available, i could not find in access

help me

thank u

[MMcCarthy]

You cannot create a table in Access using SQL. This is done using VBA Code and is quite complicated. The table name and field names will have to be altered each time you use it. This code is designed for use on a command button but can be adapted.

Private Sub cmdCreateNewTable_Click()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As Field

   ' set to the current database
   Set db = CurrentDB

   ' Create a new TableDef object.
   Set tbl = db.CreateTableDef("tableName")
Set fld = tbl.CreateField("MyStringField", dbText, 75)
tbl.Fields.Append fld
Set fld = tbl.CreateField("MyNumberField", dbDouble)
tbl.Fields.Append fld
Set fld = tbl.CreateField("MyDateTimeField", dbDate)
tbl.Fields.Append fld

' Add the new table to the database.
   db.TableDefs.Append tbl
   Set db = Nothing

End Sub

[joerozario]

i have tables allredy but now i want the query for the the created table.

in sql server we have options like right click on the tabel -> generate sql ->
it will give a sql file with query.

so if i want to creata tables in some other data base . just i can run the query

like this . this is my need

thanks for replay

[Denburt]

I don't think we understand the question... In the MS Access database window you can click on the query tab then click new and you will see a wizard that pops up and offers a variety of choices depending on what you want. I think what you are looking for is the simple query wizard. If you are looking to create the same tables in another database simply create a new DB and drag them right on over.

[joerozario]

i donot get u r point about (query tab) in ms access

can u give more explanation

[Denburt]

Buttons sorry, on the left hand side of the Database Window they have the buttons, tables, queries, Forms, etc.

[joerozario]

yes we have but we can't get

"Create Table tablename (

feild datatype length,
)"

like this query for the created table

thank for replay

thank u

[Denburt]

CREATE TABLE MyTable (ProductName Text, ProductDate Date)

[joerozario]

thanks for replay

yes this query has to be generated by access for created table

thank u

[Denburt]

The create table method in MS Access will not like the following (fld.Type) in the below code, you will need to setup a select statement or something to determine what number signifies what each field type is. Hope this steers you in the right direction.

Dim tdf, fld
Dim dbs, strFldNme
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "Msys" Then
ReDim myArray(tdf.Fields.Count)
    For Each fld In tdf.Fields
        strFldNme = strFldNme & fld.Name & " " & [b]fld.Type[b] & ","
    Next
strFldNme = Left(strFldNme, Len(strFldNme) - 1)
Debug.Print "CREATE TABLE " & tdf.Name & "(" & strFldNme & ")"

End If
Next

[joerozario]

thank for replay

i shall try that.

thanks in advance

[Denburt]

Your quite welcome, let us know how you do.