ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Access Query from Excel VBA (https://www.excelbanter.com/excel-programming/412761-access-query-excel-vba.html)

omsoft
Access Query from Excel VBA
 
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND ((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID = tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.

Harald Staff[_2_]
Access Query from Excel VBA
 
If I read this right; wildcard in SQL is % , not *

Best wishes Harald

"omsoft" wrote in message
...
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND
((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.


omsoft
Access Query from Excel VBA
 
I do not believe that is true.

"Harald Staff" wrote:

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

"omsoft" wrote in message
...
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND
((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.



Sam Wilson
Access Query from Excel VBA
 
It is true... why are you looking for LIKE '*' anyway? That will return
everything?


"omsoft" wrote:

I do not believe that is true.

"Harald Staff" wrote:

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

"omsoft" wrote in message
...
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND
((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.



omsoft
Access Query from Excel VBA
 

I am sorry, my mistake. you are both right.

% does work. Somehow (I do not know how) I have used * and ? for wild cards
and have always worked.
Sorry about that.

BTW, LIKE * is to indeed return everything.
But in this case * is the value fo a string variable, which could be either
set to a specific value or * if the user wanted to report on all divisions.

The query works correctly in Access directly with * so I assumed that it
should work in VBA calling from Excel.

"Sam Wilson" wrote:

It is true... why are you looking for LIKE '*' anyway? That will return
everything?


"omsoft" wrote:

I do not believe that is true.

"Harald Staff" wrote:

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

"omsoft" wrote in message
...
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND
((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.



Sam Wilson
Access Query from Excel VBA
 
* and ? work in Access but not in SQL proper. I've never worked out why
Access is different but there we go.


"omsoft" wrote:


I am sorry, my mistake. you are both right.

% does work. Somehow (I do not know how) I have used * and ? for wild cards
and have always worked.
Sorry about that.

BTW, LIKE * is to indeed return everything.
But in this case * is the value fo a string variable, which could be either
set to a specific value or * if the user wanted to report on all divisions.

The query works correctly in Access directly with * so I assumed that it
should work in VBA calling from Excel.

"Sam Wilson" wrote:

It is true... why are you looking for LIKE '*' anyway? That will return
everything?


"omsoft" wrote:

I do not believe that is true.

"Harald Staff" wrote:

If I read this right; wildcard in SQL is % , not *

Best wishes Harald

"omsoft" wrote in message
...
I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND
((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.



Harald Staff[_2_]
Access Query from Excel VBA
 
I believe it's in a layer of "user friendliness" in Jet. Didn't want to
start that discussion in my reply (after being proved wrong in several
similar theories lately, probably will this time too :-)

Best wishes Harald


"Sam Wilson" wrote in message
...
* and ? work in Access but not in SQL proper. I've never worked out why
Access is different but there we go.


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com