SQL Table Structure Statement

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

LVL 1
simonwaitAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
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
 
aelliso3Commented:
See if adding the connection helps. Change RSConn.Open Sql  to
 
RSConn.Open Sql, DBConn
0
 
simonwaitAuthor Commented:
But how do I then say return the information into a msgbox?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
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
 
simonwaitAuthor Commented:
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
 
simonwaitAuthor Commented:
So that shows all tables including system tables, how would I then just get the user tables?
0
 
NorieVBA ExpertCommented:
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
 
simonwaitAuthor Commented:
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
 
simonwaitAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.