By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

How to get query of created table

P: 60
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
Mar 20 '07 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCreateNewTable_Click()
  2. Dim db As DAO.Database
  3. Dim tbl As DAO.TableDef
  4. Dim fld As Field
  5.  
  6.    ' set to the current database
  7.    Set db = CurrentDB
  8.  
  9.    ' Create a new TableDef object.
  10.    Set tbl = db.CreateTableDef("tableName")
  11. Set fld = tbl.CreateField("MyStringField", dbText, 75)
  12. tbl.Fields.Append fld
  13. Set fld = tbl.CreateField("MyNumberField", dbDouble)
  14. tbl.Fields.Append fld
  15. Set fld = tbl.CreateField("MyDateTimeField", dbDate)
  16. tbl.Fields.Append fld
  17.  
  18. ' Add the new table to the database.
  19.    db.TableDefs.Append tbl
  20.    Set db = Nothing
  21.  
  22. End Sub
  23.  
Mar 22 '07 #2

P: 60
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
Mar 22 '07 #3

Denburt
Expert 100+
P: 1,356
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.
Mar 22 '07 #4

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

can u give more explanation
Mar 23 '07 #5

Denburt
Expert 100+
P: 1,356
Buttons sorry, on the left hand side of the Database Window they have the buttons, tables, queries, Forms, etc.
Mar 23 '07 #6

P: 60
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
Mar 26 '07 #7

Denburt
Expert 100+
P: 1,356
CREATE TABLE MyTable (ProductName Text, ProductDate Date)
Mar 26 '07 #8

P: 60
thanks for replay

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

thank u
Mar 27 '07 #9

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Dim tdf, fld
  2. Dim dbs, strFldNme
  3. Set dbs = CurrentDb
  4. For Each tdf In dbs.TableDefs
  5. If Left(tdf.Name, 4) <> "Msys" Then
  6. ReDim myArray(tdf.Fields.Count)
  7.     For Each fld In tdf.Fields
  8.         strFldNme = strFldNme & fld.Name & " " & [b]fld.Type[b] & ","
  9.     Next
  10. strFldNme = Left(strFldNme, Len(strFldNme) - 1)
  11. Debug.Print "CREATE TABLE " & tdf.Name & "(" & strFldNme & ")"
  12.  
  13. End If
  14. Next
  15.  
Mar 27 '07 #10

P: 60
thank for replay

i shall try that.

thanks in advance
Mar 28 '07 #11

Denburt
Expert 100+
P: 1,356
Your quite welcome, let us know how you do.
Mar 29 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.