retrieving table names from Excel and Access sources using ADO
I have created ADO connection sub below but I want to generate a list
of tables in the selected file, so that users don't have to know table names of the selected Excel or Access file in advance. If you could give me some hints or links for further study of excel ado, I would be appreciated. (undefined variables and objects are defined elsewhere.) Sub ConnectToDB() Dim cnnConn As ADODB.Connection Dim rstRecordset As ADODB.Recordset Dim cmdCommand As ADODB.Command Dim DBFileName As Variant Dim TableName As String Dim FilterList As String ' Get an access filename from wherever you want. FilterList = "Access Files (*.mdb),*.mdb, " & "Excel Files (*.xls),*.xls" DBFileName = Application.GetOpenFilename(FileFilter:=FilterList , Title:="Database File") ' Exit if GetOpenFilename dialog box canceled If DBFileName = False Then MsgBox "No file was selected." Cancel = True Exit Sub End If ' Request a table name in the DBFileName Do Until TableName < "" TableName = InputBox("Enter a table name in the database " & DBFileName) Loop ' Open a connection to the database file. Set cnnConn = New ADODB.Connection With cnnConn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Open DBFileName End With ' SQL query to the Access database Set cmdCommand = New ADODB.Command Set cmdCommand.ActiveConnection = cnnConn With cmdCommand .CommandText = "Select * From " & TableName .CommandType = adCmdText .Execute End With ' Open the TableName table as a recordset. Set rstRecordset = New ADODB.Recordset Set rstRecordset.ActiveConnection = cnnConn rstRecordset.Open cmdCommand ' Create a pivot cache Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) Set PC.Recordset = rstRecordset Call CrossTabConnect ' Close the connection to the database. cnnConn.Close Set cmdCommand = Nothing Set rstRecordset = Nothing Set cnnConn = Nothing End Sub |
retrieving table names from Excel and Access sources using ADO
Thanks onedaywhen. I have searched online further and wrote:
' List table names in the database file to LbTable listbox in UFSelectTable userform. Dim TableList As New adodb.Recordset Set TableList = cnnConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table")) While Not TableList.EOF NFound = NFound + 1 ReDim Preserve TArray(NFound) TArray(NFound) = TableList!TABLE_NAME TableList.MoveNext Wend Yes the table array is passed to a listbox. :) The following link was useful too. http://support.microsoft.com/support.../Q186/2/46.ASP I'll work out for excel files too as the code in my first post would not work for excel files. |
retrieving table names from Excel and Access sources using ADO
Have a look at the Recordset object's method called GetRows i.e. you
don't need to loop through the recordset to get an array of table names. Below is some example code. Also, you don't need to instantiate ('create') the recordset with the new keyword; OpenSchema creates the recordset and returns a reference/pointer to it: Dim TableList As ADODB.Recordset ' New keyword not required Dim TArray As Variant Set TableList = cnnConn.OpenSchema(adSchemaTables, _ Array(Empty,Empty, Empty, "Table")) TArray = TableList.GetRows(, , "TABLE_NAME") MyListBox.List = Excel.Application.Transpose(TArray) -- (masayoshi hayashi) wrote in message . com... Thanks onedaywhen. I have searched online further and wrote: ' List table names in the database file to LbTable listbox in UFSelectTable userform. Dim TableList As New adodb.Recordset Set TableList = cnnConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table")) While Not TableList.EOF NFound = NFound + 1 ReDim Preserve TArray(NFound) TArray(NFound) = TableList!TABLE_NAME TableList.MoveNext Wend Yes the table array is passed to a listbox. :) The following link was useful too. http://support.microsoft.com/support.../Q186/2/46.ASP I'll work out for excel files too as the code in my first post would not work for excel files. |
All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com