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;")
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%'"