ODBC managment with VB Excel

[Jean-Pierre Bidon]

Hi,
I'd like to get information on the possibility to manage data source via
ODBC, the data source in hand being either an Excel or an Access file.
I am interested in any form of information source, being books or web
resources for instance.
Thanks in advance.

--
Jean-Pierre Bidon
Interstat
5 place de la République
75003 Paris
Tél: 01 45 49 19 17

 

[Jan Karel Pieterse]

Hi Jean-Pierre,

I'd like to get information on the possibility to manage data source via
ODBC, the data source in hand being either an Excel or an Access file.
I am interested in any form of information source, being books or web
resources for instance.

What are you trying to achieve? In Excel you can use Data, Get External
Data, new Database query to create ODBC connections from Excel to a
database or Excel file. Is that what you're after?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Jean-Pierre Bidon]

To answer your question, I'd say that I am working on a project in which I
have to manage numerous data tables that are spread in 2 to 3 Excel
workbooks, but that may be later in an Access (or other type of) database. I
am speaking of "tables" because all those data are in a db table form, i.e.
a header row with name of fields and data lined up in column below.
Secondly, I'd like to manipulate those data with either DAO, ADO or SQL
command, that I imagine would be a handy way to proceed.
I found in a Microsoft Press book (MS Office Excel 2003 Programming -
Inside/out - by C.Frye, W. Freeze & F. K. Buckingham) the concept of
PivotTables object that seems to correspond to my needs, but I am not really
sure, by lack of experimentation.
Regards,
Jean-Pierre

 

[Jan Karel Pieterse]

Hi Jean-Pierre,

Secondly, I'd like to manipulate those data with either DAO, ADO or SQL
command, that I imagine would be a handy way to proceed.
I found in a Microsoft Press book (MS Office Excel 2003 Programming -
Inside/out - by C.Frye, W. Freeze & F. K. Buckingham) the concept of
PivotTables object that seems to correspond to my needs, but I am not really
sure, by lack of experimentation.

If you are going to do this through VBA, ADO is the preferred way to access
datasources over DAO. Both require SQL commands.

Pivot tables can be based on data from Excel but also from an external
datasource, which in turn can still be a query on a table in an Excel file, but
just as easy a table in an ODBC compliant database.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Jean-Pierre Bidon]

Jan,
Thank you very much for those pieces of information and advice. Things
becomes more clear now.
I have some books on those subjects (PivotCaches, ADO, etc.), I will look at
them more in depth now.
Regards,
Jean-Pierre