how to execute SQL query in excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Sir,

i have used microsoft access to come up with the Query and this is the SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have? how can i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
 
Here's an example that fills a column in an Excel worksheet with a list of
names from the Employees table in the Northwind sample database. You'll need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library. (If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub
 
hi Brendan,

Thanks for your script! however, my query has some expression calculation
done within the query and i dont know how it can be written in vba terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result of
comparison. And i want to execute other fields in the database table that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between [FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE] DESC;

awaiting yourprompt reply,
Junior 728

Brendan Reynolds said:
Here's an example that fills a column in an Excel worksheet with a list of
names from the Employees table in the Northwind sample database. You'll need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library. (If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Junior728 said:
Hi Sir,

i have used microsoft access to come up with the Query and this is the SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have? how can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
 
I'm not sure that I understand the question. The fact that your query
includes an expression does not, as far as I can see, change anything. You
just need to replace the "SELECT * FROM Employees" in my example with your
SQL string. Of course you have to put the SQL string into a properly
formatted VBA string - enclose it within quotes, and either place it all on
one line, or use the line continuation characters, a space followed by an
underscore at the end of continued lines. For example ...

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
"FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
"[CURRDATE]-14 DESC, [CURRDATE] DESC;"
rst.Source = strSQL

BTW: A couple of things about that query look a little odd. You have
"ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
actually produce a different result than sorting by CURRDATE alone? I don't
see how it could, and sorting on CURDATE-14 is a lot less efficient than
sorting on CURRDATE alone, as the database engine will not be able to use
any index that might exist on the CURRDATE field. This can make a big
difference to performance, especially when working with large databases
across a network.

Another BTW: It doesn't really bother me, I'm not offended or anything, but
'awaiting your prompt reply' isn't really very appropriate in a peer-to-peer
setting. We're not Microsoft employees here, we're volunteers helping each
other out. It's a bit like asking your neighbour for a favour, then telling
them 'and be prompt about it!' :-)

--
Brendan Reynolds

Junior728 said:
hi Brendan,

Thanks for your script! however, my query has some expression calculation
done within the query and i dont know how it can be written in vba terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result of
comparison. And i want to execute other fields in the database table that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
[FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
DESC;

awaiting yourprompt reply,
Junior 728

Brendan Reynolds said:
Here's an example that fills a column in an Excel worksheet with a list
of
names from the Employees table in the Northwind sample database. You'll
need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library. (If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Junior728 said:
Hi Sir,

i have used microsoft access to come up with the Query and this is the
SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have? how
can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
Click to expand...
 
Hi Brendan,

yah...indeed sound kind of weird..i thought qns are answered by
programmers.=)by the way.thanks for your script. it sort of work a bit but
when i try to run it, there is a error message that says no value is given
for one or more parameters. i suspect it is my [currdate] field that causes
this error. This field is not taken from the table, rather it is an input
prompt to the user. the user will then enter the date and the query will run.


How can i improve my script to incorporate that? See my e.g.

Private Sub Worksheet_Activate()

Dim connection As ADODB.connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim intRow As Integer

strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open strSQL
Set rst = New ADODB.Recordset
With rst
..ActiveConnection = connection
strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
"ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
"WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
"ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE] DESC;"
rst.Source = strSQL
..Open
Do Until .EOF
intRow = intRow + 1
Sheet1.Cells(intRow, 1) = .Fields("Mfg")
..MoveNext
Loop
..Close
End With
connection.Close

End Sub

Junior728

Brendan Reynolds said:
I'm not sure that I understand the question. The fact that your query
includes an expression does not, as far as I can see, change anything. You
just need to replace the "SELECT * FROM Employees" in my example with your
SQL string. Of course you have to put the SQL string into a properly
formatted VBA string - enclose it within quotes, and either place it all on
one line, or use the line continuation characters, a space followed by an
underscore at the end of continued lines. For example ...

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
"FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
"[CURRDATE]-14 DESC, [CURRDATE] DESC;"
rst.Source = strSQL

BTW: A couple of things about that query look a little odd. You have
"ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
actually produce a different result than sorting by CURRDATE alone? I don't
see how it could, and sorting on CURDATE-14 is a lot less efficient than
sorting on CURRDATE alone, as the database engine will not be able to use
any index that might exist on the CURRDATE field. This can make a big
difference to performance, especially when working with large databases
across a network.

Another BTW: It doesn't really bother me, I'm not offended or anything, but
'awaiting your prompt reply' isn't really very appropriate in a peer-to-peer
setting. We're not Microsoft employees here, we're volunteers helping each
other out. It's a bit like asking your neighbour for a favour, then telling
them 'and be prompt about it!' :-)

--
Brendan Reynolds

Junior728 said:
hi Brendan,

Thanks for your script! however, my query has some expression calculation
done within the query and i dont know how it can be written in vba terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result of
comparison. And i want to execute other fields in the database table that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
[FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
DESC;

awaiting yourprompt reply,
Junior 728

Brendan Reynolds said:
Here's an example that fills a column in an Excel worksheet with a list
of
names from the Employees table in the Northwind sample database. You'll
need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library. (If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Hi Sir,

i have used microsoft access to come up with the Query and this is the
SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have? how
can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
Click to expand...
 
That automatic, built-in prompt for parameters works only within Access. To
use the query in Excel, your Excel application will need to get the value
for the parameter from the user before executing the query. You could do
that by having the user enter the value in a cell in the Excel worksheet, by
using an Excel user-form, or by using the InputBox function. Once you have
the value, you can either build up the query in code as before or you can
use a pre-saved parameter query. Here's an example I posted recently in
response to a similar question ...

Below is the SQL for a saved parameter query in Access ...

PARAMETERS [Last Name?] Text ( 50 );
SELECT Employees.*
FROM Employees
WHERE (((Employees.LastName)=[Last Name?]));

.... and here is some VBA code to pass a variable as the parameter value, and
retrieve the result. This code would work identically from Access, Excel,
Word, or any other VBA-enabled application ...

Public Function GetFullName(LastName As String) As String

Dim strConnection As String
Dim objConnection As ADODB.connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rst As ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\USENET\Test.mdb;" & _
"Persist Security Info=False"
Set objConnection = New ADODB.Connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "qryTest"
objCommand.CommandType = adCmdStoredProc
Set objParam = objCommand.CreateParameter("[Last Name?]", _
adVarChar, adParamInput, 50, LastName)
objCommand.Parameters.Append objParam
Set rst = objCommand.Execute
GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
objConnection.Close

End Function

Result in the Excel VBA Immediate window ...
? getfullname("Davolio")
Davolio, Nancy

--
Brendan Reynolds

Junior728 said:
Hi Brendan,

yah...indeed sound kind of weird..i thought qns are answered by
programmers.=)by the way.thanks for your script. it sort of work a bit but
when i try to run it, there is a error message that says no value is given
for one or more parameters. i suspect it is my [currdate] field that
causes
this error. This field is not taken from the table, rather it is an input
prompt to the user. the user will then enter the date and the query will
run.


How can i improve my script to incorporate that? See my e.g.

Private Sub Worksheet_Activate()

Dim connection As ADODB.connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim intRow As Integer

strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open strSQL
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
"ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
"WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
"ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE]
DESC;"
rst.Source = strSQL
.Open
Do Until .EOF
intRow = intRow + 1
Sheet1.Cells(intRow, 1) = .Fields("Mfg")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

Junior728

Brendan Reynolds said:
I'm not sure that I understand the question. The fact that your query
includes an expression does not, as far as I can see, change anything.
You
just need to replace the "SELECT * FROM Employees" in my example with
your
SQL string. Of course you have to put the SQL string into a properly
formatted VBA string - enclose it within quotes, and either place it all
on
one line, or use the line continuation characters, a space followed by an
underscore at the end of continued lines. For example ...

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
"FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
"[CURRDATE]-14 DESC, [CURRDATE] DESC;"
rst.Source = strSQL

BTW: A couple of things about that query look a little odd. You have
"ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
actually produce a different result than sorting by CURRDATE alone? I
don't
see how it could, and sorting on CURDATE-14 is a lot less efficient than
sorting on CURRDATE alone, as the database engine will not be able to use
any index that might exist on the CURRDATE field. This can make a big
difference to performance, especially when working with large databases
across a network.

Another BTW: It doesn't really bother me, I'm not offended or anything,
but
'awaiting your prompt reply' isn't really very appropriate in a
peer-to-peer
setting. We're not Microsoft employees here, we're volunteers helping
each
other out. It's a bit like asking your neighbour for a favour, then
telling
them 'and be prompt about it!' :-)

--
Brendan Reynolds

Junior728 said:
hi Brendan,

Thanks for your script! however, my query has some expression
calculation
done within the query and i dont know how it can be written in vba
terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result
of
comparison. And i want to execute other fields in the database table
that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
[FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
DESC;

awaiting yourprompt reply,
Junior 728

:

Here's an example that fills a column in an Excel worksheet with a
list
of
names from the Employees table in the Northwind sample database.
You'll
need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library.
(If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Hi Sir,

i have used microsoft access to come up with the Query and this is
the
SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have?
how
can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
Click to expand...
 
þþÊã ßÊÇÈÉ "Brendan Reynolds said:
That automatic, built-in prompt for parameters works only within Access. To
use the query in Excel, your Excel application will need to get the value
for the parameter from the user before executing the query. You could do
that by having the user enter the value in a cell in the Excel worksheet, by
using an Excel user-form, or by using the InputBox function. Once you have
the value, you can either build up the query in code as before or you can
use a pre-saved parameter query. Here's an example I posted recently in
response to a similar question ...

Below is the SQL for a saved parameter query in Access ...

PARAMETERS [Last Name?] Text ( 50 );
SELECT Employees.*
FROM Employees
WHERE (((Employees.LastName)=[Last Name?]));

... and here is some VBA code to pass a variable as the parameter value, and
retrieve the result. This code would work identically from Access, Excel,
Word, or any other VBA-enabled application ...

Public Function GetFullName(LastName As String) As String

Dim strConnection As String
Dim objConnection As ADODB.connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rst As ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\USENET\Test.mdb;" & _
"Persist Security Info=False"
Set objConnection = New ADODB.Connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "qryTest"
objCommand.CommandType = adCmdStoredProc
Set objParam = objCommand.CreateParameter("[Last Name?]", _
adVarChar, adParamInput, 50, LastName)
objCommand.Parameters.Append objParam
Set rst = objCommand.Execute
GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
objConnection.Close

End Function

Result in the Excel VBA Immediate window ...
? getfullname("Davolio")
Davolio, Nancy

--
Brendan Reynolds

Junior728 said:
Hi Brendan,

yah...indeed sound kind of weird..i thought qns are answered by
programmers.=)by the way.thanks for your script. it sort of work a bit but
when i try to run it, there is a error message that says no value is given
for one or more parameters. i suspect it is my [currdate] field that
causes
this error. This field is not taken from the table, rather it is an input
prompt to the user. the user will then enter the date and the query will
run.


How can i improve my script to incorporate that? See my e.g.

Private Sub Worksheet_Activate()

Dim connection As ADODB.connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim intRow As Integer

strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open strSQL
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
"ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
"WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
"ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE]
DESC;"
rst.Source = strSQL
.Open
Do Until .EOF
intRow = intRow + 1
Sheet1.Cells(intRow, 1) = .Fields("Mfg")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

Junior728

Brendan Reynolds said:
I'm not sure that I understand the question. The fact that your query
includes an expression does not, as far as I can see, change anything.
You
just need to replace the "SELECT * FROM Employees" in my example with
your
SQL string. Of course you have to put the SQL string into a properly
formatted VBA string - enclose it within quotes, and either place it all
on
one line, or use the line continuation characters, a space followed by an
underscore at the end of continued lines. For example ...

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
"FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
"[CURRDATE]-14 DESC, [CURRDATE] DESC;"
rst.Source = strSQL

BTW: A couple of things about that query look a little odd. You have
"ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
actually produce a different result than sorting by CURRDATE alone? I
don't
see how it could, and sorting on CURDATE-14 is a lot less efficient than
sorting on CURRDATE alone, as the database engine will not be able to use
any index that might exist on the CURRDATE field. This can make a big
difference to performance, especially when working with large databases
across a network.

Another BTW: It doesn't really bother me, I'm not offended or anything,
but
'awaiting your prompt reply' isn't really very appropriate in a
peer-to-peer
setting. We're not Microsoft employees here, we're volunteers helping
each
other out. It's a bit like asking your neighbour for a favour, then
telling
them 'and be prompt about it!' :-)

--
Brendan Reynolds

hi Brendan,

Thanks for your script! however, my query has some expression
calculation
done within the query and i dont know how it can be written in vba
terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result
of
comparison. And i want to execute other fields in the database table
that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
[FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
DESC;

awaiting yourprompt reply,
Junior 728

:

Here's an example that fills a column in an Excel worksheet with a
list
of
names from the Employees table in the Northwind sample database.
You'll
need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library.
(If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Hi Sir,

i have used microsoft access to come up with the Query and this is
the
SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have?
how
can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
Click to expand...
 
Hi Brendan,

Thanks. But can i know how do i add this parameter function into my script?
Or do i enter under another module?

Junior728

Brendan Reynolds said:
That automatic, built-in prompt for parameters works only within Access. To
use the query in Excel, your Excel application will need to get the value
for the parameter from the user before executing the query. You could do
that by having the user enter the value in a cell in the Excel worksheet, by
using an Excel user-form, or by using the InputBox function. Once you have
the value, you can either build up the query in code as before or you can
use a pre-saved parameter query. Here's an example I posted recently in
response to a similar question ...

Below is the SQL for a saved parameter query in Access ...

PARAMETERS [Last Name?] Text ( 50 );
SELECT Employees.*
FROM Employees
WHERE (((Employees.LastName)=[Last Name?]));

.... and here is some VBA code to pass a variable as the parameter value, and
retrieve the result. This code would work identically from Access, Excel,
Word, or any other VBA-enabled application ...

Public Function GetFullName(LastName As String) As String

Dim strConnection As String
Dim objConnection As ADODB.connection
Dim objCommand As ADODB.Command
Dim objParam As ADODB.Parameter
Dim rst As ADODB.Recordset

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\USENET\Test.mdb;" & _
"Persist Security Info=False"
Set objConnection = New ADODB.Connection
objConnection.Open strConnection
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText = "qryTest"
objCommand.CommandType = adCmdStoredProc
Set objParam = objCommand.CreateParameter("[Last Name?]", _
adVarChar, adParamInput, 50, LastName)
objCommand.Parameters.Append objParam
Set rst = objCommand.Execute
GetFullName = rst.Fields("LastName") & ", " & rst.Fields("FirstName")
objConnection.Close

End Function

Result in the Excel VBA Immediate window ...
? getfullname("Davolio")
Davolio, Nancy

--
Brendan Reynolds

Junior728 said:
Hi Brendan,

yah...indeed sound kind of weird..i thought qns are answered by
programmers.=)by the way.thanks for your script. it sort of work a bit but
when i try to run it, there is a error message that says no value is given
for one or more parameters. i suspect it is my [currdate] field that
causes
this error. This field is not taken from the table, rather it is an input
prompt to the user. the user will then enter the date and the query will
run.


How can i improve my script to incorporate that? See my e.g.

Private Sub Worksheet_Activate()

Dim connection As ADODB.connection
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim intRow As Integer

strSQL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\Asia\Product\ADC Part Di\MeilingTesting.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open strSQL
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[CURRDATE] AS TODAY, [CURRDATE]-14 AS BACKDATE, " & _
"ADC_Parts.[ADD DATE]FROM ADC_Parts " & _
"WHERE (((ADC_Parts.[ADD DATE]) Between TODAY And BACKDATE))" & _
"ORDER BY [CURRDATE] DESC , [CURRDATE]-14 DESC , ADC_Parts.[ADD DATE]
DESC;"
rst.Source = strSQL
.Open
Do Until .EOF
intRow = intRow + 1
Sheet1.Cells(intRow, 1) = .Fields("Mfg")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

Junior728

Brendan Reynolds said:
I'm not sure that I understand the question. The fact that your query
includes an expression does not, as far as I can see, change anything.
You
just need to replace the "SELECT * FROM Employees" in my example with
your
SQL string. Of course you have to put the SQL string into a properly
formatted VBA string - enclose it within quotes, and either place it all
on
one line, or use the line continuation characters, a space followed by an
underscore at the end of continued lines. For example ...

Dim strSQL As String
Dim rst As ADODB.Recordset

strSQL = "SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], " & _
"[ADDDATE] Between [FINALDATE] And [CURRDATE] AS PARTAD " & _
"FROM ADC_Parts ORDER BY ADC_Parts.[ADD DATE] DESC, " & _
"[CURRDATE]-14 DESC, [CURRDATE] DESC;"
rst.Source = strSQL

BTW: A couple of things about that query look a little odd. You have
"ADDDATE" without a space, and "ADD DATE" with a space. Is one of those a
typo? Also, does sorting by CURDATE-14 as well as sorting by CURRDATE
actually produce a different result than sorting by CURRDATE alone? I
don't
see how it could, and sorting on CURDATE-14 is a lot less efficient than
sorting on CURRDATE alone, as the database engine will not be able to use
any index that might exist on the CURRDATE field. This can make a big
difference to performance, especially when working with large databases
across a network.

Another BTW: It doesn't really bother me, I'm not offended or anything,
but
'awaiting your prompt reply' isn't really very appropriate in a
peer-to-peer
setting. We're not Microsoft employees here, we're volunteers helping
each
other out. It's a bit like asking your neighbour for a favour, then
telling
them 'and be prompt about it!' :-)

--
Brendan Reynolds

hi Brendan,

Thanks for your script! however, my query has some expression
calculation
done within the query and i dont know how it can be written in vba
terms.
e.g. i want field "part ad" which is the result of any date between the
adddate and final date.

this "part ad" is not a date field from the table but rather the result
of
comparison. And i want to execute other fields in the database table
that
falls in between this criteria.

How can this be done?

Example from SQL view:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], [ADDDATE] Between
[FINALDATE]
And [CURRDATE] AS PARTAD
'FROM ADC_Parts
'ORDER BY ADC_Parts.[ADD DATE] DESC , [CURRDATE]-14 DESC , [CURRDATE]
DESC;

awaiting yourprompt reply,
Junior 728

:

Here's an example that fills a column in an Excel worksheet with a
list
of
names from the Employees table in the Northwind sample database.
You'll
need
to add a reference to the Microsoft ActiveX Data Objects 2.x Library.
(If
you're more familiar with DAO, the code is easily adapted to use DAO
instead.) To add a reference, open the Excel VBA editor, and select
References from the Tools menu.

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DSDATA\Northwind.mdb;" & _
"Persist Security Info=False"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub

--
Brendan Reynolds


Hi Sir,

i have used microsoft access to come up with the Query and this is
the
SQL
line:

SELECT ADC_Parts.Mfg, ADC_Parts.[Part Number], ADC_Parts.[ADD DATE],
DateAdd("d",-14,[CURRDATE]) AS FINALDATE
FROM ADC_Parts;

But i wish to combine this in my existing excel macro that i have?
how
can
i
do that? can SQL and VBA language cross each other in a module?

Awaiting ur prompt reply,
10/24/05
Click to expand...
Click to expand...
Click to expand...
 
Back
Top