SQL staments with ADO in Excell

microsoft.public.dotnet.languages.vb

24 Oct 2006 9:07 PM

jamie

Hello,

I have a SQL question with regards to retrieving data from an Access
DB.

When I use this querry in Access, it works fine, but when I try to get
data, from Access, using the querry it does not reocnize functions like
sum(), left(), or Date().

Can one use a SQL string, just as in Access, from Excell ADO?

I am using the following code snipt, I tried to make a relationship
with the left function:

************************************
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnect, strSql As String
Dim Price, Cost As Double
Dim currDate, currDateFuture As Date

currDate = FormatDateTime(Now, vbShortDate)

strSql0 = "SELECT InquiryFinancials.ProposedCost,
InquiryHeader.Booking_Date, InquiryFinancials.ProposedPrice " _
& "FROM (InquiryHeader INNER JOIN InquiryStatus ON
InquiryHeader.Inquiry_Number = InquiryStatus.Inquiry_Number) " _
& "INNER JOIN InquiryFinancials ON InquiryStatus.Inquiry_Number =
InquiryFinancials.Inquiry_Number, " _
& "(Districts INNER JOIN Regions ON Districts.Region = Regions.Region)
" _
& "INNER JOIN RegionGroups ON Regions.RegionsID = RegionGroups.RegionID
" _
& "WHERE (InquiryStatus.Inquiry_Won) = True " _
& "And (InquiryStatus.Complete_PPC) = False " _
& "And (InquiryStatus.Inquiry_Lost) = False " _
& "And (InquiryStatus.Inquiry_Dead) = False " _
& "And (InquiryStatus.InActive) = False " _
& "And RegionGroups.RegionsGroup = 1 " _
& "And (InquiryHeader.Budgetary_Firm) = 'firm' " _
& "And (InquiryHeader.Booking_Date) >= " & currDate _
& "And Districts.District = Left((InquiryHeader.Inquiry_Number), 2)"

Set db = New ADODB.Connection
Set rst = New ADODB.Recordset

SqlStatments
strSql = strSql0

dbName = "\CSInquiries_v22.mdb"
dbPath = "S:\CSCPROJ\PILOT\Master"
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & dbPath & dbName & ";"

rst.Open strSql, db, adOpenKeyset, adLockOptimistic
rst.MoveFirst

Cost = rst![ProposedCost]

**********************************************

24 Oct 2006 9:15 PM

Jeff Dillon

Those are built-in Access functions..that only work in Access.

Jeff
Show quoteHide quote

"jamie" <jamied***@yahoo.com> wrote in message
news:1161724066.034705.247850@f16g2000cwb.googlegroups.com...
> Hello,
>
> I have a SQL question with regards to retrieving data from an Access
> DB.
>
> When I use this querry in Access, it works fine, but when I try to get
> data, from Access, using the querry it does not reocnize functions like
> sum(), left(), or Date().
>
> Can one use a SQL string, just as in Access, from Excell ADO?
>
>
> I am using the following code snipt, I tried to make a relationship
> with the left function:
>
> ************************************
> Dim db As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim strConnect, strSql As String
> Dim Price, Cost As Double
> Dim currDate, currDateFuture As Date
>
> currDate = FormatDateTime(Now, vbShortDate)
>
> strSql0 = "SELECT InquiryFinancials.ProposedCost,
> InquiryHeader.Booking_Date, InquiryFinancials.ProposedPrice " _
> & "FROM (InquiryHeader INNER JOIN InquiryStatus ON
> InquiryHeader.Inquiry_Number = InquiryStatus.Inquiry_Number) " _
> & "INNER JOIN InquiryFinancials ON InquiryStatus.Inquiry_Number =
> InquiryFinancials.Inquiry_Number, " _
> & "(Districts INNER JOIN Regions ON Districts.Region = Regions.Region)
> " _
> & "INNER JOIN RegionGroups ON Regions.RegionsID = RegionGroups.RegionID
> " _
> & "WHERE (InquiryStatus.Inquiry_Won) = True " _
> & "And (InquiryStatus.Complete_PPC) = False " _
> & "And (InquiryStatus.Inquiry_Lost) = False " _
> & "And (InquiryStatus.Inquiry_Dead) = False " _
> & "And (InquiryStatus.InActive) = False " _
> & "And RegionGroups.RegionsGroup = 1 " _
> & "And (InquiryHeader.Budgetary_Firm) = 'firm' " _
> & "And (InquiryHeader.Booking_Date) >= " & currDate _
> & "And Districts.District = Left((InquiryHeader.Inquiry_Number), 2)"
>
>
> Set db = New ADODB.Connection
> Set rst = New ADODB.Recordset
>
> SqlStatments
> strSql = strSql0
>
> dbName = "\CSInquiries_v22.mdb"
> dbPath = "S:\CSCPROJ\PILOT\Master"
> db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & dbPath & dbName & ";"
>
>
> rst.Open strSql, db, adOpenKeyset, adLockOptimistic
> rst.MoveFirst
>
> Cost = rst![ProposedCost]
>
> **********************************************
>

24 Oct 2006 9:22 PM

Tim Patrick

I would guess that the problem lies in the currDate variable. When you embed
it into the SQL statement, you embed it as is. But in Access, dates must
be surrounded by "#" characters, as in "#01-Jan-1980#".

Also, there are rules by which JET preprocesses SQL statements sent through
ADO, but it's frankly too long ago since I've used JET for me to remember.

-----
Tim Patrick
Start-to-Finish Visual Basic 2005

Show quoteHide quote

> Hello,
>
> I have a SQL question with regards to retrieving data from an Access
> DB.
>
> When I use this querry in Access, it works fine, but when I try to get
> data, from Access, using the querry it does not reocnize functions
> like sum(), left(), or Date().
>
> Can one use a SQL string, just as in Access, from Excell ADO?
>
> I am using the following code snipt, I tried to make a relationship
> with the left function:
>
> ************************************
> Dim db As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim strConnect, strSql As String
> Dim Price, Cost As Double
> Dim currDate, currDateFuture As Date
> currDate = FormatDateTime(Now, vbShortDate)
>
> strSql0 = "SELECT InquiryFinancials.ProposedCost,
> InquiryHeader.Booking_Date, InquiryFinancials.ProposedPrice " _
> & "FROM (InquiryHeader INNER JOIN InquiryStatus ON
> InquiryHeader.Inquiry_Number = InquiryStatus.Inquiry_Number) " _
> & "INNER JOIN InquiryFinancials ON InquiryStatus.Inquiry_Number =
> InquiryFinancials.Inquiry_Number, " _
> & "(Districts INNER JOIN Regions ON Districts.Region = Regions.Region)
> " _
> & "INNER JOIN RegionGroups ON Regions.RegionsID =
> RegionGroups.RegionID
> " _
> & "WHERE (InquiryStatus.Inquiry_Won) = True " _
> & "And (InquiryStatus.Complete_PPC) = False " _
> & "And (InquiryStatus.Inquiry_Lost) = False " _
> & "And (InquiryStatus.Inquiry_Dead) = False " _
> & "And (InquiryStatus.InActive) = False " _
> & "And RegionGroups.RegionsGroup = 1 " _
> & "And (InquiryHeader.Budgetary_Firm) = 'firm' " _
> & "And (InquiryHeader.Booking_Date) >= " & currDate _
> & "And Districts.District = Left((InquiryHeader.Inquiry_Number), 2)"
> Set db = New ADODB.Connection
> Set rst = New ADODB.Recordset
> SqlStatments
> strSql = strSql0
> dbName = "\CSInquiries_v22.mdb"
> dbPath = "S:\CSCPROJ\PILOT\Master"
> db.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & dbPath & dbName & ";"
> rst.Open strSql, db, adOpenKeyset, adLockOptimistic rst.MoveFirst
>
> Cost = rst![ProposedCost]
>
> **********************************************
>

25 Oct 2006 3:10 PM

Paul Clement

On 24 Oct 2006 14:07:46 -0700, "jamie" <jamied***@yahoo.com> wrote:

¤ Hello,
¤
¤ I have a SQL question with regards to retrieving data from an Access
¤ DB.
¤
¤ When I use this querry in Access, it works fine, but when I try to get
¤ data, from Access, using the querry it does not reocnize functions like
¤ sum(), left(), or Date().

The Left and Date function are valid for use in a SQL statement. SUM is a SQL keyword. The only
function I see in your SQL statement is Left and the syntax looks to be OK.

Of course some functions are not available, including VBA functions. I would check the following to
make sure Sandbox mode is enabled:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2000 and Access 2002
http://support.microsoft.com/kb/239482

Paul
~~~~
Microsoft MVP (Visual Basic)