Solved

SQL as recordset in VB

Posted on 1998-09-15
8
375 Views
Last Modified: 2012-08-14
I am using a SQL to query and Access DB.  I want this data to be the chart Data for MSCHART.  What is wrong with this?

Dim WS As DAO.Workspace
Dim DB As DAO.Database
 ' Initialize a default workspace
Set WS = CreateWorkspace("TestWS", "Admin", "", dbUseJet)
Set DB = WS.OpenDatabase("c:\windows\desktop\wkformula.mdb")
Set wb = DB.OpenRecordset("CMLtable")
With wb
Dim ol As Recordset
Dim STRSQL
STRSQL = "SELECT OSBAL FROM CMLTABLE WHERE YEAR='1998'"
Set ol = DB.OpenRecordset(STRSQL)
Me!MSChart1.ChartData = ol

Thanks
0
Comment
Question by:dhill10358
8 Comments
 
LVL 14

Expert Comment

by:waty
Comment Utility
Does your field YEAR a string or date field?
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
What is the error message you are getting?
0
 

Author Comment

by:dhill10358
Comment Utility
Hi,

It gives me a runtime error.

The Year is actually in the Access table, but it is a text field. I know the sql statement is ok, but I get "Bad Function Argument"  Runtime error 1101.

Any Suggestions?
0
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
I've commented out what I don't think you need, and modified it to what should work:

'Set wb = DB.OpenRecordset("CMLtable")
'With wb
Dim ol As Recordset
Dim STRSQL
STRSQL = "SELECT OSBAL FROM CMLTABLE WHERE YEAR='1998'"
Set ol = DB.OpenRecordset(STRSQL)
if not ol.eof then
 Me!MSChart1.ChartData = ol(0) 'or ol("OSBAL") or ol!OSBAL
endif
ol.close
etc...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:dhill10358
Comment Utility
Thanks for you help, but I get the exact same error message, the only difference is when I try to debug, the OL(0) or ol("osbal") has a value now.  But it has the same error msg.

Thanks
0
 

Expert Comment

by:balrogzed
Comment Utility
I think you may need to change your sql statement to use double quotes not single quotes. ie change
STRSQL = "SELECT OSBAL FROM CMLTABLE WHERE YEAR='1998'"
to
STRSQL = "SELECT OSBAL FROM CMLTABLE WHERE YEAR=""1998"""
0
 

Author Comment

by:dhill10358
Comment Utility
Thanks, but that is not the problem.  When I am debugging I can see the results of the SQL. I use the exact same statement in to append a recordset to excel, and it works.

Thanks
0
 
LVL 3

Accepted Solution

by:
TheAnswerMan earned 80 total points
Comment Utility
IS that a forward only Recordset?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now