Exporting Excel Data to Access using DAO/ADO/VBA 
Author Message
 Exporting Excel Data to Access using DAO/ADO/VBA

I plan to use an SQL SELECT query on the Excel sheet data and than an SQL
UPDATE query to update an access database with the Excel sheet data.

Aparently I only can do this on other Excel documents,
but not with the Excel document I'am actualy working with.

Is there realy no solution?

--------------------------------------------------------------------------
DETAILS:

The work is done in Excel so the export/update must be started in Excel.

The sheet has about 100 colums (price-list calculation). Frequently colums
will be added or deleted.
the only way to reference to a column reliably, is refering to it by
ColumnName
(first Excel row). So I need an SQL Query to select the desired columns

---------------------------------------------------------------------------

Thanks for your help
Alexander Keysselitz



Sun, 14 Dec 2003 15:52:22 GMT  
 Exporting Excel Data to Access using DAO/ADO/VBA
I don't see a problem with what you propose doing.

Have a look at some of the ADO/DAO help files for examples. From a single
procedure, you should be able to create separate recordsets linked to your
Excel and Access data. Then you can perform whatever updates you need -- back
and forth.

C Petrin

Quote:

> I plan to use an SQL SELECT query on the Excel sheet data and than an SQL
> UPDATE query to update an access database with the Excel sheet data.

> Aparently I only can do this on other Excel documents,
> but not with the Excel document I'am actualy working with.

> Is there realy no solution?



Wed, 17 Dec 2003 16:01:48 GMT  
 Exporting Excel Data to Access using DAO/ADO/VBA
I'm missing the earlier dialog on this thread, but here's a possible suggestion.

I find it's ofter easier to set this type of thing up in reverse and use Access
functionality directly rather than code it myself through ADO/DAO.  From Access,
link to your Excel target range as a linked table and set up a corresponding
append query/macro again in Access. Then, in Excel, through VBA, launch the
Access db as an application object and use a DoCmd to execute that seame append
query/macro.  May not be super elegant, but works fine.

Quote:

> I don't see a problem with what you propose doing.

> Have a look at some of the ADO/DAO help files for examples. From a single
> procedure, you should be able to create separate recordsets linked to your
> Excel and Access data. Then you can perform whatever updates you need -- back
> and forth.

> C Petrin


> > I plan to use an SQL SELECT query on the Excel sheet data and than an SQL
> > UPDATE query to update an access database with the Excel sheet data.

> > Aparently I only can do this on other Excel documents,
> > but not with the Excel document I'am actualy working with.

> > Is there realy no solution?



Wed, 24 Dec 2003 08:03:37 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Stopping users from getting at Access data using Excel VBA/DAO

2. Stopping users from getting at Access data using Excel VBA/DAO

3. Exporting Data From Excel To Access Using VBA In Access

4. Access as data Server Using ADO & DAO

5. Retrieving Access OLE Object data from Word VBA using DAO

6. export data fom access to excel with ADO?

7. DAO, ADO, Excel, VBA???

8. Using ADO Recordset with Forms (ADO vs DAO)

9. How to directly work on an excel document using ADO or DAO

10. ADO and DAO [in Access VBA]


 
Powered by phpBB® Forum Software © phpBB Group