Convert SQL to Access Query

[Rkeev]

Hello All,

Is there a way to convert a WINSQL query to Access Query?

I can convert it in VBA and run it in Excel but the intent is to bring in 10's of thousands of rows which Access is preferred and my skills are less than mediocre in Access (I believe the scientific term is " I suck at it").

Consequently, I am looking for some type of guide as to what to replace in the SQL query so it can run in access or if there is some type of converter out there that is suggested to use.

Any help is most appreciated.

thx,
Rick

 

[Norie]

Rick

Access does use SQL, so have you tried running the query you have as it is in Access?

 

[Rkeev]

Norie,

Thanks for the quick reply. I have tried but it doesn't work. I've copied my VBA into a module to run and it doesn't work??

 

[Norie]

Can you post the VBA and the query?

PS Did you try running the query itself in Access?

 

[Rkeev]

Norie, sorry for the delayed response; drawn into a marathon mtg then went home.

Here is the VBA:

The VBA
Option Compare Database

Public dtBegin As Date
Public dtEnd As Date
Public strBeginDate As String
Public strEndDate As String
Public strSQL As String
Public CacheConnectionString As String
Public cnCache As Object
Public strCacheError As String
Public rsCache As Object
Public strCacheQuery As String
Public PtIen As Long
Sub DoLookup()

Dim CurRow, fRow As Integer
Dim CurCol As String
Dim Rng As String
Dim strUnit As String
Dim strPatCat As String
Dim rsSubQuery As Object
Dim bGin, bendr, strSubQuery As String
Dim thYear, thMonth, thDay, nday, strDayOfWeek As String
Dim rssub As Object


Set cnCache = Nothing
On Error Resume Next

bGin = 151001 'Right(Year(Date), 2) & Month(Date) - 1 & 1
bendr = 161231 'Date - 30 'Sheets("PROV").Range("Z2").Value
sqlstr = ""
sqlstr = "select p.name, p.dob, p.sponsor_ssn, p.fmp, op.name as PRIORITY, md.name as" & Chr(13) & Chr(10)
sqlstr = sqlstr & "REFERRED_BY, o.ancillary_procedure_name," & Chr(13) & Chr(10)
sqlstr = sqlstr & " r.referral_datetime, o.id_number as ORDER_ID_NUMBER," & Chr(13) & Chr(10)
sqlstr = sqlstr & "r.referral_number, o.appointment_datetime, rr.review_datetime," & Chr(13) & Chr(10)
sqlstr = sqlstr & " u.name as REVIEWER, rr.appointment_request_status_name as" & Chr(13) & Chr(10)
sqlstr = sqlstr & "REQUEST_STATUS, rc.review_comment" & Chr(13) & Chr(10)
sqlstr = sqlstr & "from CHCS.ORDER_101 o, CHCS.PATIENT_2 p, CHCS.MCP_REFERRAL_8554 r," & Chr(13) & Chr(10)
sqlstr = sqlstr & "CHCS.PROVIDER_6 md, CHCS.ORDER_PRIORITY_102_3 op," & Chr(13) & Chr(10)
sqlstr = sqlstr & "CHCS.SUB_APPOINTMENT_REQUEST_REVIEW_8554_09 rr, CHCS.USER_3 u," & Chr(13) & Chr(10)
sqlstr = sqlstr & "CHCS.SUB_REVIEW_COMMENT_8554_1 rc" & Chr(13) & Chr(10)
sqlstr = sqlstr & "where o.order_type_name = 'CON'" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and p.ien = o.patient" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and o.referral_number = r.ien" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and md.ien = r.referred_by" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and op.ien = r.priority" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and r.ien = rr.mcp_referral_8554" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and rr.appointment_request_status_name in ('APPOINT TO MTF', 'Keevill')" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and rr.reviewer = u.ien" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and rc.SUB_APPOINTMENT_REQUEST_REVIEW_8554_09 = rr.rowid" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and o.id_number between '" & bGin & "-' AND '" & bendr & "-'" & Chr(13) & Chr(10) & Chr(13) & Chr(10) '160101-' and '160331 -'," & Chr(13) & Chr(10)
sqlstr = sqlstr & "group by p.name,o.ancillary_procedure_name, r.referral_number,CONVERT(datetime,CAST(rr.review_datetime AS date),105)" & Chr(13) & Chr(10)
sqlstr = sqlstr & "union" & Chr(13) & Chr(10)
sqlstr = sqlstr & "select p.name, p.dob, p.sponsor_ssn, p.fmp, op.name as PRIORITY, md.name as" & Chr(13) & Chr(10)
sqlstr = sqlstr & "REFERRED_BY, o.ancillary_procedure_name,r.referral_datetime, o.id_number as ORDER_ID_NUMBER," & Chr(13) & Chr(10)
sqlstr = sqlstr & "r.referral_number, o.appointment_datetime,MAX(rr.review_datetime)," & Chr(13) & Chr(10)
sqlstr = sqlstr & " u.name as REVIEWER, rr.appointment_request_status_name as" & Chr(13) & Chr(10)
sqlstr = sqlstr & "REQUEST_STATUS, ' ' as review_comment" & Chr(13) & Chr(10)
sqlstr = sqlstr & "from CHCS.ORDER_101 o, CHCS.PATIENT_2 p, CHCS.MCP_REFERRAL_8554 r," & Chr(13) & Chr(10)
sqlstr = sqlstr & "CHCS.PROVIDER_6 md, CHCS.ORDER_PRIORITY_102_3 op," & Chr(13) & Chr(10)
sqlstr = sqlstr & " CHCS.SUB_APPOINTMENT_REQUEST_REVIEW_8554_09 rr, CHCS.USER_3 u" & Chr(13) & Chr(10)
sqlstr = sqlstr & "where o.order_type_name = 'CON'" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and p.ien = o.patient" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and o.referral_number = r.ien" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and md.ien = r.referred_by" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and op.ien = r.priority" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and r.ien = rr.mcp_referral_8554" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and rr.appointment_request_status_name in ('APPOINT TO MTF', 'Keevill')" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and rr.reviewer = u.ien" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and 1>" & Chr(13) & Chr(10)
sqlstr = sqlstr & " (select count(*) from CHCS.SUB_REVIEW_COMMENT_8554_1 rc where" & Chr(13) & Chr(10)
sqlstr = sqlstr & "rc.SUB_APPOINTMENT_REQUEST_REVIEW_8554_09 = rr.rowid)" & Chr(13) & Chr(10)
sqlstr = sqlstr & " and o.id_number between '" & bGin & "-' AND '" & bendr & "-'" & Chr(13) & Chr(10) '160101-' and '160331 -'," & Chr(13) & Chr(10)


sqlstr = sqlstr & "group by p.name,o.ancillary_procedure_name, r.referral_number,CONVERT(datetime,CAST(rr.review_datetime AS date),105)" & Chr(13) & Chr(10)


strCacheQuery = sqlstr
'MsgBox strCacheQuery

On Error Resume Next
Set rsCache = cnCache.Execute(strCacheQuery)
If rsCache.EOF Then
On Error GoTo 0
MsgBox "No entries found"
Set rsCache = Nothing
Exit Sub
End If

rsCache.Close
Set rsCache = Nothing




MsgBox "Done!"


End Sub

 

[james_lankford]

it looks like you're using the old style of joining tables

old way

select 
  b.title,
  a.last_name 
from 
  books as b,
  authors as a 
where 
  b.author_id = a.author_id

new way

select 
  b.title,
  a.last_name 
from 
  books as b
  inner join 
  authors as a 
    on 
      b.author_id = a.author_id

I think some databases (maybe Oracle) still support the old way
but I don't know if Access does

but the "new" way has been the way since the mid 90's think
and all databases recommend doing it the new way

also, you use CAST and CONVERT
I don't know if Access knows those functions

so you'd have to come up with something else

 

[Rkeev]

Thanks James

 

[xenou]

You can use this link to convert sql text to VBA:
Instant SQL Formatter

It only scripts it out in VBA, it doesn't change functions from one brand of sql to another. Like James said, CAST and CONVERT are not keywords MSAccess will understand. You would maybe use DATEVALUE() to convert date strings to date (in some cases the conversion is explicit anyway). Access/VBA has CXXX() functions for casting: CLng(), CDate(), CStr() and so on.

 

[Rkeev]

Thank you xenou I'm still trying to get this code to run and anything that helps is appreciated.

 

[xenou]

Like Norie said, run it in Access before converting it to VBA. Then you know if it works or not. Also the Access query view will give you some minimal hints by putting the cursor on words that have syntax errors.