Quick help - SQL statement in Excel Macro...
 

Code
-------------------
Option Explicit
Sub test()
Dim inttest
inttest = 0
Workbooks("efmimuster").Worksheets("Tabelle1").Cel ls(10, 1) = inttest


End Sub

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


Hi, this is how far I got in Excel :o

Now I would like to implement the following SQL statement:

SELECT Count(tblmain.main_id) AS Anzahlvonmain_id
FROM tblsbu INNER JOIN ((tblarea INNER JOIN tblcinco ON tblarea.area_i
= tblcinco.area_id) INNER JOIN tblmain ON tblcinco.cinco_id
tblmain.cinco_id) ON tblsbu.sbu_id = tblcinco.sbu_id
GROUP BY tblsbu.sbu, tblarea.area, tblmain.status
HAVING (((tblsbu.sbu)="Electronic Technologies") AN
((tblarea.area)="A-1") AND ((tblmain.status)="EF"));

I think that I basically need to open an Access session, open th
database, run somewhere the sql statement, close it all again. Fo
that I believe to have to also implement some kind of Access library i
excel.

Never done this before though, maybe I don't have to open an Acces
session? I read somewhere I can access tables without opening th
database...

Don't know, could someone pls just show me a standard routine to d
this. Thanks a lot

--
Message posted from http://www.ExcelForum.com

Quick help - SQL statement in Excel Macro...
 

You can use either native Excel QueryTable...
or do it the harder way and code your own ado routine
Which is not so complicated as you might think
(see http://erlandsendata.no)

have you ever used /data /import external data / new db query?

either save the query or create with macro recorder running and edit the
code.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Florianbur wrote:


Code:
--------------------
Option Explicit
Sub test()
Dim inttest
inttest = 0
Workbooks("efmimuster").Worksheets("Tabelle1").Cel ls(10, 1) =
inttest


End Sub

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


Hi, this is how far I got in Excel :o

Now I would like to implement the following SQL statement:

SELECT Count(tblmain.main_id) AS Anzahlvonmain_id
FROM tblsbu INNER JOIN ((tblarea INNER JOIN tblcinco ON
tblarea.area_id = tblcinco.area_id) INNER JOIN tblmain ON
tblcinco.cinco_id = tblmain.cinco_id) ON tblsbu.sbu_id =
tblcinco.sbu_id GROUP BY tblsbu.sbu, tblarea.area, tblmain.status
HAVING (((tblsbu.sbu)="Electronic Technologies") AND
((tblarea.area)="A-1") AND ((tblmain.status)="EF"));

I think that I basically need to open an Access session, open the
database, run somewhere the sql statement, close it all again. For
that I believe to have to also implement some kind of Access library
in excel.

Never done this before though, maybe I don't have to open an Access
session? I read somewhere I can access tables without opening the
database...

Don't know, could someone pls just show me a standard routine to do
this. Thanks a lot.


---
Message posted from http://www.ExcelForum.com/

Quick help - SQL statement in Excel Macro...
 

Florianbur wrote ...

I think that I basically need to open an Access session, open the
database, run somewhere the sql statement, close it all again. For
that I believe to have to also implement some kind of Access library in
excel.

You definitely do not want to automate the MS Access app.

You need a data access (small 'a') tool. The popular choices for Excel
users are MS Query (Data | Import External Data | New Database Query)
for GUI tools i.e. has a SQL window you can paste your code into, and
ADO if you prefer VBA code.

Jamie.

--