Quick help - SQL statement in Excel Macro...

F

Florianbur

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


End Sub

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


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

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
 
K

keepITcool

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 >
 
J

Jamie Collins

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.
Click to expand...

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.

--
 
Top