?
Solved

Need Table/Query Script Generator

Posted on 2003-02-24
25
Medium Priority
?
339 Views
Last Modified: 2012-06-27
Is there an Access equivalent to the SQL Server "Generate SQL Script" task?

I need to create a text file (or text output to window) that contains the code to recreate an Access table.  The user should be able to select the table (or all tables) from the Access DB.

Of course, then I need a way to "execute" this script to recreate the table(s) from the text file.

So, the successful answer to this question will provide (1) instructions for using the Access built-in wizard/task, or (2) third-party code, or (3) your custom code to:

1.  Select one or more tables/queries from an Access DB and write the script to a text file/window that defines the table(s)/queries and can be used to create the table(s) in another DB.

2.     Run the script from #1 and create the table(s)/Queries in another DB.

Please keep this as simple as possible.  A useful option would be to just output the table/query definition in a nice, readable format suitable for posting in a web forum.  I would like to be able to give this to a remote user (who may be a novice programmer) to use and send the script to me so that I can provide better tech support/debugging.

0
Comment
Question by:apollois
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +3
25 Comments
 
LVL 58
ID: 8011949
There is no built-in function other then the copy object command, but that's MDB to MDB.

 Are you looking for just the field definitions or everything (indexes, relationships, etc).

What are your thoughts on handling version differences?

 And will this always be Access to Access?

Jim.
0
 
LVL 10

Author Comment

by:apollois
ID: 8012039
JDettman,

>>> Are you looking for just the field definitions or everything (indexes, relationships, etc).<<<

Everything would be nice (selectable options like SQL Server). :)  But I can live with just fields and indexes.

>>>What are your thoughts on handling version differences?<<<

I'm only interested in Access 2000 and later.  Are there any DDL diferences here?

>>> And will this always be Access to Access?<<<

Yes.


Best Regards,
apollois
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8012236
Hi apollois,

Sorry, access can't deliver this.
I would advise you to look into Q:
http://www.experts-exchange.com/Databases/MS_Access/Q_11282837.html?query=Designer+nico5038&searchType=topic

As products like S-Designer will do "re-engineering" on tables allowing you to "transfer" the DDL definitions to other database formats.

Nic;o)
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 10

Author Comment

by:apollois
ID: 8012312
Hi Nico,

What about using a SQL data-definition query?

Couldn't you write code to read the table structure and output as a SQL data-definition query?

Best Regards,
apollois
0
 
LVL 58
ID: 8012410
Apollois,

<<Couldn't you write code to read the table structure and output as a SQL data-definition query?>>

  Yes you can.  You can also use DAO or ADOX to read the structures, write to some type of format (text or XML), then write utilities to bring all that back.  There are a bunch of ways to do this.  That's why I asked some of the questions I did.

  However, it's a lot of work, some of which already exists in various products (Erwin, Rational Rose, FMS Codebook, etc).  Erwin and Raitional Rose offer what you want, but they cost big $$.

  FMS has code/utilites that come close, but don't quite hit the mark.  Save for database advisors Back End Updater (you create a script of changes that should be applied to a Back End and it executes them).

  I think for tech support, your probably better off to provide some means of getting the actual MDB.  Many times, a problem can only be reproduced with the data.  A copy of the structure alone won't do it.

  It also sounds like from your question that you want the whole thing written for you.   Your talking tens of hours at work most likely.  Something which I doubt anyone here is going to be willing to undertake.

Jim.

0
 
LVL 54

Expert Comment

by:nico5038
ID: 8012479
Hi appollois,

Yes I can, but why the effort when you can get an off the shelf product ?

I do have a function to extract the tables and fields.
Adding the type won't be the work, but you'll also need  indexes and foreign keys. There's where the trouble starts. Finally the cascading relations for update and delete.
Guess it will take me several days to develop and test.

A product like S-Designer will do this and also creates a graphical relationship diagram. Onc imported the result can be moved to many other DBMS formats.

When there are not too many tables involved you could do with the table/field extract function and use that data with Word to do some find/replace and manual updates to get the job done.
Will be faster as developing a 100% function to do it all.

Otherwise the purchase of such a re-engineering tool is really the best option I know.

Hope you're not too disappointed.

Nic;o)
0
 
LVL 10

Author Comment

by:apollois
ID: 8012547
Nico and Jim,

Well, I was hoping that either Access had this built-in, or someone already had the code, or something close.

If you think this is too much work for this question, what about just a simple listing of the table structure?

I'm looking for something much, much simpler than the commercial products like S-Designer.  Since Access can't do it built-in, would like to have code the user could download and run in the Access VBA module.  Would an add-in make sense?

Actually, I would think that this is something that you guys could make good use of here in the Access TA.

I guess I should have been clearer about my intended use.  We could use this in the ASP TA.  So many questions that would be much easier/quicker to answer if only the asker would post the table structure.

Maybe we could collaborate on it.  I know SQL and ADO, but not that familiar with the internals of Access.

Best Regards,
apollois
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8012660
As stated in the Q of my link, you can use the Tools/Analyze/Documenter to show the table definition with indexes, etc.
I just didn't like the format, thus I created a function like:

Function CreateTableField()
'Function to create a table/field table
Dim db As DAO.Database
Dim td As DAO.TableDefs
Dim tdx As DAO.TableDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Dim intI As Integer
Dim strTable As String
Dim prp As Property

On Error GoTo err_CreateTableField

Set db = CurrentDb
Set td = db.TableDefs

' Create table. First drop existing table
'db.Execute "DROP TABLE tblTableNico5038;"

db.Execute "CREATE TABLE tblTableNico5038 (TableName TEXT, FieldName TEXT, FieldDescription TEXT);"

Set rs = db.OpenRecordset("tblTableNico5038")

For Each tdx In td
      strTable = tdx.Name
  For intI = 0 To tdx.Fields.Count - 1
      rs.AddNew
      rs!TableName = strTable
      rs!FieldName = tdx.Fields(intI).Name
      rs!FieldDescription = tdx.Fields(intI).Properties("Description")
      rs.Update
  Next intI
Next
Exit Function

err_CreateTableField:
Select Case Err
Case 3270
    Resume Next
Case 3376
    Resume Next
Case Else
    MsgBox Err.Number & " " & Err.Description
End Select
End Function

Now you'll be able to manipulate the data with queries and additionally you could add the field type, etc.

Nic;o)
0
 
LVL 10

Author Comment

by:apollois
ID: 8012788
How do you test to determine if a table already exists?

Can you have ADO and DAO set as ref at the same time?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8013239
Re:

I need to create a text file (or text output to window) that contains the code to recreate an Access table


I like the option of opening a recordset based on the chosen table then
persisting the recordset as xml.

Then Open the xml.recordset which has all the table properties defined
compliments of  ADO and create a table based on the xml.recordset.


Dim rsUser As New ADODB.Recordset
Dim sqlUser As String
'Build a recordset that returns no records    
sqlUser = "SELECT  * FROM tbl_YourTable where tbl_YourTable.CusID = -999"

'STEP 1 create rsUser and persist as User.xml
    rsUser.CursorLocation = adUseClient
    rsUser.Open sqlUser, cn, adOpenStatic, adLockBatchOptimistic, adCmdText
    Set rsUser.ActiveConnection = Nothing
    Dim sUserXml As String
    sUserXml = fso.BuildPath("C:\temp\", "User.xml")
   
    With rsUser
      .Save sUserXml, adPersistXML
      .Close
    End With
'Step 2 Open the xml file as a recordset then create a table based on the xml
'recordsets fields collection.
...

regards
alan@cashoz.com
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8013372
Re:

I need to create a text file (or text output to window) that contains the code to recreate an Access table


I like the option of opening a recordset based on the chosen table then
persisting the recordset as xml.

Then Open the xml.recordset which has all the table properties defined
compliments of  ADO and create a table based on the xml.recordset.


Dim rsUser As New ADODB.Recordset
Dim sqlUser As String
'Build a recordset that returns no records    
sqlUser = "SELECT  * FROM tbl_YourTable where tbl_YourTable.CusID = -999"

'STEP 1 create rsUser and persist as User.xml
    rsUser.CursorLocation = adUseClient
    rsUser.Open sqlUser, cn, adOpenStatic, adLockBatchOptimistic, adCmdText
    Set rsUser.ActiveConnection = Nothing
    Dim sUserXml As String
    sUserXml = fso.BuildPath("C:\temp\", "User.xml")
   
    With rsUser
      .Save sUserXml, adPersistXML
      .Close
    End With
'Step 2 Open the xml file as a recordset then create a table based on the xml
'recordsets fields collection.
...

regards
alan@cashoz.com
0
 
LVL 10

Author Comment

by:apollois
ID: 8013522
Please do not use the browser's refresh/reload button.  Due to a bug in EE, it will cause a duplicate post.

To refresh the question, click the link "Reload this question" in the upper left corner of this page.

Thanks.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8013568
Get me every time.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 1600 total points
ID: 8014228
apollois,


'Step 2
'Open the xml file from anywhere lan wan
'very transportable
'Create table based on xml file created in previous Step

'This could be altered to create a sql maketable script.
Dim sTemp As String
rsUser.Open sUserXml, cn, adOpenStatic, adLockBatchOptimistic, adCmdFile

Dim fld As DAO.Field
Dim i As Integer
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table    'Requires a reference to Microsoft ADO Ext. 2.7 for DDL and Security
Set cat.ActiveConnection = cn
    With tbl
      Set .ParentCatalog = cat
      .name = "MyNewTable"
    End With

For i = 0 To (rsUser.Fields.Count - 1)
    Debug.Print rsUser.Fields(i).name
     tbl.Columns.Append rsUser.Fields(i).name, rsUser.Fields(i).Type, rsUser.Fields(i).DefinedSize  'etc...
Next
'get an error if table already exists
cat.Tables.Append tbl

hmmm that should do it
Sorry about the double submits.
Alan

0
 
LVL 5

Expert Comment

by:Netminder
ID: 8014552
alanwarren,

I've been asked to delete the duplicate posts, something we are loathe to do unless it's a serious violation of the Membership Agreement OR if the person who posted okays it.

Any objections?

Netminder
EE Admin
0
 
LVL 10

Author Comment

by:apollois
ID: 8020892
Nico et al,

Thanks for the short code segment.  It's in the right direction, but this falls far short of answering my question.

>>>Hope you're not too disappointed.<<<

Yeah, I am disappointed.  For a 400 point question, I guess I expected that you Access experts would be willing to do a little research, look at your code libraries, and maybe adapt something that comes close.

I'm not expecting a polished app, but at least some simple code that output a listing of the table structure something like this:

Table Customers
================
Cust_ID  autonumber primary key
LName  Text(30)
FName  Text(20)
Age   Integer
BirthDate Date/Time

Failing this, at least a list of linked references where the DDL code has been used.

I guess there's differences TA to TA as to what the EE Experts are willing to do.  In the ASP and Javascript TAs, posting 2 or 3 pages of code by the EE Expert is common for 300-pt and up questions.  Heck, I've even seen it for 50pt questions.

Don't mean to offend anyone here, I'm just disappointed.

Best Regards,
apollois
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8021231
Hi apollois,

I'm never disappointed.
My main target here is to teach and help people to enjoy creating their own applications.
Also from a maintenance point of view it's best to have knowledge how an application is structured.
With a little effort you'll be able to extract all information in your above sample from my code by using using the field properties.

I do state regularly that I don't answer Q's for points as I have more then enough of them.
Thus I hope you're not disappointed/offended that I don't code for points :-)

Nic;o)

BTW when you're stuck on changing the code, feel free to add a comment here and I'll help you !
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8021238
I tried,

thought you wanted to recreate a table.

Have you thought of using the documenter?

Menu > Tools > Analyse > Documenter.

0
 
LVL 10

Author Comment

by:apollois
ID: 8021305
Nico,

>>>BTW when you're stuck on changing the code, feel free to add a comment here and I'll help you ! <<<

I did.  I asked two questions.  No response.



Alan,

Thanks.  I'll take a look at your code.  Looks promising.

Best Regards,
apollois
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8021307
Netminder

Yeah, sorry about the double posts, I am at a loss as to why this is happening sometimes. I'm fairly sure it's not the browser.refresh. It does it whether I click with my mouse or tab to the submit and use the spacebar.

Please feel free to delete duplicates.

Alan
0
 
LVL 1

Expert Comment

by:AceDefabul
ID: 8022026
Well, here's some unpolished code from a module I wrote a while back when I was experimenting with DDL.

GetBasicTableDDL(txtTableName) returns ddl to create the
table passed to it.

GetIndexesDDL(txtTableName) retuns dll to create the indexes and fk constraints.

-----------------------
Option Compare Database
Option Explicit


'CREATE UNIQUE INDEX if no duplicates allowed
'CREATE INDEX if duplicates allowed
Function GetRelationsDDL(sTableName) As String
  Dim DB As DAO.Database
  Dim rel As DAO.Relation
  Dim fld As DAO.Field
  Dim sTemp As String
  Dim sConstraintName As String
 
  Set DB = CurrentDb
 
  For Each rel In DB.Relations
    With rel
      If .ForeignTable = sTableName Then
       
        For Each fld In .Fields
          sConstraintName = rel.Name ' "fk" & .ForeignTable & fld.ForeignName
          sConstraintName = Replace(sConstraintName, " ", "")
          sTemp = sTemp & "CONSTRAINT " & sConstraintName & " FOREIGN KEY (" & fld.ForeignName & ") REFERENCES " & .Table & " (" & fld.Name & ")," & vbCrLf
        Next
     End If
    End With
  Next
  GetRelationsDDL = sTemp
  Set DB = Nothing
End Function

Function GetPrimaryKey(sTableName As String) As String
  ' shows the Primary key of sTableName
  Dim DB As DAO.Database
  Dim tdf As TableDef
  Dim fld As Field
  Dim sTemp As String
  Dim sConstraintName As String
  Set DB = CurrentDb
  Dim i As Integer
 
  sConstraintName = Replace(sTableName, " ", "")
  sConstraintName = "pk" & sConstraintName
  Set tdf = DB.TableDefs(sTableName)
 
 
  For i = 0 To tdf.Indexes.count - 1
    If tdf.Indexes(i).Primary = True Then
      For Each fld In tdf.Indexes(i).Fields
        sTemp = sTemp & "," & fld.Name
      Next
    End If
  Next
  sTemp = "(" & Mid$(sTemp, 2) & "),"
  sTemp = "  CONSTRAINT " & sConstraintName & " PRIMARY KEY " & sTemp
  GetPrimaryKey = sTemp & vbCrLf
End Function

Function GetBasicTableDDL(sTableName As String) As String
 
  Dim DB As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim sTemp As String
  Dim sCurrentLine As String
 
  Set DB = CurrentDb
  Set tdf = DB.TableDefs(sTableName)
  For Each fld In tdf.Fields
    sCurrentLine = sCurrentLine & "     " & Left$(fld.Name & String$(30, " "), 30)
    sCurrentLine = sCurrentLine & GetFieldType(fld.Type)
    'get size if Text field
    If fld.Type = dbText Then
      If Len(fld.Size & "") > 0 Then
        sCurrentLine = sCurrentLine & "(" & fld.Size & ")"
      End If
    End If
    'allow nulls?
    If fld.Required Then sCurrentLine = sCurrentLine & " NOT NULL "
    sCurrentLine = sCurrentLine & "," & vbCrLf
   Next
  sCurrentLine = sCurrentLine & GetPrimaryKey(sTableName)
  sCurrentLine = sCurrentLine & GetRelationsDDL(sTableName)
  sCurrentLine = Left$(sCurrentLine, Len(sCurrentLine) - 3)
  sTemp = sTemp & "CREATE TABLE " & Replace(sTableName, " ", "") & " (" & vbCrLf
  sTemp = sTemp & sCurrentLine
 
  sTemp = sTemp & vbCrLf & ")"
  GetBasicTableDDL = sTemp
End Function

Function GetFieldType(lType As Long) As String
Dim s As String
Select Case lType
  Case dbBigInt
    s = "Big Integer"
  Case dbBinary
    s = "Binary"
  Case dbBoolean
    s = "Boolean"
  Case dbByte
    s = "Byte"
  Case dbChar
    s = "Char"
  Case dbCurrency
    s = "Currency"
  Case dbDate
    s = "Date/Time"
  Case dbDecimal
    s = "Decimal"
  Case dbDouble
    s = "Double"
  Case dbFloat
    s = "Float"
  Case dbGUID
    s = "GUID"
  Case dbInteger
    s = "Integer"
  Case dbLong
    s = "Long"
  Case dbLongBinary
    s = "Long Binary (OLE Object)"
  Case dbMemo
    s = "Memo"
  Case dbNumeric
    s = "Numeric"
  Case dbSingle
    s = "Single"
  Case dbText
    s = "Text"
  Case dbTime
    s = "Time"
  Case dbTimeStamp
    s = "Time Stamp"
  Case dbVarBinary
    s = "VarBinary"
  Case Else
    s = "Unknown"
End Select

GetFieldType = s

End Function

Function GetRelation(sTableName, sFieldName) As String
  ' shows any relationships sTableName is part of
  Dim DB As DAO.Database
  Dim rel As DAO.Relation
  Dim fld As DAO.Field
  Dim sTemp As String
  Dim sConstraintName As String
 
  Set DB = CurrentDb
 
  For Each rel In DB.Relations
    With rel
      If .ForeignTable = sTableName Then
       
        For Each fld In .Fields
          If fld.Name = sFieldName Then
           ' sConstraintName = "fk" & .ForeignTable & fld.ForeignName
           ' sConstraintName = Replace(sConstraintName, " ", "")
            sTemp = sTemp & " FOREIGN KEY REFERENCES " & .Table & " (" & fld.Name & ")," & vbCrLf
          End If
        Next
     End If
    End With
  Next
  GetRelation = sTemp
  Set DB = Nothing
End Function

Function GetIndexesDDL(sTableName As String) As String
 
  Dim DB As DAO.Database
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim idx As DAO.Index
  Dim sFields As String
  Dim sDDL As String
  Set DB = CurrentDb
  Dim i As Integer
 
  Set tdf = DB.TableDefs(sTableName)
   
 
  For Each idx In tdf.Indexes
    sFields = ""
    sDDL = sDDL & "Create "
   
    If idx.Unique Then
      sDDL = sDDL & "Unique "
    End If
   
    sDDL = sDDL & "Index " & idx.Name & vbCrLf & "  ON " & sTableName & " ("
   
    For Each fld In idx.Fields
       sFields = sFields & "," & fld.Name
    Next
    sDDL = sDDL & Mid$(sFields, 2) & ")" & vbCrLf
   
    If idx.Required Then
      sDDL = sDDL & "  WITH DISALLOW NULL  "
    End If
   
    sDDL = Mid$(sDDL, 1, Len(sDDL) - 2) & ";"
    sDDL = sDDL & vbCrLf & vbCrLf
   
   ' Debug.Print "Primary Key: "; idx.Primary
   ' Debug.Print "Clustered: "; idx.Clustered
   ' Debug.Print "Ignore Nulls: "; idx.IgnoreNulls
   ' Debug.Print "Foreign Key: "; idx.Foreign
       
  Next
  GetIndexesDDL = sDDL
End Function

---------------------------------
0
 
LVL 10

Author Comment

by:apollois
ID: 8207445
Thanks alan.  This looks great.

Sorry it took me so long to select an answer -- I got busy with other stuff and forgot.  You should have prompted me. <bg>

Best Regards,
>apollois<
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question