Help with Access and SQL

[rlaker]

Hi all



Im a new member, I havent used access in years but used to do quite abit of work in access creating DB's with Froms and front ends etc, its been a while but the form side of things I think i should be fine with one i get over this stumbling block, if i layout what I have and what im trying to achieve.

What I have

Access 2013
SQL Server 2008 (Hosted but we have connection via secure link)
a ton of SQL Queries Ive written with help of my Development Manager (im new to SQL)
Ive linked all the tables in Access that my Queries use from SQL
All of the queries work in SQL Management Studio and give the desired results

What im trying to achieve

Im trying to build a front end application that will link back to my SQL DB for my CRM System, I then am going to build a Main Menu system using forms and build a few forms for various Departments so they can click a button to run a Report, this will run a SQL Query and dispay the output in a New Window or within a box on the form depending on how i design it and what works for that specific output. Im not talking about anything massivly complicated hopefully that I want to setup I alreayd have SQL Query's that give the desired output but I have managers that I want design an interface for to save them coming to me all the time to produce reports for them, this way I can give them a Front End on their machines and if they want to see the daily stats or ticket stats etc for the week / month they can just hit a button.

Anyone able to assist me with this, Ive been having alook and trying but Access wont let me save the Query's when Ive entered them inot Access in SQL View or run them it doesnt like the expressions and moans about various ones but these are standard SQL Query's that work fine in SQL Management view.

Cheers in advance for any help

Rich

[orange]

Please show some examples of

but Access wont let me save the Query's when Ive entered them into Access in SQL View or run them it doesnt like the expressions and moans about various ones but these are standard SQL Query's that work fine in SQL Management view.

I don't use SQL Server nor Management Studio, but it sounds like you want an Access front end to your SQLserver based data.

Possible issue is using SQL server based tools that require SQLServer SQL. Access SQL and SqlServer SQL are not the same thing. You may be using some SQL variant that is only available in SQLServer.

Just a guess.

[rlaker]

Hi Orange

Thanks for your reply, please see below a snip of the error im getting when i copy and paste the SQL code and try to run it, also on the left you can see the linked tables

[orange]

Well, as I said I don't use SQLserver, but I can tell you that CASE is not available in Access SQL.
If you intend to use SQLServer-based SQL, then your strategy may be to save all SQL and code in SQLServer, and use SQLServer stored procedures, then execute those procedures from Access. That's my best guess since I don't use SQLServer.
I recommend you wait for some members with SQLServer expertise to clarify/respond.

In the meantime you can do some research on SQLServer stored procedures, and/or calling SQLServer stored procedures from Access.

If you intend to store your queries in Access, then you must revise your queries to use ONLY Access-sql constructs. Day is a reserved word in Access. Access reserved words.

You can simulate/approximate the Case with some IIF statements in Access.

[IrogSinta]

The way your query is written would not work in Access' Query by Example (QBE). You would need to either use a Pass-Through query or rewrite your query using MS Access SQL syntax by changing this part:
Sum(Iif(DateDiff("d", sv_Date_Entered, Date()) = 0, 1, 0) As Opened, Sum(Iif(DateDiff("d", sv_Date_Closed, Date()) = 0, 1, 0) As Closed

[rlaker]

Hi Irog

Thanks for that PassThrough Query gave me my results but it seems each time it wants me to select the the datasource, is there a way for it to remember or use the linked tables?

Rich

[IrogSinta]

You can add the datasource to the ODBC Connect Str property of your pass through or just use the linked tables by changing your query syntax as I mentioned earlier.

[rlaker]

thanks guys, both of your help has really got me going on this so far, first query all working with forms leading up to it, so i now have a structure to build on, its only been 17 years since i used access lol now i feel really old!!!