Query Works In Access Query But Not Through ADO??

Query Works In Access But Not In VB
I have the following query that works fine if I cut/pase into an SQL query in access, but when executed in VB the loop is skipped - ie, no data is returned.

Has to be something simple

I am running Vb in Visual Studios 2008

Form1.GlobalVars.myDbCommand.CommandText = _
"SELECT count(rec.MemberId), def.SupTrainingDescr " & _
"FROM DEF_SUPTRAINING AS def, def_suptrainingclass AS dc,, _rec_suptraining AS rec " & _
"WHERE rec.supclassid = dc.supclassid And dc.suptrainingid = def.suptrainingid And " & _
"def.suptrainingdescr Like 'IS * CERTIFICATE' " & _
"GROUP BY def.suptrainingdescr"

myDataReader = Form1.GlobalVars.myDbCommand.ExecuteReader

Do While (myDataReader.Read())

iCount = myDataReader.GetInt32(0)
WorkString = myDataReader.GetString(1)

iRow = iRow + 1

oWS.Cells(iRow, 1) = iCount
oWS.Cells(iRow, 2) = WorkString

Loop

---

LIKE Query Works In Access, Not In VB
Hi. I'm using VB6 on an Access 97 db.
My code is:

Code:
Private Sub txtSearch_Change()
Dim rs As New ADODB.Recordset
dim sSql as string

sSql = "SELECT nameCust FROM tblCustomers WHERE namecust LIKE '*" & txtSearch & "*' " _
& "ORDER BY namecust"
rs.Open sSql, cn, adOpenKeyset, adLockPessimistic
'use the rs to populate a listbox
End Sub
The immediate window shows the value of sSql as: "SELECT nameCust FROM tblCustomers WHERE namecust LIKE '*h*' ORDER BY namecust". When I create a query in the Access db using this string, it works fine. When I change the sql in the VB to choose all of tblCustomers, it also works fine, so the connection isn't the problem.

I'm totally stumped. What vital piece of information do I not know here???

Your help much appreciated,
Sharon

---

SQL Query Works In Access But Not VB
Hey, i got my sql from an MS Access query. It works absolutely perfect in Access but when i use it as my recordset source in VB it throws the error:

"Method 'Open' of object '_Recordset' failed"

I cant see why it works fine in Access and not in my program. Heres the sql statement, just incase you notice anything out of place, i cut and paste it from access so should be the same.


VB Code:
If RS.State = adStateOpen Then    RS.CloseEnd If RS.Source = "SELECT Person.Name, Booking.BookingID, Event.Name, Person.Position FROM Event INNER JOIN (Person INNER JOIN Booking ON Person.PersonID = Booking.Name) ON Event.EventID = Booking.Event WHERE (((Event.Name)='" & newstring & "'))"RS.Open

---

Query Works In Access But Not In VB6
This query workd well in access until I add the last date criteria in the where clause

QUERY:

VB Code:
SELECT distinct tblDebtor.ipkDebtorId, tblDebtor.vFirstName, tblDebtor.vSecondName,tblDebtor.vSurname, tblDebtor.vIDNumber, tblDebtor.vAddress1,tblDebtor.vAddress2 , tblMatter.ifkDebtorId FROM (tblMatter INNER JOIN tblCall ON tblMatter.ipkMatterId = tblCall.ifkMatterId) INNER JOIN (tblDebtor INNER JOIN tblUsers ON tblDebtor.ifkUserId = tblUsers.ipkUserId) ON tblMatter.ifkDebtorId = tblDebtor.ipkDebtorId WHERE tblMatter.iActive=True AND tblMatter.Allocated=True AND tblUsers.ipkUserId=39 --Worked OK in Access and VB



VB Code:
AND tblCall.dtCallDate BETWEEN #03/07/26# AND #03/08/01#--Failed in VB: Returns no records


Does A-N-Y-B-O-D-Y know how the heck what is going on here. This has to be working by tomorrow and I am stumped!

---

Why Does This Access SQL Query Works On All PC's Except My Own
Hi,

I have the following query:

DoCmd.RunSQL "SELECT * INTO [Excel 8.0;DATABASE=" & txtDestination.Value & "].[data] FROM [tbl_6_Month_Average];"

It essentially dumps an access table into an excel sheet. This works just right on every PC except my own. The problem is that if there are any null values in the access table, they don't go into the spreadsheet as an empty cell - thay are omitted and columns to the right of the null value are shifted one over. eg

a b c
d    e
f g h

becomes

a b c
d e
f g h

Anyone know what I can do to fix this?

Thanks.

---

SQL String Works In Access Query But Not In ADO
Need help, I wrote a sql string that works well in Access query, but when I try to download the result in an ADO recordset the output is different than one from the query.

Basically, the table "tblRelationship" has 3 main fields, "Parent_Part_Number," "Component_Part_Number," and "Component_Requirement."

What I want to do is to download distinct "Parent_Part_number" and distinct "Component_Part_Number" that meets certain criteria

Again, if I were to run the sql string in the built in query tool in Access, the output is correct: only showing distinct part numbers that meet the criteria

Once, downloaded, it shows distinct part numbers but also include the ones that do not meet the criteria.

Please, shed some light if you can.

---

SQL Select Works With ADO But Not In Access Query Window
I have an sql select that works fine when I run it with ado from my VB6 program. However, when I try to run the same select statement in the Access DB that is called by the ADO in the VB6 program, I get a circular reference error.

Does that make any sense?

Here is the sql statement:

SELECT Format(SFTimingFrom,'MM/DD/YY') & ' - ' & Format(SFTimingTo,'MM/DD/YY') as SFTimingFrom ,Right(SFTxt,(Len(SFTxt)-LenFileExtName)) as SFTxt,SFImpression ,SFElement, SFDetail FROM SponsorCatAndFileInName WHERE SFTxt <>'' AND CatID = 22 AND NameID = 13 AND SponsorID = 56 ORDER BY SFTxt ASC

Here is the ADO that calls it and works just fine:

Set myRS = New adodb.Recordset
Set myRS.ActiveConnection = adoconn
If (pagesize > 0) Then
myRS.pagesize = pagesize
End If
myRS.CursorLocation = adUseClient
myRS.Open sqlTxt

Here is the error I get when I run it in the Access query window:

"Circular reference caused by alias 'SFTimingFrom' in query definitions select list"

---

Access 97 DB Query Works In Access 2000 But Not In Access 97
I have a MS Access 97 DB that worked but when I came to my new company the DB has problems with a particular query on my new PC. The new PC(Win98) has office 97, therefore Access 97. When I try the same DB on a co-workers PC (also Win98) which has Office 2000 installed I do not encounter this problem. The following is the error message that I get: Function isn't available in expressions in query expression '||f(IsNull([Close Date]),Date(),[Close Date])'

Does anyone know what the problem is?

Thanks

---

Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.

What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? Thanks.

---

Time Elapsed In SQL Query In VB Vrs SQL Query In Access
Hi guys,

Should it be normal for a SQL query in VB to take longer than in Access. Both queries are out to a SQL server and it is taking 30 seconds for MS Access and almost 4 minutes for VB. Is there anything I am missing or are there different engines in each of the SQL queries?

Thanks a lot,

- George

---

SQL Query Works On XP System, Not On NT
I have a program I am developing on a computer at my home that is running winXP. This particular SQL query works fine on it. (It returns 50 records right now.) The program is to be used at work, where my computer is running winNT. The query does not work on it! It returns 0 records. (this query should return 50 records, as that is how many in the DB match the criteria.)

sqlStr = SELECT Transactions.* FROM Transactions, Corrections WHERE Transactions!ID = Corrections!Transaction AND Corrections!CorrectionData LIKE '%Model # Should Be: PV-C1341%'

Is anyone aware of differences in how the MSJet database engine handles SQL between winXP & winNT?

Could my problem be the colon after "Should Be"?

This is really frustrating, as I must debug it by making a correction at home in the IDE, then going to work the next day to try it out.

Thanks in advance!
Randtek

---

SQL Works In Query Analyzer Not In ADO
The SQL statement below works fine when I use the MS SQL 2000 Query Analyzer but when I try it in the VB IDE using the Execute method of a Connection object in ADO 2.5 I get a Null value inserted into my table instead of the @@Identity. Any clue why that is?

Insert into TestTbl (Hello) Values (@@Identity)

The example above is not a simplified version of something more complicated that I am trying to do, but more of a proof of concept before I even attemp anything more complicated.

Thanks in advance

---

Query Works But Has Bugs.
hey,
I am using this query to get data from an excel sheet and put into a table in access database.but the problem is access is not recognizing my column names/headers, access is assigning default values (F1 to F37).how can i get my column names into the access tables..any help would be appreciated..thanks

sqlString1 = "SELECT * INTO [GAS] FROM [Excel 8.0;DATABASE=C:Documents and Settings03985Desktopproject ranz.xls;HDR=No].[GAS TRADES$]"

---

Ocx That Works Like A Query Builder
I am working on a Patient's Monitoring system. My client asked me if I can include a module in my program wherein they can make a query builder sort of thing. Somebody told me that there is an ocx in Windows that works like a query builder wherein all you have to do is to set the data source and you can build query statements just by selecting fields and choosing from list of criteria. Somebody please help me. Thanks

---

Check Out This ADO Query Pls! (but If Ne1 Gets Why This Works He's A God)
I got this. It worked once and never since:

the dsn I use is a standard one I found in my dsns.
could the conn be the prob? But why will I get an empty recordset with the correct number of fields and fieldnames?


VB Code:
Sub Test() Dim strConnTxt As StringDim strSourceFile As StringDim intcount As IntegerDim intParVal As IntegerDim cmd As ADODB.CommandDim rstQ As ADODB.RecordsetDim strQry As StringDim par As ADODB.Parameter  strSourceFile = App.Path & "xltemp.xls"Set conn = New ADODB.ConnectionstrConnTxt = "DSN=excel files;DBQ=" & strSourceFile & ";DefaultDir=" & App.Path & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" conn.ConnectionString = strConnTxtconn.Open Set cmd = New ADODB.CommandSet rstQ = New ADODB.Recordset strQry = "SELECT SPSS.* FROM SPSS;" Set cmd.ActiveConnection = conn cmd.CommandText = strQry   Set rstQ = cmd.Execute '(Parameters:=Array(1))Debug.Print rstQ.RecordCountDebug.Print rstQ.Fields.Count rstQ.CloseSet rstQ = NothingSet rstQ = New ADODB.RecordsetrstQ.ActiveConnection = connrstQ.Source = strQryrstQ.OpenDebug.Print rstQ.RecordCountDebug.Print rstQ.Fields.Count End sub


The funny thing is it always retrieves something: a recordset with no records but the correct fieldcount and fieldnames.

Why can't I get any data??

thx,

Helger

---

Sql Query Fails For Mdb But Works For Mysql?
I created an app which uses a Mysql db as back-up. I want to make it possible to use a Access db as back-up as well.
I've got this query which worked when using de odbc connection to a MySql db but fails when connected to an Access db

the query:
strRst_SQL = "SELECT ma_id, pl_a_id, CONCAT(t_players.firstname,' ' ,t_players.lastname) As playerA, t_matches.pl_b_id, CONCAT(t_players2.firstname,' ' ,t_players2.lastname) As playerB, po_id, round, pl_a_stat, pl_b_stat, pl_a_po_id, pl_b_po_id, time_sched, sched_court FROM t_matches " _
& " INNER JOIN t_players ON t_matches.pl_a_id = t_players.id INNER JOIN t_players as t_players2 ON t_matches.pl_b_id = t_players2.id WHERE pl_a_stat > 0 and pl_b_stat > 0 and pl_a_stat <> 2 and pl_a_stat <> 3 ORDER BY time_sched ,round DESC, po_id ASC "

I got the feeling it has something to do with the fictive tabel "t_players".
What are the limitations using Access (regarding the querys) and how can I solve these?

---

RESOLVED - Query Works In Many Programs, But Not VB
Why does this query work in SQL Plus but not in VB?
Is it my outer joins?


RESOLVED

---

The Ascending Query Code Works....
Here's is the working code:

SELECT DataStudentBioAcademic.LastNameCandidate
FROM DataStudentBioAcademic
WHERE ((( DataStudentBioAcademic.LastNameCandidate) >= "A"))
ORDER BY DataStudentBioAcademic.LastNameCandidate;

This is the specific query code itself. Now, all there is to ge it fitting into the VB code and see how it goes. I want t thank everyone for their postings. I know I seemed difficul with all of this, but I'm still a BIG newbie with all of thi and I do appreciate your postings. I'll let you all know when get the Order button working.

---

Query Works Directly In Accessdb But Not Through ADO From VB
Hi,

Please help me on this.

I have query which can be run from the accessdb and getting the desired reult but when the same is fired from vb using ADO, it return zero records.

The sql is like this

"select id from tblTable where status like '*|*|*|*|*' ",

am I doing anything wrong?.

CursorType = adOpenKeyset
LockType = adLockOptimistic
settings are used with the recordset.

Thanks in advance.

---

Access Query Vs VBA Query Question
I have some code (Listing 1 below) that produces the following:

SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason
FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum
WHERE (((tblECOMain.JobNo)=99999) AND ((tblECOList.ManufacturedBy)<>'METRO MACHINE') AND ((tblECOList.SubChg)=False) AND (([NewQty]-[OldQty])<0) AND ((tblECOList.ECOReturnProc)=True));

The query runs great, however when run from the command button in VBA for Access it produces the rs.BOF or rs.EOF condition which goes to a “no records to process” condition. The purpose of the code is to simple check for records! I select records from a form via the "((tblECOList.ECOReturnProc)=True));" condition. If any are selected, then lets perform on those records later.

The initial form (which allows me to check my values) comes directly from a query, not code. The code basically reproduces that same query, but with the "((tblECOList.ECOReturnProc)=True));" set to true, meaning that these are the specific records I want to deal with. When I check the box, the control source is the field ECOReturnProc, for my check box control chkECOReturnProc.

I also tried referring to the control in code instead, so that the last line read “(Me.chkECOReturnProc = True));” – but this produced a different error. A runtime error of:

-2147217904(80040e10) – No value given for one or more required parameters.

I’d prefer to do this on read only, forward only – since I’m doing nothing to the data in this segment – but have changed to open keyset, lock optimistic because I’ve had similar problems before. There is no difference.

I’d have to say my confusion level is on the rise. I’ve done this exact thing elsewhere, in several places – with no problems. I believe the code is representatively correct because the debug.print produces valid syntax. Sad to say I spent the whole day on this yesterday, and it really is something that should take 30 minutes.

Any help would be greatly appreciated!


Listing 1:
sql = "SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, " & _
"tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, " & _
"tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason " & _
"FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOMain.JobNo)= " & [Forms]![frmReportsListing]![cboJN] & ") AND" & _
"((tblECOList.ManufacturedBy) <> " & Chr(39) & "METRO MACHINE" & Chr(39) & ") AND " & _
"((tblECOList.SubChg)=False) AND " & _
"(([NewQty]-[OldQty])<0) AND " & _
"((tblECOList.ECOReturnProc)=True));"

Debug.Print "SQLCHK: "; sql
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.BOF Or rs.EOF Then GoTo Skip1

---

SQL Query From Access Query Vs Table
Hello!

Will a simple SQL query from an access query be much slower that one from a table:

SELECT * FROM qryTest (qryTest = "SELECT Name, Adress from tblTest")

vs

SELECT Name, Adresss FROM tblTest

noccy

---

MySQL Query Vs Access Query
Ok so I made the switch finally after some headache but am finding query speeds are so much faster than Access. I am running into a problem with one of my Queries.

Access = #DateValue#
mySQL = 'DateValue'

Is that correct? Doug I believe you posted the wrapper for Access using the number sign, do I need to switch it back now or am I going about this wrong?

---

Query That Works Like Select Into W/o Overwriting The Table
I am trying to select data from a table and dump it into another. My problem is that every time I run the Select Into query, it overwrites all the old data in that other table. I want to be able to Select Into a table and ADD the info to the other table rather than having it overwrite the whole table each time.

For example, if my database is used everyday, I want some of that day's info dumped into another table that stores up to 3 days worth of old info. Is there a way to do this?

---

Error In SQL ADO, Works Fine In Query Analyzer
I am having an issue using a function in my SQL statement, but it works 100% fine in Query Analyzer.

I am running SQL Server, using ADO in VB6.

What I am trying to accomplish is to encrypt credit card numbers that I insert into the database. I have a function that will do this for me, and when run in query analyzer, it runs perfectly (I copied & pasted the exact SQL from a debug.print)

When I attempt to run it through code in VB, I get the error:

Run-time error '-2147217900 (80040e14)':

The name 'fn_sqEncrypt22' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Any help is appreciated

---

Query Works In SQL Analyser But Fails From VB. - Need Help From Experts.
Hi All, following is the query which is unable to run from my application. It returns with an error "
The Conversion of a char data type to a datetime datatype resulted in an out-of-range datetime value.



SELECT DISTINCT SCODE,RNO,CO_NAME,COORDNAME,USERNAME,MAX(DATE_OF_F) FROM JS
WHERE COORDNAME='XYZ' AND USERNAME='PETER' AND DATE_OF_IN BETWEEN '2005/10/06 00:00:00'
AND '2005/10/18 23:59:59' GROUP BY SCODE,RNO,CO_NAME,COORDNAME,USERNAME



Pl. need help urgently.

Thanks



.


.........
When you are laughing, you are in a state of meditation.
b'caz laughing and thinking cannot be done together.

---

Stored Procedure Fails But Query Analyzer Works?
Code:
SELECT TOP 100 PERCENT Tb2.MemNo, Tb3.LastContributionDate, Tb3.TimesContributed, Tb3.ContributionsTotal,
Tb2.Principal, Tb2.Agency, Tb2.Address1,MemberType, dbo.[tblLOOKUP-County].County FROM dbo.tblProspectMember Tb2 LEFT OUTER JOIN
(SELECT DISTINCT
(Tb1.Memno) AS NewMemno, MAX(Tb1.ContributionDate) AS LastContributionDate, COUNT(ContributionAmt) AS TimesContributed,
SUM(ContributionAmt) AS ContributionsTotal
FROM TblPacContributions Tb1
WHERE ContributionAmt > 0 AND Year(tb1.ContributionDate) != '1991'
GROUP BY Tb1.Memno) Tb3 ON Tb3.NewMemno = Tb2.MemNo
INNER JOIN dbo.[tblLOOKUP-County] ON Tb2.COC = dbo.[tblLOOKUP-County].ID
WHERE (Tb2.MemberType IN ('C', 'E', 'F', 'G', 'L', 'N') AND ASN = '31')
ORDER BY Tb2.MemNo

For SOme Reason When I drop this into a stored procedure it says it was successfull but didnt return any results yet when I put it in Query analyzer it works 100%


in My Stored Procedure I use:


Code:
If @Contributed = '2'
If @MyAsn = 06

SELECT TOP 100 PERCENT Tb2.MemNo, Tb3.LastContributionDate, Tb3.TimesContributed, Tb3.ContributionsTotal,
Tb2.Principal, Tb2.Agency, Tb2.Address1,MemberType, dbo.[tblLOOKUP-County].County FROM dbo.tblProspectMember Tb2 LEFT OUTER JOIN
(SELECT DISTINCT
(Tb1.Memno) AS NewMemno, MAX(Tb1.ContributionDate) AS LastContributionDate, COUNT(ContributionAmt) AS TimesContributed,
SUM(ContributionAmt) AS ContributionsTotal
FROM TblPacContributions Tb1
WHERE ContributionAmt > 0 AND Year(tb1.ContributionDate) != @MyYear
GROUP BY Tb1.Memno) Tb3 ON Tb3.NewMemno = Tb2.MemNo
INNER JOIN dbo.[tblLOOKUP-County] ON Tb2.COC = dbo.[tblLOOKUP-County].ID
WHERE (Tb2.MemberType IN ('C', 'E', 'F', 'G', 'L', 'N') AND ASN = @MyAsn)
ORDER BY Tb2.MemNo
anybody have any clua as to this one..baffled me for a cpl hours now..

---

How Join Works In Query If 2nd Table Missing Rows
SELECT A.itemno, A.itemname, A.price, SUM(B.quantity - C.quantity) AS stock FROM items A INNER JOIN purchase B ON A.itemno = B.itemno INNER JOIN sale C ON A.itemno = C.itemno GROUP BY A.itemno, A.itemname, A.price

code message appear sytex error (missing operator)


i think cause is table1 have all categories rows include table2 and table3 but
table2 have rows 2,4,7 and table3 have rows 1,2,3
i mean if missing rows in table2(1,3,5,6) and table3(4,5,6,7)

so how could be total through join like

(table2.quantity-table3.quantity)as stock

what could be logic there if same itemno is not in a 2nd or 3rd table

---

Query Works Fine But Records Are Not Shown In Data Report
In my project i have 2 data reports associated to different commands but in the same Data Environment.
The first one prints all the records found between dates.
I do the query using : SELECT * FROM Table1 WHERE Date BETWEEN ? AND ? ORDER BY Date
Now for the second data report i do a query for all the records found between dates but also for the people who arrived late.
I do this query like this: SELECT * FROM Table1 WHERE Date BETWEEN ? AND ? AND Delayed = 'YES' ORDER BY Date

The first report shows the information fine.
The problem is the second report. If my query only finds one record then this record isn't shown in the data report, i mean, the data report is displayed empty. But then if the query finds more than one record they show up.

This problem is driving me crazy because i designed both data reports exactly, the only difference is the SQL sentence but i'm sure that's not the problem because i used them in other parts of the project and they work fine.

Do you have any idea how to solve this?.

---

Query On An Access Query !
I have a table with data as follows:

ProductNo, Customer
1, John
1, Simon
1, Andrew
2, Andrew
2, Dave
3, Simon
4, Pete
4, John
4, Dave

I want to display the data in a query as follows:

Product Number, Customers Bought
1, "John, Simon, Andrew"
2, "Andrew, Dave"
3, "Simon"
4, "Pete, John, Dave"


Any ideas how??????

---

Access Query -&gt; ADO Query Using DSN
Hello,

I have an Access database which links to tables on an MS SQL Server. It has a query which pulls together information from three different tables. Two of the tables are in one database; the third is in another. The query is supposed to grab the first full set of information it finds for a particular production unit (reel). The query is as follows, and works as expected from Access:

SELECT TOP 1 [dbo_reel].[reel_key], [dbo_Roll_Summary].[cust_key_end_user], [dbo_reel].[dt_turnup], [dbo_reel].[avg_speed_fpm], [dbo_reel].[birth_bwt_act], [dbo_reel].[birth_moisture], [dbo_Quality_Data].[Opacity_(6S20)], [dbo_Quality_Data].[TEAR_md_(2M80)], [dbo_Quality_Data].[TEAR_cd_(2C80)], [dbo_Quality_Data].[TENS_cd_(2C10)], [dbo_Quality_Data].[BRT_t_(6T10)], [dbo_Quality_Data].[SMOOTH_t_(4T40)], [dbo_Quality_Data].[SMOOTH_b_(4B40)], [dbo_Quality_Data].[POROSITY_(5S30)], [dbo_Quality_Data].[TEA_md_(2M60)]

FROM dbo_Quality_Data INNER JOIN (dbo_reel INNER JOIN dbo_Roll_Summary ON [dbo_reel].[reel_key]=[dbo_Roll_Summary].[roll_reel]) ON [dbo_Quality_Data].[sample_key]=[dbo_reel].[reel_key]

WHERE ((([dbo_reel].[reel_key])=[Reel Key]) And (([dbo_Quality_Data].[Opacity_(6S20)]) Is Not Null));

Now, I need to convert this query to a form I can use with ADO. I created a DSN for the SQL Server and pass its name to the ADODB.Connection object. I realized that the table names in Access are not those on SQL Server. Through the SQL Server database list and small test SELECT queries I found the database & table names I need and that ADO (or SQL Server?) does not like square brackets in the statement. My "converted" query is:

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user, rep_snnb.dbo.reel.dt_turnup, rep_snnb.dbo.reel.avg_speed_fpm, rep_snnb.dbo.reel.birth_bwt_act, rep_snnb.dbo.reel.birth_moisture, Technical.dbo.Quality_Data.Opacity_(6S20), Technical.dbo.Quality_Data.TEAR_md_(2M80), Technical.dbo.Quality_Data.TEAR_cd_(2C80), Technical.dbo.Quality_Data.TENS_cd_(2C10), Technical.dbo.Quality_Data.BRT_t_(6T10), Technical.dbo.Quality_Data.SMOOTH_t_(4T40), Technical.dbo.Quality_Data.SMOOTH_b_(4B40), Technical.dbo.Quality_Data.POROSITY_(5S30) , Technical.dbo.Quality_Data.TEA_md_(2M60)

FROM Technical.dbo.Quality_Data INNER JOIN (rep_snnb.dbo.reel INNER JOIN Technical.dbo.Roll_Summary ON rep_snnb.dbo.reel.reel_key=Technical.dbo.Roll_Summary.roll_reel) ON Technical.dbo.Quality_Data.sample_key = rep_snnb.dbo.reel.reel_key

WHERE (((rep_snnb.dbo.reel.reel_key)='" & reelKey & "') And ((Technical.dbo.Quality_Data.Opacity_(6S20)) Is Not Null));

So, [dbo_reel] = rep_snnb.dbo.reel; [dbo_Roll_Summary] = Technical.dbo.Roll_Summary; and [dbo_Quality_Data] = Technical.dbo.Quality_Data.

Unfortunately, the converted query doesn't work. When I run the following code, I get a "Run-time Automation" error at the last line:


   Dim rs As ADODB.recordSet
   Dim conn As ADODB.connection
   Dim sql As String

   Set rs = New ADODB.recordSet
   Set conn = New ADODB.connection
' SPNEWBERG14 = ODBC data source name
   conn.Open "SPNEWBERG14"
   sql = (query)
   rs.Open sql, conn, adOpenDynamic, adLockOptimistic


I've also tried simple JOINing queries such as this one, but still get the error.

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user FROM rep_snnb.reel INNER JOIN Technical.Roll_Summary ON rep_snnb.dbo.reel.reel_key = Technical.dbo.Roll_Summary.roll_reel WHERE rep_snnb.dbo.reel.reel_key = '5H0101';

Only the most simple SELECT queries work:

SELECT rep_snnb.dbo.reel.reel_key FROM rep_snnb.dbo.reel WHERE rep_snnb.dbo.reel.reel_key='5H0101';

Perhaps it has something to do with my JOIN statements, but they were essentially (except for the name conversion and removal of square brackets) created by Access and should work--or am I wrong in assuming that?

Thank you for any light you can shed.

- Ben

---

Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?


Sub Collect_HealthPlan()

Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command

'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection

'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"

'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")

'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize

'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount

i = 1

'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
    If ctrlHPlstbox.Selected(CurrentRow) Then
        HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
        'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
        HealthPlan_Selected = i
        i = i + 1
    End If
Next CurrentRow

i = 1

'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
    QueryBuilder(i) = SQLString
    QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
    
    If ctrlNBchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
        
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
                
            End If
            
        Next CurrentRow
        
    End If
    
    If ctrlNBEAchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
            
        Next CurrentRow
        
    
    End If
    
Next i
    
End Sub

Let me know if you have a better way to do this..Thanks for all your help!!!

Regards,
Eric




Edited by - bahamaej on 3/3/2005 4:50:53 AM

---

Crystal Reports && Dynamic Query (even Tables And Fields Changes In The Query At Run Time)
 
Hi there!

I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .

i tried with a temporarytable but that too doesnt seems to work.

Can anyone guide me of any simpler methode of doing this?

Bipin

---

Count Rows Returned By Query In The Query Strings
i have some problem guys about grid. the prob is like this. im using DBGrid n when i queried it, it return the values ryt ? but i dono how to count the data from the returned query string processing that i put in DBGrid. pls tell me the solution about this pls ? wil u help me guys ? n thx in advanced 4 ur
 help. GOD BLESS U ALL !!!

---

How To Add Parameters To A Query Created In Microsoft Query
I define the parameter I want (which is data placed in the excel file itself) but when I refresh the cell the sql loosses the parameter.

---

SSTab Query && Data Repeater Query
I have set up a SStab to make data from a system easier to organise. The system retrives the data correctly but does not update it. I am using the propertychanged method on the datarepeater.

Private Sub txtclosedate_Change()
PropertyChanged "closedate"
End Sub

Public Property Get closedate() As Date
closedate = txtclosedate
End Property

Public Property Let closedate(ByVal newclosedate As Date)
txtclosedate = newclosedate
End Property

Is the Tab affecting it if so where can I find a guide to sort this out- the KB is not that helpful.

Thanks

Frank

---

Query With SHAPE Command In Query Analyzer ???? Please Help!
Hi,

I am writing a complicated SQL that deals with SHAPE command.
I am using MSSQL 2000. Ok.

The problem is that Query Analyzer dont recognizes SHAPE sintaxe (gives me a "[Microsoft][ODBC SQL Server Driver] Sintaxe error or access violation" message), then is a pain-in-the-*** to keep testing the query directly in VB.

Can anyone tell me if there is some SQL environment where I can test queries with SHAPE command, or if there is any way to trick Query Analyzer to force it to understand this command?

Thanks!

---

Executing Query Analyzer Query File
Is it possible to execute a Query Analyzer Query File from within VB6? If so, can you show me how? Can the Query Analyzer Query File be converted into a SP?


Thanks in advance!!

---

Set Query Parameter For Query That Is Report Source
I am trying to assing a value to a parameter defined in one of my queries in an Access Database. The ultimate purpose is to run a report named "Summary" that has this query as the source. The problem is that I've tried different ways to assing the parameters and they don't seem to be working. How would I go about making this code work?

CODE:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value 'Assign value specified in Form

DoCmd.OpenReport "Summary", acViewPreview

'This didn't work as it still prompted me to define the parameters.
'So I tried the following Code:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String
Dim rstQuery As Recordset

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value

Set rstQuery = parQ.OpenRecordset()
DoCmd.OpenReport "Summary", acViewPreview

'This doesnt work either. I have to other parameters in the query, two dates read off directly as entered in the form, that are not explicitly defined as parameters. This code gives me an error that it expected more parameters. I assume it is the dates. But the dates do have values in the form that are apparently not being read.

Any suggestions? All is appreciated.

---

Help Needed To Create Such An SQL Query, A Looong Query?
I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

I have three tables with the following data:
Table one: tblAssessment

UserID companyName SportName CountryName
   1 2        6         
   2 4        8    
   3 3        7
 
Table two: tblDropDown

DropDownID ParentID Name
1 0 CompanyName (as CompanyName has DropDownID=1, all the companynames will have parentId 1
2 1 Nike
3 1 Reebox
4 1 Puma
5 0 SportName (as SportName has DropDownID=5, all the Sportnames will have parentId 5)
6     5 Football
7     5 Baseball
8 5 Rugby

Table three: tblSummary

CompanyName SportName CountryName
Nike     Football England
Reebok     Baseball USA
Puma     Rugby NewZealand


Below is my brief explanation of the tables and fields

tblAssessment have 4 Fields:
UserID (PrimaryKey)
CompanyName(this stores an ID that refers to the name in the dropdown table)
SportName(This stores an ID that refers to the sportsname in the dropdown table)
CountryName

tblDropDown has three fields:
DropDownID (Primary Key)
ParentID
Name

The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

tblSummary has three fields:
CompanyName
SportName
CountryName
This table has pure text data for all fields.

I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

Could someone plz guide me how to create such a query?

I have attached a sample access database that mirrors the same above format.

Thanks,

---

Print Access Reports With Values Passed To A Access Query From A Textbox On A Form.
Hello,


DB = access 2000
Method = DAO

This is my first post on this forum, I'm new to VB.

I am trying to print a report from access using VB, but what I would like to do is pass values to the query and then print the report based on the the results of the query that had values passed to it. I'm able to pass values to the query that works well.
also I'm able to print the report but I have to pass values to it using the dialog boxes that Access 2000 provides.

Is there a way to print the report that uses the query with the values that have been passed to it through the form?

***Code for printing reports***

Private Sub cmdReport_Click()

  Set MSAccess = New Access.Application

  MSAccess.OpenCurrentDatabase ("C:Documents and SettingsfiqbalDesktopdb1.mdb")
  MSAccess.DoCmd.OpenReport "Report based on Para Passing", acViewNormal
  MSAccess.CloseCurrentDatabase
  Set MSAccess = Nothing
    
End Sub

***End of Code****

***Code for Passing values from a textbox on a form***

sqlstr = "select * from Table1 where [name] ='" & Text1.Text & "' and [phone] ='" & Text2.Text & "'"

*** End of Code****

How can I get the report to use the above SQL without the access dialog boxes and the values just being passed from the text boxes.

would appreciate all the help

thanx in advance.

Kinara

---

Access Query From Vb
i'm writting a program that is going to update a table in an access data base. this is all automated from visual basic. the table that is going to be updated is a history file (its Called AdamWeeklySpentHistory). this is going to be updated by having other tables put in to it eg AdamWeeklySpentWeek1. Nothing is shown in vb.

i think i need to aisgn an sql statement to a variable then pass it to a query in access. i don't know how to pass the variable to the access query. can anyone help.

i've never programmed access to work with vb. all i've got so far is the access database opening. and an exit button. this is the code:


Option Explicit
'access variables
Dim myACApp As New Access.Application
Dim myDatabase As Database

Private Sub cmdCreateHistoryFile_Click()
Set myACApp = New Access.Application
myACApp.OpenCurrentDatabase ("H:/History Project/dbtest.mdb")
Set myDatabase = myACApp.CurrentDb


End Sub

Private Sub cmdExit_Click()
myACApp.Quit
End
End Sub


any help would be great.

thanks a lot

---

Access Query And VBA
I am using the following code to return a sum of all sales (this is actually a Select query with totals). The only this this query returns is the sum. The field is TotalSales. The problem is as follows:
----------------------------------
Compile Error:

Method or Data member not found.
-----------------------------------

The debuger highlights the TotalSales in the next to last line.
----------------------------------------------------------------

Dim qryLoc1Total As QueryDef
Dim strTotLoc1 As String


Set qryLoc1Total = CurrentDb.QueryDefs("qry_SalesTotalCurMonth_TotalsLoc1")

qryLoc1Total("strTotLoc1") = Me.TotalSales

MsgBox strTotLoc1


The gist is I am using a query that returns a sum of sales. That is the only value that is returned (in the TotalSales field).


Thanks again.....

---

Access Query
Hey

Im trying to create a query in Access to show the currency in a field. The field is called Monthly Rental Cost and the currencies are £300, £400 and £500. When I try to define the criteria to one of these amounts it doe'nt work.

any info on how to solve this will be appericated

Thanks

---

Access Query
Code:
DoCmd.RunSQL ("INSERT INTO tblfinalquery ( FIRST, LAST ) " & _
"SELECT tblMain.FIRST, tblMain.LAST FROM tblMain WHERE (((tblMain.pcn) Like '*'))")


Is there some way to place a variable AND or several AND's towards the end of this statement? I have tried several different ways unsuccesfully.

---

Query Access From VB
I have a form with 2 text boxes (Date and shift)and a Command button. I want to make it so when I enter a date in the date box and a shift (1,2,3) in the shift box and click the command button it sets the criteria in a access query.

Is this possibe and if it is, Could anyone please explain to me how to do it. I'm just learning VB so this may take awhile.

Thanks

---

Access Query
I have a text box called 'LearnerID' on a form and i would like to simply add the contents of whatever is inside to a 'Learner' table on a press of a button. Can this be done all in vba and NOT as an append/update query? If so roughly how would it be written? Thanks

---

Access Query
I want to make a program this way: I click a button and the input box comes.. i write the sql select command and it adds a query named "vb" in the file "test.mdb".
How to add a query from vb?? Help me! I use visual basic 6 with service pack 6.. any ideas?? anyone?

---

Access DB Query
How can I run a query through a Access Database? I have the data control on my form, and it's all fine and dandy, but now instead of moving from one to next I want to move straight to the entry where the 'email' field is set to, say, "me@notyou.com".

---

Access Query
this is a query in access:access gives me weird errors....please help

strSql = ((((SELECT a.book as Book, a.bucket as Bucket, a.Book_Attr6 as [Book Attribute], a.trade_type1 as [Trade Type],
a.trade_date as [Trade Date], a.group1 as [Group], a.tnum as TNUM, a.cpty_short as [Counterparty],
a.load_shape as [Load Shape], a.comp1 as [Component], a.buy_sell as [Buy Sell], a.putcall as [Put Call],
a.premium_rt as [Premium Rate],
b.delta_quantity as [Delta Quantity],a.qty as [Quantity], a.prc_strk_addr as [Price Strike Adder],
a.market1 as [Market], a.rec_date as [RecDate],
b.days_to_expiration as [Days to Expiration], b.interest_rate as [Interest Rate], b.option_price as [Option Price],
b.market_value as [Market Value], b.mark_to_market_disc as [MTM Disc], b.mark_to_market_nondisc as [MTM Non Disc],
b.contract_value as [Contract Value], b.fwd_price as [Forward Price],
v.Price as [Palo Verde On Peak Trade Date],
x.Price as [Palo Verde Off Peak Trade Date],
y.Price as [Palo Verde Flat Trade Date]
FROM dbo_u_master_pl a
INNER JOIN dbo_u_MTM_Output b ON a.rec_date = b.rec_date AND a.tnum = b.tnum AND a.block_num = b.block_num AND a.bucket = b.bucket )
LEFT JOIN dbo_vPrices v ON a.bucket = v.MatureDate AND a.trade_date = v.EntryDate AND v.Zainet_Mkt = 'POWER' AND v.Zainet_Comp = 'PL_ON')
LEFT JOIN dbo_vPrices x ON a.bucket = x.MatureDate AND a.trade_date = x.EntryDate AND x.Zainet_Mkt = 'POWER' AND x.Zainet_Comp = 'PL_OF')
LEFT JOIN dbo_vPrices y ON a.bucket = y.MatureDate AND a.trade_date = y.EntryDate AND y.Zainet_Mkt = 'POWER' AND y.Zainet_Comp = 'PL_FL' )
WHERE a.BUCKET BETWEEN (SELECT MIN(bucket) FROM dbo_u_master_pl WHERE rec_date = #" & dtmDate & "# ) AND (SELECT MAX(bucket) FROM dbo_u_master_pl)" & _
AND a.book IN ('SE10','SE06')
AND a.rec_date = #" & dtmDate & "#
ORDER BY a.book, a.bucket, a.trade_date, a.tnum

---