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
RSConn.Open Sql
End With

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

NorieConnect With a Mentor VBA 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).
aelliso3Connect With a Mentor Commented:
See if adding the connection helps. Change RSConn.Open Sql  to
RSConn.Open Sql, DBConn
simonwaitAuthor Commented:
But how do I then say return the information into a msgbox?
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.
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
simonwaitAuthor Commented:
So that shows all tables including system tables, how would I then just get the user tables?
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.
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.
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
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.