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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So that shows all tables including system tables, how would I then just get the user tables?
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.
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.
ASKER
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.
ASKER
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
ASKER