?
Solved

SQL Table Structure Statement

Posted on 2010-09-20
9
Medium Priority
?
465 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 400 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 1600 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

765 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