Programming Excel with VBA and .NET

Name

workbooks.OpenDatabase(Filename, [CommandText], [CommandType], [BackgroundQuery], [ImportDataAs])

Synopsis

Creates a new workbook and imports data from a database into it. Returns a reference to the new workbook.

Argument	Settings
Filename	The database file to open, or an Office Data Connection (.odc) file specifying the data source.
CommandText	A command to execute on the database. Typically, this is a SQL command.
CommandType	The type of command to execute. This seems to be ignored, but you can specify “SQL”, “Table”, or “Default” if you like.
BackgroundQuery	True retrieves the data asynchronously in the background; False retrieves the data synchronously. Default is False.
ImportDataAs	It is not clear how this argument is used.

When working with file-based databases, such as from Microsoft Access, you can specify the filename to use as the data source as shown here:

cnn = "C:\Program Files\Microsoft Office\OFFICE11" & _
  "\SAMPLES\Northwind.mdb"
sql = "SELECT * FROM Employees"
Set wb = Application.Workbooks.OpenDatabase(cnn, sql, , False)

The preceding code creates a new workbook and imports the Employees table from the Northwind Access database.

When working with server-based databases, such as from Microsoft SQL, you must specify the connection information in an Office Data Connection file (.odc). For example, the following code creates a new workbook and imports invoice information from the Northwind SQL database:

Dim wb As Workbook, cnn As String, sql As ...