Excel-SQL Connect
 

Hi,

I have a little problem with my excel-SQL connection.

In one cell of my excel doc is a date. example 2003-11-04

I want to pass that date into my sql query.

select * from database where date="value from table1!A1"

is this possible?
I need a solution, so that the User in Excel can choose the date and than
click run.

thx,
mike

Excel-SQL Connect
 

sDate = _
Format$(Sheets("Table1").Range("A1"),"dd-mmm-yyyy")

sql="select * from database where date='" & sDate & "';"

note that (1) date is dd-mmm-yyyy and (2) is wrapped in
single quotes

Patrick Molloy
Microsoft Excel MVP




-----Original Message-----
Hi,

I have a little problem with my excel-SQL connection.

In one cell of my excel doc is a date. example 2003-11-04

I want to pass that date into my sql query.

select * from database where date="value from table1!A1"

is this possible?
I need a solution, so that the User in Excel can choose
the date and than
click run.

thx,
mike
.

Excel-SQL Connect
 

Hi Mike,

It is better use the standard SQL date format. For example,
select * from database
where date=TO_DATE(Format(Cdate("value from table1!
A1"),"DD-MMM-YYYY),"DD-MON-YYYY")

Best Regards

Bill


-----Original Message-----
Hi,

I have a little problem with my excel-SQL connection.

In one cell of my excel doc is a date. example 2003-11-04

I want to pass that date into my sql query.

select * from database where date="value from table1!A1"

is this possible?
I need a solution, so that the User in Excel can choose
the date and than
click run.

thx,
mike
.

Excel-SQL Connect
 

thanks for the answers.

The select from Microsoft Query Editor SQL contains:
SELECT auslastung_0.zeit, auslastung_0.usr, auslastung_0.sys, auslastung_
0.wio, auslastung_0.idle
FROM auswertung.auslastung auslastung_0
WHERE auslastung_0.zeit like ' & Format$(Sheets("Tabelle1").Range
("C7"),"dd.mm.YYYY" & ';

C7 contains:
04.11.2003

DB contains for example:
04.11.2003 14:10

didn't work. There still must be an error?

thx mike

"Patrick Molloy" wrote in news:077a01c3a76d
:

sql="select * from database where date='" & sDate & "';"

Excel-SQL Connect
 

Hi

Lately I answered to similar question in NG microsoft.public.de.excel. The
thread was 'Excel in Verbindung mit Datenabnk benutzen', started by Wolfgang
Dausend at 30.10.2003 12:22. Maybe you get some ideas.


--
(When sending e-mail, use address )
Arvi Laanemets


"Michael Schreiber" wrote in message
...
Hi,

I have a little problem with my excel-SQL connection.

In one cell of my excel doc is a date. example 2003-11-04

I want to pass that date into my sql query.

select * from database where date="value from table1!A1"

is this possible?
I need a solution, so that the User in Excel can choose the date and than
click run.

thx,
mike

Excel-SQL Connect
 

Thank you for the hint.

Now i now that i must connect through a VBA Macro.

I use the myodbc3 driver to connect to the database.
It works fine within excel as data-source.

But i can't manage to connect to the database through VBA.

Sub Daten_importieren()

Dim myconn As New Connection
Dim myrec As New Recordset
Dim mySQL As String
Dim myrow As Long

Dim sql$
Set ws = ThisWorkbook.Worksheets("Tabelle1")


conn.Open
"Provider=MSASQL;Driver=MySQL;Server=x.x.x.x,UID=r oot,PDW=****,database=a
uswertung"

sql = "SELECT auslastung_0.zeit, auslastung_0.usr, auslastung_0.sys,
auslastung_0.wio, auslastung_0.idle FROM auswertung.auslastung
auslastung_0"

rec.Open sql.conn
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Get an error that Connection is not defined ...

thx mike

"Arvi Laanemets" wrote in
:

Hi

Lately I answered to similar question in NG microsoft.public.de.excel.
The thread was 'Excel in Verbindung mit Datenabnk benutzen', started
by Wolfgang Dausend at 30.10.2003 12:22. Maybe you get some ideas.

Excel-SQL Connect
 

Hi

Haven't never made it such way myself. I create a ODBC query
(Data.GetExternalData), then write a procedure, which is changing the query
obect's CommandText property and then invokes refresh event, and attach it
to button from Forms toolbar. And usually I set data to be refreshed on open
(Data Range Properties). But I think when you need to change the connection
through code, then QueryTable.Connection must be the property to meddle
with. You have to read it somehow at first - then you'll know how to change
it.


--
(When sending e-mail, use address )
Arvi Laanemets


"Michael Schreiber" wrote in message
...
Thank you for the hint.

Now i now that i must connect through a VBA Macro.

I use the myodbc3 driver to connect to the database.
It works fine within excel as data-source.

But i can't manage to connect to the database through VBA.

Sub Daten_importieren()

Dim myconn As New Connection
Dim myrec As New Recordset
Dim mySQL As String
Dim myrow As Long

Dim sql$
Set ws = ThisWorkbook.Worksheets("Tabelle1")


conn.Open
"Provider=MSASQL;Driver=MySQL;Server=x.x.x.x,UID=r oot,PDW=****,database=a
uswertung"

sql = "SELECT auslastung_0.zeit, auslastung_0.usr, auslastung_0.sys,
auslastung_0.wio, auslastung_0.idle FROM auswertung.auslastung
auslastung_0"

rec.Open sql.conn
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Get an error that Connection is not defined ...

thx mike

"Arvi Laanemets" wrote in
:

Hi

Lately I answered to similar question in NG microsoft.public.de.excel.
The thread was 'Excel in Verbindung mit Datenabnk benutzen', started
by Wolfgang Dausend at 30.10.2003 12:22. Maybe you get some ideas.