Solved

SQL Table Structure Statement

Posted on 2010-09-20
9
459 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
  • 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 33

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
 
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 33

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 33

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now