Solved

access sql weired issue

Posted on 2014-02-11
35
229 Views
Last Modified: 2014-03-05
hi,
can someone explain why the sql below works

SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM (CHRC INNER JOIN (Employeestbl LEFT JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28]. AS es ON Employeestbl.ID = es.employeeid) ON CHRC.EmployeeID = Employeestbl.ID) INNER JOIN [Employee'sFacilitiestbl] ON Employeestbl.ID = [Employee'sFacilitiestbl].EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183));

Open in new window


and the following sql gets an error invalid operation
SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM (CHRC INNER JOIN (Employeestbl LEFT JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28]. AS es ON Employeestbl.ID = es.employeeid) ON CHRC.EmployeeID = Employeestbl.ID) INNER JOIN [Employee'sFacilitiestbl] ON Employeestbl.ID = [Employee'sFacilitiestbl].EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183)) OR (((Employeestbl.Title)="housekeeper") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=6488));

Open in new window


they are the same, except as i am adding to the where clause??
0
Comment
Question by:bfuchs
  • 18
  • 12
  • 3
  • +2
35 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Try to reformat you where clause to this:
WHERE es.employeeid Is Null AND CHRC.OrientationDate<Date()-30 AND 
((Employeestbl.Title="hha" Or Employeestbl.Title="pcw") AND [Employee'sFacilitiestbl].FacilitieID=2183) 
OR 
(Employeestbl.Title="housekeeper" AND [Employee'sFacilitiestbl].FacilitieID=6488);

Open in new window

0
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
Each line containing [Employee'sFacilitiestbl].FacilitieID has one to many right-parenthesis at the end.
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Just wanted to add: try to avoid using any special characters in table names, like in Employee'sFacilitiestbl.
Also, I noticed that there is a full stop here:
JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28].

Open in new window

It is perhaps not required. I think the square brackets should be replaced with normal brackets:
JOIN (select employeeid from PatientsEmployeesSchedule where day > date()-28)

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
hi thanks for replying,

@chaau,
1- tried your first suggestion, same problem.
2- when you do this type of query in query designer sql view and then switch to design view, access automatically saves it in this bracket format with a dot at the end, the way you see it in my first sql, and this is why i posted both, the working and the not working sql, so i guess we have to focus on the where clause rather then the join as there it lays the difference..

@John_Vidmar,
i need to join 3 tables (employeestbl,employeesfacilitiestbl, chrc) and see those that dont have records in PatientsEmployeesScheduletbl in the last 30 days, do you have another way to join them?
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
hi,
just changed the join (which in my case dont think it makes a diff), and the below works..

SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM ([Employee'sFacilitiestbl] INNER JOIN (Employeestbl LEFT JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28]. AS es ON Employeestbl.ID = es.employeeid) ON [Employee'sFacilitiestbl].EmployeeID = Employeestbl.ID) INNER JOIN CHRC ON [Employee'sFacilitiestbl].EmployeeID = CHRC.EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183)) OR (((Employeestbl.Title)="housekeeper") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=6488));

Open in new window


however no idea why this is better then the previous one..
0
 
LVL 24

Expert Comment

by:chaau
Comment Utility
Very strange indeed. It may be something to do with the apostrophe in the table name
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
well in access its legal to have them named this way, as long they are enclosed in brackets.
I did now testing the old sql giving it an alias as below, still same issue.

SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM (CHRC INNER JOIN (Employeestbl LEFT JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28]. AS es ON Employeestbl.ID = es.employeeid) ON CHRC.EmployeeID = Employeestbl.ID) INNER JOIN [Employee'sFacilitiestbl] AS EF ON Employeestbl.ID = EF.EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND ((EF.FacilitieID)=2183)) OR (((Employeestbl.Title)="housekeeper") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND ((EF.FacilitieID)=6488));

Open in new window

0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

beside what the others already said:

day > date()-28

Open in new window


"Day" is a function name so maybe this is the reason for your error. This is a typical example why you should never use simple column names like "Name", "Date", "Day" and many more.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,
long time didn't hear from you--:)

1- don't think day is a reserved word in access/sql, this is why I named it day and not date.

2- my original question still remains open, what is the difference between the first sql that is accepted and runs well and the second sql that returns invalid operation, this day condition is included in both.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

I was not away...:-)

Day is a function to get the day part of a date, same like "Month" and "Year" functions. But you're right, that's not the reason here, I tested that now and Access accept that (but I would really change that name as you could also get in trouble using that in VBA). As a rule of thumb: Add a prefix to all column names, makes life really easy. I always use "F_" for fields, "ID_" for key columns and "C_" for calculated fields (in SQL Server) - nothing else. Clean and simple and never have problems with keywords.

The other thing is that I really don't know how you got this strange "[select...]." syntax into your SQL. Access sometimes exchanges funny things (like column name aliases with a kind of comic book alias "@wxo!!_" or similar) but I've never seen that square bracket syntax. So what's "PatientsEmployeesSchedule"? You said above that you only want to use three tables, but this one was not contained in your list. If this is a query then maybe your real problem is here (additionally to the square brackets, you should first try to remove that on your own independent if that works or not). I saw strange effects if queries of Access were nested too much with lots of datatype conversions and wrong table column declarations in beginners databases so if that is a query you should also post the SQL of that and if that one also uses other queries, then all of them also.

(The most easy way would be to simply upload a demo database containing your queries, the needed tables with some demo data and a hint what to do to got your error). I checked the WHERE clause and there's nothing in there what could cause an error.

Another possibility is that some reference in VBA is broken as Date is completely written as "VBA.Date()". If a reference is broken VBA sometimes does not recognize it's own functions and throws errors (especially also if you use a column or alias name named "Date" of course). So you should also at least write the "Day" column in square brackets to make sure that not the Day function is used instead.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi,

every time i want to do a query in access that should select from tableA where there is no records in tableB in certain condition, in this case where there is no schedule in PatientsEmployeesSchedule for the last 29 days, I have the following options

1- save a query that contains all scheduling from the last 29 days and use that query as the left join, I end up with countless saved objects in my app if I choose that route..

2- use this in the where clause, like where EmployeeID not in (select EmployeeID from PatientsEmployeesSchedule where Day > date-28), or use where not exists select...
however this usually does not perform well in large tables and takes forever.

3- use the first option, just embed it into the sql like the following
 LEFT JOIN (select employeeid from PatientsEmployeesSchedule where day > date()-28) AS es ON Employeestbl.ID = es.employeeid)

Open in new window

and end it with where es.EmployeeID is null.

Now every time I use that last option ans switch from sql view to design view and back to sql, or just by saving it closing and reopening, access encloses it into brackets with the dot in the end, like you see above..
there is something strange with this, that it works well as long as you don't do any changes to it in design view, if you happened to change something, it would throw you an error and the only way out is to remove that brackets in sql view, replace them with parenthesis and save it, (just to have access convert it back to brackets the next time you open...).

I am attaching a screenshot of how the architecture of the tables look like, let me know if you need more info,

Thanks
Ben
Doc14.doc
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

using a saved query is always preferable because it allows the database engine to optimize it before it is used. In case of the choice between "IN" and "EXISTS" you should use EXISTS because this don't need to load the data but only need to compare what you have in the WHERE clause (where the used columns should of course be indexed). In case of EXISTS a saved query would not help.

It seems a little bit as if your database file is maybe corrupt, did you try to compact & repair or import anything into a new database file to remove that behaviour with the squared brackets?

In your diagram there are also 4 entities, not 3...;-)
So the question is still open: Is "PatientsEmployeesSchedule" a table or a query? If it is a query, what's the SQL code here?

If you do not want to upload a demo database you should at least do the following on your own: Import all tables and queries which are used in your query into a new database file (only that, nothing more) and try to run that here to see if you have the same problems with the square brackets.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi,

1- I guess we're both right, you are talking of version 2007+ while I am still using <=2003, see link below
http://www.access-programmers.co.uk/forums/showthread.php?t=169147

2- PatientsEmployeesSchedule is a table

3- I meant joining 3 tables (Employeestbl, CHRC, Employee'sFacilitiestbl) were data does not exists in table4 (PatientsEmployeesSchedule).

4- Not sure if its worth to have the query optimized when its something not used so often, maybe once a day by one user, while for me creating and saving all those queries then having to deal with hundreds or more objects in the application will cause a real burden on my shoulders..

5- are you so sure access does not uptimize subqueries saved within queries?
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

I would say, it is always worth to optimize a query especially if the only thing you need to do is to create an index...:-)

But I think we are now at a point where this can't be solved without your database file. Did you try to import that into a new file and test if that works?

Did you try the suggestion in your quoted thread to switch off the auto name correction?

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi,

Not sure I undetstood,

1-
especially if the only thing you need to do is to create an index..
this is not the issue, my problem layes on having to save too many queries on the application file, that eventually giving me a hard time to find things when needed..

2-
did you try to compact & repair or import anything into a new database file to remove that behaviour with the squared brackets?
well, now that you can see that this is the expected behavior of access at least in my version, why do you think its still necessary the import procedure?

3- Yes, that auto name correction is something that I always turned off, (the only thing I saw is just complains about it, don't even know what this was really meant for...),

4- as they mention there, this doesn't alter the brackets behavior, besides i dont think that this is the source of the problem, as both sql statements above have them, they only differ in the where clause, when i try to add another condition.

5- fyi, I did got that to work when I modified the joining fields as below, in this case the outcome would be the same, however I didn't close the question as this still wonder me why that original sql posted didn't work..

SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM ([Employee'sFacilitiestbl] INNER JOIN (Employeestbl LEFT JOIN [select employeeid from PatientsEmployeesSchedule where day > date()-28]. AS es ON Employeestbl.ID = es.employeeid) ON [Employee'sFacilitiestbl].EmployeeID = Employeestbl.ID) LEFT JOIN CHRC ON [Employee'sFacilitiestbl].EmployeeID = CHRC.EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183)) OR (((Employeestbl.Title)="housekeeper") AND ((es.employeeid) Is Null) AND (([Employee'sFacilitiestbl].FacilitieID) In (6488,6589,8510)) AND (([Employee'sFacilitiestbl].DayEntered)<Date()-30));

Open in new window


Thanks
Ben
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
just to prove its nothing to do with the brackets..

I tried the below and got the same error message, "Invalid Operation"

SELECT DISTINCT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, Employeestbl.Title
FROM (CHRC INNER JOIN (Employeestbl LEFT JOIN (select employeeid from PatientsEmployeesSchedule where day > date()-28) AS es ON Employeestbl.ID = es.employeeid) ON CHRC.EmployeeID = Employeestbl.ID) INNER JOIN [Employee'sFacilitiestbl] ON Employeestbl.ID = [Employee'sFacilitiestbl].EmployeeID
WHERE (((Employeestbl.Title)="hha" Or (Employeestbl.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183)) OR (((Employeestbl.Title)="housekeeper") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=6488)); 

Open in new window

0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
EmployeesFacilitiestbl

Employee'sFacilitiestbl     My editors stop at the '
0
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

 
LVL 3

Author Comment

by:bfuchs
Comment Utility
hi,
are you enclosing them with brackets?
what program editor are you using?
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

the number of saved objects should never be a reason to not save a query because if you use a good naming style you will not have a problem to find the right object. In my current project I use around 300 tables, at least the same number of queries, around 500 and in future more stored procedures, UDFs and so on and I cannot say that I am not able to find exactly the object I want whenever needed.

"Expected behaviour" is no reason to not simply test it because it only takes minutes to try that - excluding possible reasons is a very good method to find the exact error if you cannot find the right reason for some issue directly.
Moreover I personally cannot find a solution for an issue which is not clearly coming from a syntax problem without having the objects available and some test data because often the problem is not the object you are looking on but a problem with the base objects or a file problem which you will never find if you stare at the SQL code only.

But if you have a solution everything is fine now..:-)

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

Ok I did what you suggested and it worked there, meaning imported all 4 tables into a new access file included their data and it worked..

Then I tried refreshing all links of my original app and compacted/repaired, but still nothing helped.

So there is one more thing I am guessing, that one of those tables is an access table (CHRC) while the others are sql tables, could be the mix of those data sources is the culprit..

It could also be that only if all those tables are stored in access they work..

There could also be a possibility that just by importing all my objects into a new fresh access file it will work.
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Ok finally i think i got it, my first guess was right.

I deleted all those 4 tables and instead of importing them I linked them to the original source and the same problem appeared again,
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

you see, a simple test can bring you closer to the real problem. So now we know that it are linked tables here and not Access tables which could of course make a difference. If the linked tables are not from an Access backend but any other database DAO needs to do datatype conversions for any field and so the reason for your problem may be that you use a field where there is a problem with this conversion or where you try to compare a datatype with another which could not be implicitly converted or anything like this.

Of course this cannot be seen from your SQL or without your complete environment. If your backend is a SQL Server then the next thing I would check is SQL Server Profiler. Here you can see which exact command will be sent to the server and also the detailed error message which the server replies. You will find out that Access manipulates your SQL a lot to execute what you want, usually using some system stored procedures to execute dynamic SQL.
The other question is: If you already have a SQL Server backend - why do you still have local tables? Move them to the backend and then use a stored procedure with parameters for all you have in your WHERE to get a clean result from the server without all the datatype conversion problems and with normally also a lot better performance.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,
(I just happen to like this name :-))

I see what you're saying about testing...

now regarding your explanation about data type conversion, I have one point, to my understanding all the conversion should be done at the joining process not at the filtering stage, and therefore if something would be wrong It should not be returning anything even without a criteria, let alone that the first sql contains filter for those same exact fields, its just adding additional conditions that I would also like to see the data.

About your last question:

1- this is coming from a pure access environment, and its being converted to sql piece by piece, hopefully one day we will get a full conversion.

2- at the moment, I don’t feel so comfortable working with the t-sql language as I am with VBA, would need some learning curve and alot of hand on experience to
 reach the point to convert everything to sql stored procedures.

if you know of a tool nowadays that converts vba to t-sql let me know..

Thanks
Ben
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

did you also check the data itself? Your additional WHERE part adds data to your result set which is not in the first query so maybe you should check the data in the tables if there is something wrong.

T-SQL has of course a lot more power than Access SQL simply because of a wide range of additional syntax. But if you know Access SQL you can use the same knowledge to nearly do the same on the server with T-SQL as Access SQL is a very basic SQL. So even without learning much about T-SQL you will be able to use it and then learn the additional syntax piece by piece.

Of course there's no VBA to SQL converter as VBA is a programming language and SQL is a query language. T-SQL offers some additional programming-like structures like IF or WHILE or GOTO and so on, but basically it is not designed to program something but to get the best out of (and into) the database.
But even without T-SQL: VBA should not be used to do things which SQL (including Access SQL) can do better.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Your additional WHERE part adds data to your result set which is not in the first query

Ok just tried with using only the second condition, which happen to be one record only, and it worked, therefore I dont think the data is the problem.

attaching a screenshot of the query designer, maybe that can help you figure out what's the matter here..
Doc4.doc
Doc4.doc
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

that doesn't help much, the only thing here is that it looks strange that your employee table seems to have an ID field which is not the primary key, the same with the facilities table where you used combined fields as PK instead of using the ID field and setting a unique key onto the employee and facility ID. Don't think that this is the error but it's a strange design.
Moreover, if you left join the PatientsEmployeesSchedule table, why do you use a subquery instead of joining the table directly and setting the condition in the WHERE clause instead? Should return the same result, but you would get rid of the square brackets.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

1- I am attaching a screenshot of the design of the Employeestbl where you can see that the ID is indeed the primary key, why its not bold is a good question, maybe your experts have an answer to that too...

2- the EmployeesFacilitiestbl, you right, if I would design that table today I would do it that way, while this was done about 15 years ago, its possible you will find some strange features here..

3- about your question with PatientsEmployeesSchedule, if I would want to see all records that does not exists in this table, I understand it could be accomplished like you said, by left join and where PatientsEmployeesSchedule.id is null, however since I only want to see who doesn't have schedule in the past 29 days, don't think this can be done just with a left join, if you have a way to do it let me know.

Thanks
Ben
Doc4.doc
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

I would say, this one should do the same (untested of course):
SELECT DISTINCT E.ID, E.LastName, E.FirstName, E.Title
FROM (CHRC INNER JOIN (Employeestbl AS E LEFT JOIN PatientsEmployeesSchedule AS es ON E.ID = es.employeeid) ON CHRC.EmployeeID = E.ID) INNER JOIN [Employee'sFacilitiestbl] ON E.ID = [Employee'sFacilitiestbl].EmployeeID
WHERE (((E.Title)="hha" Or (E.Title)="pcw") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=2183)) OR (((E.Title)="housekeeper") AND ((es.employeeid) Is Null) AND ((CHRC.OrientationDate)<Date()-30) AND (([Employee'sFacilitiestbl].FacilitieID)=6488))
AND (es.day > date()-28);

Open in new window


Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

This sql does not give me any records existing in PatientsEmployeesSchedule table, as you're requesting where EmployeeID is null, while I do need to see Employees Having schedule. as long the schedule is not within the past 29 days.

See attached how the designer looks like when I pasted your sql here in our database.

Thanks,
Ben
Doc4.doc
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi Ben,

it's the same what you had in your WHERE, I only changed the position of the condition. Maybe you should remove the IS NULL condition in your case. As I do not have your data I cannot test that. If you could create a demo database with these four tables containing some test data maybe I (or someone else) can help you further with this.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

Attached you will find a very simple database just demonstrating what type of query i need, please let me know how you would do it.

The goal is to see there all employees that did not have schedule in 2014.

Also remember we dont want to use where not exists here and not a saved object either..

BTW, you had mentioned the following:
Move them to the backend and then use a stored procedure with parameters for all you have in your WHERE
since this is a MDB file, not an ADP, I am not familiar how to accomplish it, do you have an example to demonstrate?

Thanks,
Ben
db3.mdb
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

although this contains only 2 and not 4 tables the desired result could be reached with a similar query as I wrote above:

SELECT Employees.*
FROM Employees LEFT JOIN Schedule AS s  ON Employees.ID=S.EmployeeID
WHERE NOT (s.day Between #1/1/2014# And #12/31/2014#);

Open in new window


or, as a more index friendly variant:
SELECT Employees.*
FROM Employees LEFT JOIN Schedule AS s  ON Employees.ID=S.EmployeeID
WHERE s.day < #1/1/2014# or s.day > #12/31/2014#

Open in new window


Stored Procedure: It depends if you want to use the result of the query in a form and also if this should be editable. You can use a pass-through-query to execute a stored procedure and bind this to a form but this is read only (as all PT-queries). If you want to keep it updatable then you need to create an ADO recordset and assign that recordset to the form's recordset (which can be either DAO or ADO). In this case you also need to use the SQLOLEDB driver, all other drivers are not able for Access to be updatable in a form recordset.

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

Its not exactly the same situation as above, but I believe the answer I would get for this could be applied there as well..

I am testing both versions of sql you posted and they don’t seem to return the requested results.
I need to see employees that don’t have any scheduling for 2014, (In this example test2 and test3), while both of yours return test1 and test2, as you can see, test1 has schedule for 1/1/14, besides that they are not showing test3 which doesn't have schedule in 2014 (the fact that it does not have any schedule record its not a reason it should be excluded from the output results).

About store procedure, I see what you're saying about including them in PT queries, I happen to use PT in my application and they really work very fast, however as you stated they have this big drawback about not being updatable..
Therefore I would prefer to take your second route, however if you have something on the works that use this recordset approach, please post it here,
And one more point, I guess this additional sql driver you mentioning is something that comes with windows, and just needs to be checked by application's references, can you let me know how is this called in the references dialog box?

One thing to keep in mind, our office is version 2000/2003 and the back end is SQL Express 2005, hope this does not pose any obstacles..

With appreciation,
Ben
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
Comment Utility
Hi Ben,

you're right, I didn't look at the result detailed enough, because of the similar names "Testx" in the demo database...:-)

It's indeed not possible without a subquery, but I have another variant for you which should return the wanted result but with a better performance (but also using a subquery):

SELECT *
FROM Employees
WHERE NOT EXISTS
(SELECT 1 FROM Schedule AS s 
WHERE Employees.ID=s.EmployeeID 
AND (s.[day]>=#1/1/2014# AND s.[day]<=#12/31/2014#));

Open in new window


Stored Procedures: As you already worked with ADPs I think you already have a code somewhere to execute an SP and return an ADO recordset?

Then you only need a Set command to assign that to the form's recordset property.

What I meant with the SQLOLEDB driver is that you cannot use the additional SQLNCLI driver (which must be downloaded and installed separately but is normally the best choice to work with the server), you are forced to use the SQLOLEDB driver which Access uses on it's own. So that driver is of course always available and don't need to be installed. You only need this driver if you want to get an updatable recordset, in any other case SQLNCLI is the better one.

Details on how to bind an ADO recordset to a form can be found here:
http://support.microsoft.com/kb/281998/EN-US

Cheers,

Christian
0
 
LVL 3

Author Closing Comment

by:bfuchs
Comment Utility
Hi Bitsqueezer,

Thanks I hope to get use of that ADO recordset suggestion in the near future.

Thanks very much for your valuable input!
Ben
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

8 Experts available now in Live!

Get 1:1 Help Now