We help IT Professionals succeed at work.

How to convert VB script in to SQL Script

LakshmanaRavula
on
544 Views
Last Modified: 2007-12-19
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
Comment
Watch Question

jadedataMS Access Systems Creator
CERTIFIED EXPERT

Commented:
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

Commented:
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)

Author

Commented:
I'm Not clear Nico
Can you further explain me

Author

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

Commented:
Do you have the SQL string built in code ?
Do you know how to set a break in that code ?

Nic;o)

Commented:
That's a loop removing the query named "Final" when found.

Nic;o)

Author

Commented:
Yes that loop is understood

I know how to set a break in the code
Yes I have SQL strings built in codes

Commented:
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)

Author

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

Commented:
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)

Author

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

Commented:
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)

Author

Commented:
But I think there is some logic (where condition used while appending also can also analise that

Author

Commented:
Can you please analise the where condition

Author

Commented:
Can you also give me a SQL syntax for an alter code if so I shall paste it

Commented:
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)

Commented:
alter code ?

Nic;o)

Author

Commented:
Yes a code written to alter a table based on certain logics

Author

Commented:
COC TYPE                              "EOC"
What is this ?

Author

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);

Author

Commented:
I think the above has been fixed out shall I send you the remaining code

Commented:
OK

Nic;o)

Author

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

Commented:
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)

Author

Commented:
Ok I shall be waiting for Ypu

Commented:
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)

Author

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

Commented:
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)

Author

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

Commented:
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)

Author

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

Commented:
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)

Author

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

Author

Commented:
While I tried to run the query It is giving me syntax error

Commented:
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)

Author

Commented:
Nico,
the replacement has been done before commenting itself.

It is giving Syntax error (comma) in Query expression

Commented:
Can you paste it here ?

Nic;o)

Author

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)'

Commented:
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)

Author

Commented:
Now it is Data type mismatch Error

Author

Commented:
Data Type mismatch in Criteria expression

Commented:
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)

Author

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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank You Nico
Let me work it out
Shall be back on Monday

Commented:
OK, have a nice weekend !

Nic;o)

Author

Commented:
I'm Sorry
I've been working on other assignment which is given top priority
Thank You Nico

Commented:
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)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.