Solved

SQL Table Structure Statement

Posted on 2010-09-20
9
464 Views
Last Modified: 2012-06-21
I have a database with 5 user tables in it.  I am trying to get the information of each of the table's structures and my plan is then to use that information to populate the columns of an excel sheet with sheet per table and column per field.  under each column it should show the column name in row 1, type in row 2, length in row 3 null in 4 and so on.  I have written the code below but seem to get an error 3709 - The connection cannot be used to perform this operation.  It is either closed or invalid in this context.
Dim DBConn As New ADODB.Connection
Dim RSConn As New Recordset
DBConn.Open ("Provider=SQLOLEDB.1;User ID=user;Password=password;Initial Catalog=a;Data Source=ENTALIVE2008\ENTABOXOFFICE;")
With RSConn
Sql = "SELECT ORDINAL_POSITION AS POSITION, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH, COLUMN_DEFAULT, IS_NULLABLE As ALLOW_NULL FROM INFORMATION_SCHEMA.Columns WHERE Table_Name  LIKE 'tbl%'"
RSConn.Open Sql
End With
DBConn.Close

Open in new window

0
Comment
Question by:simonwait
[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
  • 5
  • 3
9 Comments
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 100 total points
ID: 33719118
See if adding the connection helps. Change RSConn.Open Sql  to
 
RSConn.Open Sql, DBConn
0
 
LVL 1

Author Comment

by:simonwait
ID: 33724905
But how do I then say return the information into a msgbox?
0
 
LVL 34

Expert Comment

by:Norie
ID: 33730768
Return what information in a message box?

Do you mean the data for the records in the recordset?

Don't you want to actually put that somewhere (Excel worksheet) rather than temporarily display it in.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 1

Author Comment

by:simonwait
ID: 33732229
Ultimatly I want the information displayed as I described at the beginning but I thought it would be easier to ask how to display in a msgbox and then I could figure it out from there but if an expert wants to just tell me how to do exactly as above, sheet name is table name, column names on row 1, type on 2 etc.  That would be great
0
 
LVL 34

Accepted Solution

by:
Norie earned 400 total points
ID: 33732989
Well what you've got will return a recordset with all the information for all the tables in the specified database/catalog.

You can transfer all of that to a worksheet like this.

Worksheets("Sheet1").Range("A1").CopyFromRecordSet RSConn

You won't get column/field names and it will be data from all the tables in one worksheet.

If you want separate worksheets per table you have 2 choices.

1 Create a recordset that returns all the table names, use them in the criteria for another recordset and copy the results from that to a worksheet.

2 Use Excel's filtering capabilities to separate the data out, one table to one worksheet.

Give the code I posted a shot and post back if you need help with it or with the other bit(s).
0
 
LVL 1

Author Comment

by:simonwait
ID: 33738654
So that shows all tables including system tables, how would I then just get the user tables?
0
 
LVL 34

Expert Comment

by:Norie
ID: 33740451
Eh, the code doesn't actually show all the system tables as far as I can see.

It shows all the tables beginning with 'tbl'.

Even when I remove the criteria it only shows the user tables in the database I run it against.
0
 
LVL 1

Author Comment

by:simonwait
ID: 33744972
I think that actually my last comment is abit ambigous.  My code has now evolved and I think the last question I was asking is actually worthy of its own question.  I have opened a new question (ID:26494667) and will close this one.
0
 
LVL 1

Author Closing Comment

by:simonwait
ID: 33744989
I believe that the actual question (why am I getting the error was answered by question aelliso3 but a more usable piece of information was provided by imnorie.  I hope this is fair
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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