How to find information about workbook tables using MS Query

[SkipVought]

Ladies & Gents:

What is the SQL for accomplishing this in Excel > MS Query, like...

SELECT *
FROM information_schema.TABLES

once drilling down to a particular workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Do you mean something like this to work with a table?

sSQL = "select TableName=object_name(id) from sysobjects where type = 'U' and [name] in (" & sNewTableList & ") "
rsFrt.Open sSQL, cnFrt, 3, 1, 1

 

[SkipVought]

Without knowing anything about this particular workbook, 1) what are the tables (sheets) and Named ranges OR given a particular table in this workbook, 2) what are the columns?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Heres the code that renames a table in SQL driven from a userforms checkbox in Excel. It also cross checks against Access to make sure the correct database is selected. Its rather lengthy.

Public Sub RenameTable()

' just in case something goes wrong
On Error GoTo Error_Handler

Application.StatusBar = "Renaming Table"

' Make sure the sheet that has the buttons clicked is being used
'Sheets("1-ConfirmMDBInfo").Select

' Make sure all variables are cleared
Cleaner
If Warning_Flag = 1 Then Exit Sub

' Populate the table list
TableList
' If no date was entered then exit the sub
If Warning_Flag = 1 Then Exit Sub

' Set the flag to zero for future use
Warning_Flag = 0

' Get the last row
ilastrow = Sheets("1-ConfirmMDBInfo").Range("B1").End(xlDown).Row

'check thru and see if any databases already exist

' Clear Variable
sNewTableList = ""

Section_1
If Warning_Flag = 1 Then Exit Sub

' There should always be a table name but if theres not this won't happen
If sNewTableList > "" Then

Section_2
If Warning_Flag = 1 Then Exit Sub

' Check if its the end of the record set
If Not rsFrt.EOF Then

Section_3
If Warning_Flag = 1 Then Exit Sub

Else

Section_4
If Warning_Flag = 1 Then Exit Sub

End If 'not rsFrt.EOF

End If 'sNewTableList > ""

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Rename Tables Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub
Private Sub Section_1()

' just in case something goes wrong
On Error GoTo Error_Handler

Application.StatusBar = "Checking for existing table"

For I = 2 To ilastrow

' Get the name of the table that was check marked
If Sheets("1-ConfirmMDBInfo").OLEObjects("chk" & I).Object.Value Then

' Get table name from column C
sTableName = Trim(Sheets("1-ConfirmMDBInfo").Range("C" & CStr(I)).Value)

' Verify what to use for dates on the tables.
Check_For_Existing_Table

' Put date in front of table name
sNewTableList = sNewTableList & "'" & sCurrentMonth & "_" & sTableName & "',"

' Flag is created in check for existing tables to indicate
' the date was updated correctly. It is a way to exit the For loop
' once the correct information is aquired
If Flag = 1 Then GoTo Done_With_Check

End If

Next I

Done_With_Check:

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Section_1 Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub
Private Sub Section_2()

' just in case something goes wrong
On Error GoTo Error_Handler

Application.StatusBar = "New Table"

' Remove comma from the far right of the table name
If Right(sNewTableList, 1) = "," Then sNewTableList = Left(sNewTableList, Len(sNewTableList) - 1)

'Debug.Print sNewTableList

' Connect to SQL
SQL_CONNECTION

' Open the recordset
Open_Recordset_Active_Connection

' SQL Code
sSQL = "select TableName=object_name(id) from sysobjects where type = 'U' and [name] in (" & sNewTableList & ") "
rsFrt.Open sSQL, cnFrt, 3, 1, 1

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Section_2 Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub

Private Sub Section_3()

' just in case something goes wrong
On Error GoTo Error_Handler

Application.StatusBar = "Checking if table already exists"

' Clear Variable
sExistTableList = ""

' While the recordset isn't at the end..
While Not rsFrt.EOF

' Add the table name and the rsfrt tablename together
sExistTableList = sExistTableList & rsFrt("TableName") & vbCrLf

' Check the next record
rsFrt.MoveNext

Wend

' Tell the user if a table already exists
iAnswer = MsgBox("The following table(s) already exist, continue? " & vbCrLf & vbCrLf & sExistTableList, vbYesNo, "Renaming table...")

' If the user doesn't want to continue exit sub
If iAnswer = 7 Then Exit Sub

' VBYES = 6
If iAnswer = 6 Then

' Get the row count again
For I = 2 To ilastrow

If Sheets("1-ConfirmMDBInfo").OLEObjects("chk" & I).Object.Value Then

' Get the table name from column C
sTableName = Trim(Sheets("1-ConfirmMDBInfo").Range("C" & CStr(I)).Value)

' Add the month to the table name
sNewTableName = sCurrentMonth & "_" & sTableName

Rename_Table_SQL_Code

' Exit For
If Flag = 1 Then GoTo A

End If

Next I

A:

Close_SQL_Connection_And_Recordset

' Tell the user what happened
Application.StatusBar = "Done renaming table(s)"

End If

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Section_3 Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub

Private Sub Section_4()

' just in case something goes wrong
On Error GoTo Error_Handler

Application.StatusBar = "Renaming table in SQL"

For I = 2 To ilastrow

If ActiveSheet.OLEObjects("chk" & I).Object.Value Then
sTableName = Trim(ActiveSheet.Range("C" & CStr(I)).Value)
sNewTableName = sCurrentMonth & "_" & sTableName

Rename_Table_SQL_Code

' Exit For
If Flag = 1 Then GoTo A

End If

Next I

A:

Close_SQL_Connection_And_Recordset

Application.StatusBar = "Done renaming tables"

Exit Sub

Error_Handler:

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "Section_4 Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub


Public Sub TableList()

' Just in case
On Error GoTo Error_Handler

Application.StatusBar = "Creating Table List"

' Make sure the sheet that has the buttons clicked is being used
'Sheets("1-ConfirmMDBInfo").Select

' Make sure all variables are cleared
Cleaner
If Warning_Flag = 1 Then Exit Sub

' Get date
Date_From_Inputbox
' If the flag is set to one here it means
' a date wasn't entered in the input box
If Warning_Flag = 1 Then Exit Sub

' Clear existing info
Sheets("1-ConfirmMDBInfo").Range("D40500").ClearContents

SQL_CONNECTION
If Warning_Flag = 1 Then Exit Sub

Open_Recordset_Active_Connection
If Warning_Flag = 1 Then Exit Sub

'get table name list
sSQL = "select TableName = object_name(id) "
sSQL = sSQL & "from sysobjects where type = 'u' and object_name(id) like '" & sCurrentMonth & "%' "
sSQL = sSQL & "order by substring(object_name(id),charindex('_',object_name(id),1)+1,30),object_name(id)"
rsFrt.Open sSQL, cnFrt, 3, 1, 1

I = 40
While Not rsFrt.EOF
Sheets("1-ConfirmMDBInfo").Range("D" & I).Value = rsFrt("TableName")
I = I + 1
rsFrt.MoveNext
Wend

Close_SQL_Connection_And_Recordset

Exit Sub

Error_Handler:

Application.ScreenUpdating = True

Warning_Flag = 1

MsgBox Err.Description

Application.StatusBar = "TableList Failed. " & Err.Description

Close_SQL_Connection_And_Recordset

End Sub

 

[SkipVought]

Your voluminous code, assumes that you know stuff about the workbook!!!

Have you read my question???

Like Sgt Shultz, "I know noTHING!" about the tables and sheets in the workbook!!! I want to know if there is SQL that can return me information about the tables in the workbook!!!

How many different ways can I state it??? _{Let me not count the ways}

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Yes it can. Typically I use a single variable to return table results from SQL or when reading variables in Excel to translate to SQL.

In the example above the first sheet holds a user form column A is a checkbox, column B is a list of the names of Access databases and column C is the list of SQL tables. This in turn populates a list of tables from SQL into Excel and populates a field on sheet 2.

On sheet 2 the user updates the month they want info from, clicks a button and SQL populates information into an Excel table based on the information from the user. That table holds 5 columns of information.

The user does what they have to do and then updates the table which prints and then creates a batch in SQL.

 

[SkipVought]

What I want to know is, there is a sheet named "Sheet1" and a sheet named "Sheet2" in this workbook, but when I query another workbook, there are 4 sheets named, "Summary", "IV", "RQ", and "RP".

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Have you tried to play with an ADOX.Catalog object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

ADOX.Catalog: No I have not, but I will look.

Thanx PHV!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I had not been thinking about ADO, hoping to accomplish this via MS Query alone.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}