How to convert VB script in to SQL Script

Hi

I've a VB code written behind a click button in the Access form
Which runs and creates a new tables extracting data from existing three tables

Now I need to convert this code in to SQL script.

can any body help in this regard or
Is there any utility for doing that

Thank You
LakshmanaRavulaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nico5038Connect With a Mentor Commented:
OK, first insert a numeric field Sequence the result table, then use a query like:

UPDATE Result SET Sequence = DCOUNT("NPA","Result","NPA=" & chr(34) & [NPA] & chr(34) & " AND NXX=" & chr(34) & [NXX] & chr(34) & " AND UniqueKey<=" & Uniquekey);

Now you can check to see or you have a John / 1 and a John / 2, etc

On this you can clean the table using:

delete * from result where sequence > 1;

Nic;o)
0
 
jadedataMS Access Systems CreatorCommented:
Greetings LakshmanaRavula!

 I am not aware of any tools to do this.
 You will need to take what you have and edit it into compliance with SQL Servers requirements

regards
jack
0
 
nico5038Commented:
I assume you create the SQL in code.
To get that you can place a breakpoint (click the left "line" to set a brown dot) and activate the code.
When it halts on the SQL creation line press F* till the string is filled and type in the immediate window (normally at the bottom):
?strSQL
(Assuming the strSQL holds the string)
The printed string can be copy/pasted into the query editor.

Clear ?

Nic;o)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LakshmanaRavulaAuthor Commented:
I'm Not clear Nico
Can you further explain me
0
 
LakshmanaRavulaAuthor Commented:
What does this do

For Each qry_v In db.QueryDefs
    v_name = qry_v.Name
    If v_name = "Final" Then
        DoCmd.DeleteObject acQuery, v_name
    End If
Nex
0
 
nico5038Commented:
Do you have the SQL string built in code ?
Do you know how to set a break in that code ?

Nic;o)
0
 
nico5038Commented:
That's a loop removing the query named "Final" when found.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Yes that loop is understood

I know how to set a break in the code
Yes I have SQL strings built in codes
0
 
nico5038Commented:
So place a break and make sure the string is filled.

Check that the immediate window is visible, when not select View/Immediate window.
To get the filled string you can print it in the immediate window by using the ? as print statement like:
? strSQL
Pressing enter will reveal the content and enable you to do a copy/paste to the clicpboard.

Clear sofar ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I'm extremly sorry i could not get you again

here is some code
can you explain me the logic

Dim db As Database

Set db = CurrentDb()


Dim rstQry As Recordset
Dim rstRslt As Recordset
Dim v_chknpa As Integer

Set rstQry = db.OpenRecordset("Query1", dbOpenDynaset)
Set rstRslt = db.OpenRecordset("Result", dbOpenTable)
rstQry.MoveFirst

Do Until rstQry.EOF
    If rstQry("COC TYPE") = "EOC" And Not IsNull(rstQry("SWITCH")) Then
'    If Not IsNull(rstQry("SWITCH")) Then
        If IsNumeric(rstQry("NXX")) Then
            If CLng(rstQry("LATA")) <> 888 And CInt(rstQry("NXX")) >= 200 Then
                    v_chknpa = CInt(rstQry("NPA"))
                    Select Case v_chknpa
                        Case 242, 246, 264, 268, 284, 340, 345, 441, 473, 500, 649, 664, 670, 671, 758, 767, 784, 787, 809, 868, 869, 876, 900, 939
                        Case Else
                        With rstRslt
                            .AddNew
                            !LATA = rstQry("LATA")
                            !NPA = rstQry("NPA")
                            !NXX = rstQry("NXX")
                            !LINEFR = rstQry("LINE FR")
                            !LINETO = rstQry("LINE TO")
                            ![LINE NUMS] = rstQry("LINE TO") - rstQry("LINE FR") + 1
                            !EFFDATE = rstQry("Lerg 6.EFF DATE")
                            !STATUS = rstQry("Lerg 6.STATUS")
                            !Switch = rstQry("SWITCH")
                            !SHA = rstQry("SHA INDICATOR")
                            !OCN = rstQry("OCN")
                            ![HOST-EFFDATE] = rstQry("Lerg 7 SHA.EFF DATE")
                            ![HOST-STATUS] = rstQry("Lerg 7 SHA.STATUS")
                            ![HOST] = rstQry("HOST")
                            ![C-TDM] = rstQry("H-TRM C TDM")
                            ![D-TDM] = rstQry("H-TRM D TDM")
                            ![SWH-STATE] = rstQry("LOC STATE")
                            .Update
                        End With
                    End Select
            End If
        End If
    End If
    rstQry.MoveNext
Loop
rstQry.Close
rstRslt.Close
Set rstQry = Nothing
Set rstRslt = Nothing
0
 
nico5038Commented:
I see, you have a recordset processing loop transferring a query ("Query1") to a table ("Result")
You can create a query for this by opening Query1 and change the query type into an append query.
When you do Access will ask for the target table, there enter "Result".
Next place the fields:
LATA
NPA
NXX
LINE FR => LINEFR
LINE TO => LINETO
[LINE TO] -[LINE FR] + 1 => [LINE NUMS]
EFF DATE => EFFDATE
STATUS
Switch
SHA INDICATOR => SHA
OCN
EFF DATE => [HOST-EFFDATE]
STATUS => [HOST-STATUS]
[HOST]
H-TRM C TDM =>[C TDM]
H-TRM D TDM =>[D-TDM]
LOC STATE => [SWH-STATE]
The single names will appear "automatically", the others you need to select from the target table, see the => value.

Clear ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
What to do with =>
I understood up to creating a append query and amfmiliar with that But here This type is very new for me
0
 
nico5038Commented:
When you place the field STATUS there needs to be a target field and that (initially empty) field needs to point at HOST-STATUS in the target table. (Just see the fields in the target table you need to chose from)

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
But I think there is some logic (where condition used while appending also can also analise that
0
 
LakshmanaRavulaAuthor Commented:
Can you please analise the where condition
0
 
LakshmanaRavulaAuthor Commented:
Can you also give me a SQL syntax for an alter code if so I shall paste it
0
 
nico5038Commented:
OK, here it goes:
Add these columns and place in the same criteria line to test:
COC TYPE                              "EOC"
SWITCH                                  not is null
Val(NXX)                                 >= 200
LATA                                      <> 888
NPA                                        NOT IN (242, 246, 264, 268, 284, 340, 345, 441, 473, 500, 649, 664, 670, 671, 758, 767, 784, 787, 809, 868, 869, 876, 900, 939)

Clear ?

Nic;o)
0
 
nico5038Commented:
alter code ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Yes a code written to alter a table based on certain logics
0
 
LakshmanaRavulaAuthor Commented:
COC TYPE                              "EOC"
What is this ?
0
 
LakshmanaRavulaAuthor Commented:
When I us like this It is giving me an error Data Type mismatch

Where [COC TYPE] = "EOC" AND [SWITCH]  <> null AND Val(NXX) >= 200  AND Val(LATA) <> 888
 AND NPA NOT IN (242, 246, 264, 268, 284, 340, 345, 441, 473, 500, 649, 664, 670, 671, 758, 767, 784, 787, 809, 868, 869, 876, 900, 939);
0
 
LakshmanaRavulaAuthor Commented:
I think the above has been fixed out shall I send you the remaining code
0
 
nico5038Commented:
OK

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I need the SQL for below code

Set rstRslt = db.OpenRecordset("Result", dbOpenTable)

rstRslt.MoveFirst
Do Until rstRslt.EOF
  v_status = rstRslt.Fields("STATUS")
  Select Case v_status
      Case "E"
          If (rstRslt.Fields![EFFDATE] < v_date) Then
              With rstRslt
                .Edit
                !STATUS = Null
                ![EFFDATE] = Null
                .Update
              End With
          Else
              rstRslt.Delete
          End If
      Case "M"
          If (rstRslt.Fields![EFFDATE] < v_date) Then
              v_npa = rstRslt.Fields!NPA
              v_nxx = rstRslt.Fields!NXX
              v_linefr = rstRslt.Fields("LINEFR")
              v_lineto = rstRslt.Fields("LINETO")
              rstRslt.MovePrevious
              vp_npa = rstRslt.Fields!NPA
              vp_nxx = rstRslt.Fields!NXX
              vp_linefr = rstRslt.Fields("LINEFR")
              vp_lineto = rstRslt.Fields("LINETO")
             
              If (v_npa = vp_npa) And (v_nxx = vp_nxx) Then
                  If (v_linefr = vp_linefr) And (v_lineto = vp_lineto) Then
                     rstRslt.Delete
                  Else
                     If CInt(v_lineto) - CInt(v_linefr) + 1 = 10000 Then
                        Dim partial As Integer
                        Dim remain As Integer
                        partial = CInt(vp_lineto) - CInt(vp_linefr) + 1
                        remain = 10000 - partial
                        rstRslt.MoveNext
                        Do Until remain = 0
                            rstRslt.MoveNext
                           
                            If rstRslt.Fields("Status") = "M" Or rstRslt.Fields("Status") = "D" Then
                               If rstRslt.Fields("Effdate") < v_date Then
                                    rstRslt.MovePrevious
                                    rstRslt.Delete
                                    rstRslt.MoveNext
                                    rstRslt.MoveNext
                                Else
                                    rstRslt.Delete
                                    rstRslt.MoveNext
                                End If
                            End If
                           
                            partial = CInt(rstRslt.Fields("LINETO")) - CInt(rstRslt.Fields("LINEFR")) + 1
                            remain = remain - partial
                            rstRslt.Delete
                        Loop
                        rstRslt.MovePrevious
                        rstRslt.MovePrevious
                        rstRslt.Delete
                     End If
                  End If
              Else
             
              End If
             
              With rstRslt
                .MoveNext
                .Edit
                !STATUS = Null
                !EFFDATE = Null
                .Update
              End With
          Else
              rstRslt.Delete
          End If
      Case "D"
          If (rstRslt.Fields![EFFDATE] < v_date) Then
              v_npa = rstRslt.Fields!NPA
              v_nxx = rstRslt.Fields!NXX
              v_linefr = rstRslt.Fields("LINEFR")
              v_lineto = rstRslt.Fields("LINETO")
              rstRslt.MovePrevious
              vp_npa = rstRslt.Fields!NPA
              vp_nxx = rstRslt.Fields!NXX
              vp_linefr = rstRslt.Fields("LINEFR")
              vp_lineto = rstRslt.Fields("LINETO")
              If (v_npa = vp_npa) And (v_nxx = vp_nxx) And (v_linefr = vp_linefr) And (v_lineto = vp_lineto) Then
                  rstRslt.Delete
              End If
              rstRslt.MoveNext
              rstRslt.Delete
          Else
              rstRslt.Delete
          End If
  End Select
  rstRslt.MoveNext
Loop
rstRslt.Close
Set rstRslt = Nothing
0
 
nico5038Commented:
Hmm, just start by yourself, I guess the previous one did show the way most of it just keep in mind:
1) every Case will require a different query
2) each IF will result in a WHERE

I'm very busy tonight, but will check in the morning where you ran into trouble.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Ok I shall be waiting for Ypu
0
 
nico5038Commented:
OK, ltes start with the first CASE and thus the first query:

      Case "E"
          If (rstRslt.Fields![EFFDATE] < v_date) Then
              With rstRslt
                .Edit
                !STATUS = Null
                ![EFFDATE] = Null
                .Update
              End With
          Else
              rstRslt.Delete
          End If

This implies an update query setting status and effdate to null with a WHERE [effdate] < v_date
For you the task to find out where the v_date is ciming from....

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Thanks Nico
That is done! and the rest of the thinks I think I can take care

But I need your help on this part

If (v_npa = vp_npa) And (v_nxx = vp_nxx) Then
                  If (v_linefr = vp_linefr) And (v_lineto = vp_lineto) Then
                     rstRslt.Delete
                  Else
                     If CInt(v_lineto) - CInt(v_linefr) + 1 = 10000 Then
                        Dim partial As Integer
                        Dim remain As Integer
                        partial = CInt(vp_lineto) - CInt(vp_linefr) + 1
                        remain = 10000 - partial
                        rstRslt.MoveNext
                        Do Until remain = 0
                            rstRslt.MoveNext
0
 
nico5038Commented:
When you see the code there's a command:
    rstRslt.MovePrevious
This implies that the previous record is read.
Thus the v_ and vp_ are comparing or there is a difference between two rows.
When no difference is found the duplicate row is deleted.

Clear ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I could able to walk through the code.

But he problem with me is converting it in to SQL. When we think of deleting duplicate rows again we have to go for a function.

else
make a query which displays the duplicates in a Table and then use the delete query based on to match the value in the Duplicates table and delete them .

Now the problem is when deleting the rows finding the matching with the duplicates table It is deleting the every occurence hence leaving no row for single occurence

How to avoid this.

Did I made some sense
0
 
nico5038Commented:
Yes, you did :-)

Normally duplicates are "removed" by using a GroupBy query for the fields that cause the duplication.
The problem however will be what row to select when there's a duplicate.
When the original data is sorted and has a unique key and you need the first or the last row, then use
linefr and lineto for the groupby and e.g. MIN or First to get the unique key.
When the complete lines are "full duplicates" a simple:
select distinct field1, field2, etc

will do.

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
No
I made the table finding the Duplicates as per the requirement. (I think I used the same way you said)

Now I have two Tables one is Main and the Other is duplicates.
Example: A name caleed "John' occured twice with the same cobination and it is in Duplicates Table

Now the problem is deleting the duplicates from the Main.

I could able to write the Query for this also.

But after deleting I could not able to see the Row containing 'John'
It should apear for a single occurance. But his is not happening
0
 
nico5038Commented:
Hmm, a solution is to add a column in the table (or query) counting the duplicates like:

select ("fieldname1","table","Fieldname1=" & chr(34) & [fieldname1] & chr(34) & " AND fieldname2=" & chr(34) & [fieldname2] & chr(34) & " AND UniqueKey<=" & Uniquekey) As DupCount, ....

Now you can run your queries with an additional:
WHERE DupCount = 1

Getting the idea ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I think there is a syntax error in
select ("fieldname1","table","Fieldname1=" & chr(34) & [fieldname1] & chr(34) & " AND fieldname2=" & chr(34) & [fieldname2] & chr(34) & " AND UniqueKey<=" & Uniquekey) As DupCount, ..
can you please debug and give me the correct syntax
0
 
LakshmanaRavulaAuthor Commented:
While I tried to run the query It is giving me syntax error
0
 
nico5038Commented:
You'll have to replave the field(s) with the appropriate values like:

select ("NPA","Result","NPA=" & chr(34) & [NPA] & chr(34) & " AND NXX=" & chr(34) & [NXX] & chr(34) & " AND UniqueKey<=" & Uniquekey) As DupCount, NPA, NXX FROM Query1

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Nico,
the replacement has been done before commenting itself.

It is giving Syntax error (comma) in Query expression
0
 
nico5038Commented:
Can you paste it here ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
SELECT ("NPA","Result","NPA=" & chr(34) & [NPA] & chr(34) & " AND NXX=" & chr(34) & [NXX] & chr(34) & " AND UniqueKey<=" & Uniquekey) As DupCount, NPA, NXX FROM Result

Syntax error (comma) in Query expression '("NPA","Result","NPA=" & chr(34) & [NPA] & chr(34) & " AND NXX=" & chr(34) & [NXX] & chr(34) & " AND UniqueKey<=" & Uniquekey)'
0
 
nico5038Commented:
Hmm, forgot the DCOUNT function, use:

SELECT DCOUNT("NPA","Result","NPA=" & chr(34) & [NPA] & chr(34) & " AND NXX=" & chr(34) & [NXX] & chr(34) & " AND UniqueKey<=" & Uniquekey) As DupCount, NPA, NXX FROM Result

BTW Are you sure the field [UniqueKey] exists in your table ?

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Now it is Data type mismatch Error
0
 
LakshmanaRavulaAuthor Commented:
Data Type mismatch in Criteria expression
0
 
nico5038Commented:
Verify or NPA and NXX are text fields. When numeric drop the "& chr(34)"
Check uniquekey is number if not use: UniqueKey<=" & chr(34) & Uniquekey & chr(34)

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
Ok
>Now you can run your queries with an additional:
>WHERE DupCount = 1

>Getting the idea ?

What is this Idea ?
My problem is
 
>I made the table finding the Duplicates as per the requirement. (I think I used the same way >you said)

>Example: A name caleed "John' occured twice with the same cobination and it is in >Duplicates Table

>Now the problem is deleting the duplicates from the Main.

>I could able to write the Query for this also.

>But after deleting I could not able to see the Row containing 'John'
>It should apear for a single occurance. But his is not happening
0
 
LakshmanaRavulaAuthor Commented:
Thank You Nico
Let me work it out
Shall be back on Monday
0
 
nico5038Commented:
OK, have a nice weekend !

Nic;o)
0
 
LakshmanaRavulaAuthor Commented:
I'm Sorry
I've been working on other assignment which is given top priority
Thank You Nico
0
 
nico5038Commented:
Can you drop the table zipped in my mail with this Q's URL so I can see the errors you get with this statement.

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.