rcmb
asked on
Export Table Structure
I have a website that contains in excess of 2000 pages. In this site I use multiple access databases. I need to export the db structure of each database for various reports. Is there anyway I can export the structure of each table to list all of the columns and the column type (e.g. datetime, number, text, etc.)?
RCMB
RCMB
How do you want the output of your export formatted? Are you starting something like a data dictionary, so that a text file or spreadsheet would be adequate, or are you looking to have the structure in some kind of data definition language that could be used to actually recreate the database?
ASKER
Excel is just fine.
Take a look at http://68.209.129.5/database.htm and you can see the format I eventually desire. Excel will import just fine into this format.
RCMB
Take a look at http://68.209.129.5/database.htm and you can see the format I eventually desire. Excel will import just fine into this format.
RCMB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All makes sense to me except what to do with the code you listed above. Do I create some VB code in access or something else? I am a web programmer and very little experience in programming in access. Please provide specifics.
Also -- will this ask me to provide the db path?
Also -- will this ask me to provide the db path?
ASKER
Okay I have the visual Basic module created and compiled -- now how do I run this module and input the db path?
RCMB
RCMB
You can do this many ways, the likeliest and easiest are:
1. You can run this in the Immediate window by typing
GetTableInfo("\\someserver
(You get the Immediate window when you're in the VBA IDE--like when you're editing a module--with the ctrl-G keystroke. You can dock the window at the bottom of the screen or leave it un-docked.)
2. You can design a basic form with a textbox, called for example DatabaseName, and a command button, called for example GetTheData. Then you can call the procedure from the On Click event of the button. You get to this by having the form in design mode, making sure that properties are displayed, click once on the button, the in the Events tab, there'll be a line for On Click. Click the ellipses that appear at the right of the On Click button, choose Event Procedure, which will open up a VBA IDE window with your cursor between something like
Private Sub GetTheData_Click
End Sub
Between those lines, type
GetTableInfo(me!DatabaseNa
MsgBox "Data retrieved successfully for " & me!DataBaseName & "!"
Then open the form in normal view mode, type the full path to one of the databases you want to retrieve info from, and click the button.
This is not at all robust--no error trapping/handling--but will work for your well-defined needs. I'd suggest you implement error handling if others beside you will be using it.
1. You can run this in the Immediate window by typing
GetTableInfo("\\someserver
(You get the Immediate window when you're in the VBA IDE--like when you're editing a module--with the ctrl-G keystroke. You can dock the window at the bottom of the screen or leave it un-docked.)
2. You can design a basic form with a textbox, called for example DatabaseName, and a command button, called for example GetTheData. Then you can call the procedure from the On Click event of the button. You get to this by having the form in design mode, making sure that properties are displayed, click once on the button, the in the Events tab, there'll be a line for On Click. Click the ellipses that appear at the right of the On Click button, choose Event Procedure, which will open up a VBA IDE window with your cursor between something like
Private Sub GetTheData_Click
End Sub
Between those lines, type
GetTableInfo(me!DatabaseNa
MsgBox "Data retrieved successfully for " & me!DataBaseName & "!"
Then open the form in normal view mode, type the full path to one of the databases you want to retrieve info from, and click the button.
This is not at all robust--no error trapping/handling--but will work for your well-defined needs. I'd suggest you implement error handling if others beside you will be using it.
ASKER
Thanks for your patience and quick response. Once I figured out how to run in immediate it worked great.
RCMB
RCMB