SQL Server Query convert to access query

[ahmedjamalaboelez]

Gentlemen,

Company (IT) team provide me with SQL Server Query which result in sales query , no errors while running on our MS SQL Server Machine , i already linked required tables in my access database because i need to make more advanced reports , but i can not write this kind of queries in access and team mate have no knowledge about access queries

:banghead: Question : is there any way to send this query to SQL server in VBA Code , and drop the result in my sub form

Notes :
Thread Not Published in Any other Forums
Any Help Regarding This Thread will be appreciated


Thanks

Select
lok_nazwa As Location,
DSN_PelnyNrDokumentu [Shift No.],
format(dsn_datadokumentu,'yy')+convert (varchar,lok_nr)+convert (varchar,DSR_NrRachunku) [Bill Number],
Format(DSR_DataZamkniecia,'yyyy-dd-MM hh:mm tt' ) [DATE],
ART_nr As [Item No.],
ART_Nazwa [Item Name],
SUM (DSL_Ilosc)[Qty],
Sum(dsl_ilosc*DSL_CenaSprzedazyNetto) [Net Total],
Sum(dsl_ilosc*DSL_CenaSprzedazyBrutto) [Total],
(Sum(dsl_ilosc*DSL_CenaSprzedazyBrutto))-(Sum(dsl_ilosc*DSL_CenaSprzedazyNetto)) VAT,
FP1.FPL_ID [FOP1 ID],
FP1.FPL_Nazwa [FOP1],
SUM(DSL_KwotaFP1) as [FP1 Amount],
SUM(DSL_FPLID2) as [FOP ID2],
(select FPL_Nazwa [FOP2] from FPL_FormyPlatnosci where SUM(DSL_FPLID2) is not null and FPL_ID=SUM(DSL_FPLID2)),
SUM(DSL_KwotaFP2) as [FP2 Amount],
TOR_Nazwa [T.O.O]
from DSL_DokumentySprzedazyLinijki
Left Outer Join DSR_DokumentySprzedazyRachunki on DSR_ID=DSL_DSRID
JOIN TOR_TypyOtwarciaRachunku ON TOR_ID=DSR_TORID
Left Outer JOIN SSG_StanowiskaSprzedazyGrupy ON SSG_ID=DSR_SSGID
Left Outer JOIN LOK_Lokale ON LOK_ID=SSG_LOKID
JOIN FPL_FormyPlatnosci FP1 ON FP1.FPL_ID=DSL_FPLID1
JOIN ART_Artykuly ON ART_ID=DSL_ARTID
Left Outer JOIN DSN_DokumentySprzedazyNaglowki ON DSN_ID=DSL_DSNID
where DSL_DataStorna is null and DSR_DataZamkniecia is not null and DSR_DataZamkniecia  between '2019-01-01 00:00:00' and '2019-01-01 23:59:00' and DSL_DSLIDPowiazana is null
Group by lok_nazwa,
format(dsn_datadokumentu,'yy')+convert (varchar,lok_nr)+convert (varchar,DSR_NrRachunku) ,
DSN_PelnyNrDokumentu,
DSR_DataZamkniecia,
ART_nr ,
ART_Nazwa,
FP1.FPL_ID,
FP1.FPL_Nazwa,
TOR_Nazwa 
Order by [DATE],[Location],[Shift No.],[Bill Number]

 

[theDBguy]

Hi. You could try this:


1. Create a new query design in Access
2. Close the Show Table window
3. Click on the Pass-Through button on the Ribbon
4. Copy and paste your SQL statement
5. Add the connection string to your server in the ODBC Connect Str property in the Properties window
6. Save the query and try to run it. If it works, try using it for your form.


Hope that helps...

 

[plog]

The problem with SQL is that every vendor has their own flavor. While they are all 95% the same, that 5% difference causes issues like this when you run the same SQL on different platforms. I suggest you learn about generic standard SQL:

https://www.w3schools.com/sql/

That's the basics. From there the problems that arise are generally from domain functions (e.g. convert(), format(), Date(), etc.) which are specific to SQL server engine the SQL is run on. I am pretty sure that's the problem for you.

My advice is to copy in the SQL you posted but remove every field that uses a function (convert(), format(), SUM()) and see if if the query works then. I am pretty sure it will. Once it does you turn to google and find out what you should use in Access instead of those specific convert(), format(), SUM() functions and then add them back to your query with the Access equivalents..

 

[ahmedjamalaboelez]

Hi. You could try this:


1. Create a new query design in Access
2. Close the Show Table window
3. Click on the Pass-Through button on the Ribbon
4. Copy and paste your SQL statement
5. Add the connection string to your server in the ODBC Connect Str property in the Properties window
6. Save the query and try to run it. If it works, try using it for your form.


Hope that helps...

I already tried this, but resulted error , not recognized Sql |Of Access

 

[theDBguy]

I already tried this, but resulted error , not recognized Sql |Of Access

Hi. Sorry to hear that. Can't help you if we can't see what happened. Can you post a screenshot of the error message and the query in design view?