SQL staments with ADO in Excell

**Jeff Dillon** · Oct 24 '06, 09:05 PM

Re: SQL staments with ADO in Excell

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

Jeff
"jamie" <jamiedora@yaho o.comwrote in message
news:1161724066 .034705.247850@ f16g2000cwb.goo glegroups.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.Connectio n
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 InquiryFinancia ls.ProposedCost ,
InquiryHeader.B ooking_Date, InquiryFinancia ls.ProposedPric e " _
& "FROM (InquiryHeader INNER JOIN InquiryStatus ON
InquiryHeader.I nquiry_Number = InquiryStatus.I nquiry_Number) " _
& "INNER JOIN InquiryFinancia ls ON InquiryStatus.I nquiry_Number =
InquiryFinancia ls.Inquiry_Numb er, " _
& "(Districts INNER JOIN Regions ON Districts.Regio n = Regions.Region)
" _
& "INNER JOIN RegionGroups ON Regions.Regions ID = RegionGroups.Re gionID
" _
& "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.Re gionsGroup = 1 " _
& "And (InquiryHeader. Budgetary_Firm) = 'firm' " _
& "And (InquiryHeader. Booking_Date) >= " & currDate _
& "And Districts.Distr ict = Left((InquiryHe ader.Inquiry_Nu mber), 2)"
>
>
Set db = New ADODB.Connectio n
Set rst = New ADODB.Recordset
>
SqlStatments
strSql = strSql0
>
dbName = "\CSInquiries_v 22.mdb"
dbPath = "S:\CSCPROJ\PIL OT\Master"
db.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & dbPath & dbName & ";"
>
>
rst.Open strSql, db, adOpenKeyset, adLockOptimisti c
rst.MoveFirst
>
Cost = rst![ProposedCost]
>
*************** *************** *************** *
>

**Tim Patrick** · Oct 24 '06, 09:05 PM

Re: SQL staments with ADO in Excell

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

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.Connectio n
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 InquiryFinancia ls.ProposedCost ,
InquiryHeader.B ooking_Date, InquiryFinancia ls.ProposedPric e " _
& "FROM (InquiryHeader INNER JOIN InquiryStatus ON
InquiryHeader.I nquiry_Number = InquiryStatus.I nquiry_Number) " _
& "INNER JOIN InquiryFinancia ls ON InquiryStatus.I nquiry_Number =
InquiryFinancia ls.Inquiry_Numb er, " _
& "(Districts INNER JOIN Regions ON Districts.Regio n = Regions.Region)
" _
& "INNER JOIN RegionGroups ON Regions.Regions ID =
RegionGroups.Re gionID
" _
& "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.Re gionsGroup = 1 " _
& "And (InquiryHeader. Budgetary_Firm) = 'firm' " _
& "And (InquiryHeader. Booking_Date) >= " & currDate _
& "And Districts.Distr ict = Left((InquiryHe ader.Inquiry_Nu mber), 2)"
Set db = New ADODB.Connectio n
Set rst = New ADODB.Recordset
SqlStatments
strSql = strSql0
dbName = "\CSInquiries_v 22.mdb"
dbPath = "S:\CSCPROJ\PIL OT\Master"
db.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & dbPath & dbName & ";"
rst.Open strSql, db, adOpenKeyset, adLockOptimisti c rst.MoveFirst
>
Cost = rst![ProposedCost]
>
*************** *************** *************** *
>

**Paul Clement** · Oct 25 '06, 02:55 PM

Re: SQL staments with ADO in Excell

On 24 Oct 2006 14:07:46 -0700, "jamie" <jamiedora@yaho o.comwrote:

¤ 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

Error - Office.com - Microsoft Support

http://support.microsoft.com/kb/239482

Paul
~~~~
Microsoft MVP (Visual Basic)

SQL staments with ADO in Excell

SQL staments with ADO in Excell

Comment

Comment

Comment