show all tables in MS query editor "Excel"

Question

via Excel 2003 vba I made dsn connection by this method

Sub CreateDataSourceFile()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txtfile = fso.CreateTextFile("C:\program files\Common Files\ODBC\Data Sources\" & Me.cnnName & ".dsn", True)
    txtfile.WriteLine ("[ODBC]")
    txtfile.WriteLine ("DBQ=" & Me.DBFolderName)
    txtfile.WriteLine (Me.DBFolderName)
    txtfile.WriteLine ("DefaultDir=C:\")
    txtfile.WriteLine ("Driver={Driver do Microsoft Access (*.mdb)}")
    txtfile.WriteLine ("DriverId = 25")
    txtfile.WriteLine ("FIL=MS Access")
    txtfile.WriteLine ("MaxBufferSize = 2048")
    txtfile.WriteLine ("MaxScanRows = 8")
    txtfile.WriteLine ("PageTimeout = 5")
    txtfile.WriteLine ("SafeTransactions = 0")
    txtfile.WriteLine ("Threads = 3")
    txtfile.WriteLine ("UserCommitSync = Yes")
    txtfile.WriteLine ("[Microsoft Office]")
    txtfile.WriteLine ("DefaultTable =" & Me.cnnName)
    txtfile.Close
End Sub

now the issue is when Microsoft Query editor is opened for my connection it automatically add the default table to the editor, what I need is to have all tables in the data base added automatically?

is this available?! is there a way to do this by editing the dsn file?

any way, if any one can help me with this I will be thank full for him.

Have a nice day & thank you for reading,

Answer 1

You can set up ODBC data connections via Control Panel:

Start > Control Panel > Administrative Tools > Data Sources (ODBC)

Click on the System DSN tab and click Add. Select the driver (dependent on the database provider you have), then it will ask you for the relevant information -- the name you want to give your data connection, server name, the type of authentication, etc -- it will step you through the process.

Once you have the ODBC data connection set up, then you can invoke Excel's Query Editor and tell it the name of the connection. I'm using Excel 2007, but I think this is how to get there in 2003:

Data > Import External Data > New Database Query

Then it will allow you to select the ODBC data source you created before.

Once you get to the Query Editor, you will be able to see all the tables you have access to.

I wasn't entirely clear on what you were after. Does that help? If not, give me some more details on how you're trying to get to your data.