Solved

How to convert VB script in to SQL Script

Posted on 2004-03-24
48
471 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
0
Comment
Question by:LakshmanaRavula
  • 26
  • 21
48 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 10669288
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10669299
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
 

Author Comment

by:LakshmanaRavula
ID: 10669387
I'm Not clear Nico
Can you further explain me
0
 

Author Comment

by:LakshmanaRavula
ID: 10669590
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10669591
Do you have the SQL string built in code ?
Do you know how to set a break in that code ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10669635
That's a loop removing the query named "Final" when found.

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10669768
Yes that loop is understood

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

Expert Comment

by:nico5038
ID: 10669811
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
 

Author Comment

by:LakshmanaRavula
ID: 10669892
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10670087
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
 

Author Comment

by:LakshmanaRavula
ID: 10670178
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10670257
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
 

Author Comment

by:LakshmanaRavula
ID: 10670298
But I think there is some logic (where condition used while appending also can also analise that
0
 

Author Comment

by:LakshmanaRavula
ID: 10670357
Can you please analise the where condition
0
 

Author Comment

by:LakshmanaRavula
ID: 10670392
Can you also give me a SQL syntax for an alter code if so I shall paste it
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10670440
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10670446
alter code ?

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10670528
Yes a code written to alter a table based on certain logics
0
 

Author Comment

by:LakshmanaRavula
ID: 10670565
COC TYPE                              "EOC"
What is this ?
0
 

Author Comment

by:LakshmanaRavula
ID: 10670613
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
 

Author Comment

by:LakshmanaRavula
ID: 10670681
I think the above has been fixed out shall I send you the remaining code
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10670744
OK

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10670775
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10670931
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:LakshmanaRavula
ID: 10671020
Ok I shall be waiting for Ypu
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10678188
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
 

Author Comment

by:LakshmanaRavula
ID: 10681435
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10682068
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
 

Author Comment

by:LakshmanaRavula
ID: 10682476
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10682581
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
 

Author Comment

by:LakshmanaRavula
ID: 10682668
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10687582
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
 

Author Comment

by:LakshmanaRavula
ID: 10689171
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
 

Author Comment

by:LakshmanaRavula
ID: 10689193
While I tried to run the query It is giving me syntax error
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10689590
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
 

Author Comment

by:LakshmanaRavula
ID: 10689916
Nico,
the replacement has been done before commenting itself.

It is giving Syntax error (comma) in Query expression
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10690258
Can you paste it here ?

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10691095
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10691240
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
 

Author Comment

by:LakshmanaRavula
ID: 10691290
Now it is Data type mismatch Error
0
 

Author Comment

by:LakshmanaRavula
ID: 10691302
Data Type mismatch in Criteria expression
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10691336
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
 

Author Comment

by:LakshmanaRavula
ID: 10691496
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10691598
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
 

Author Comment

by:LakshmanaRavula
ID: 10691933
Thank You Nico
Let me work it out
Shall be back on Monday
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10691975
OK, have a nice weekend !

Nic;o)
0
 

Author Comment

by:LakshmanaRavula
ID: 10724915
I'm Sorry
I've been working on other assignment which is given top priority
Thank You Nico
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10759796
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now