Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access query not showing as sources for pivot table | Excel Discussion (Misc queries) | |||
Excel worksheet retrieving data from Access | Links and Linking in Excel | |||
Creating a Product DATABASE in ACCESS or EXCEL and then retrieving | Excel Discussion (Misc queries) | |||
Excel Spreadsheet from Access. List of names changes as names are | Excel Discussion (Misc queries) | |||
Multiple MS Access table sources for pivot table | Excel Discussion (Misc queries) |