SQL staments with ADO in Excell

  • Thread starter Thread starter jamie
  • Start date Start date
J

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]

**********************************************
 
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.
 
¤ 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)
 
Back
Top