I have a piece of code which works brilliantly at finding tables in a SQL database and putting their structures into an excel worksheet. At the moment the code looks for the 5 tables as defined in thistbl by name.
Is there a way that I can look through the database for all tables so that should, for instance, a table be added I dont need to change the vba code?
Do system tables have a type which could be included in a WHERE or something like that?
Dim DBConn As New ADODB.Connection
Dim RSConn As New Recordset
DBConn.Open ("Provider=SQLOLEDB.1;User ID=user;Password=password;Initial Catalog=catalog;Data Source=server;")
tblno = 1
tblnos = 5
For tblno = 1 To tblnos
nextrow = ActiveSheet.UsedRange.Rows.Count + 1
If nextrow = 2 Then
nextrow = 1
thistbl = Choose(tblno, "Assignments", "Equipment", "Faults", "Theatres", "Users")
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 = " & "'" & thistbl & "'"
RSConn.Open Sql, DBConn
Worksheets("SQL Structure").Range("A" & nextrow).CopyFromRecordset RSConn