Link to home
Create AccountLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Convert Logic From VBA to SQL

I want to move a VBA process in my Access 2013 application to SQL.  The logic is pretty simple.  1. There is a loop, 2. left padding of a string and 3. checking  for the existence of a file.

I've done a little  T-SQL programming but nothing that had any of these 3 functionalities.

Can anyone help with the conversion?

This is the piece of logic I want to move into a SQL SP.
Dim wkMuniNum As Long
Dim wkMuniStr As String
'

SQHD_Prefix = "SQHD"
wkFileExtension=".txt"

For wkMuniNum = 1 To 1000
    '
    ' Any files for this muni?  Pick one of the six to see if it's there.
    '
    wkMuniStr = Trim(Str(wkMuniNum))
    If Len(wkMuniStr) = 4 Then
        '  nothing to do
    ElseIf Len(wkMuniStr) = 3 Then
        wkMuniStr = "0" & wkMuniStr
    ElseIf Len(wkMuniStr) = 2 Then
        wkMuniStr = "00" & wkMuniStr
    ElseIf Len(wkMuniStr) = 1 Then
        wkMuniStr = "000" & wkMuniStr
    End If
    '
    ' Look for the first file
    '
    wkSearchFile = wkTaxDataPath & SQHD_Prefix & wkMuniStr & wkFileExtension
    '
    If Len(Dir(wkSearchFile)) = 0 Then
        GoTo getNextMuni     '///////////////// get outof here and get next muni
    End If
.
. Logic if the file is found
.
.

getNextMuni:
Next wkMuniNum

  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I don't get the point of moving something that has nothing to do with updating a table from VBA to SQL Server.  What are you trying to build?
Avatar of mlcktmguy

ASKER

Pat, The 'Logic if the file is found' in the code calls a SPROC that processes the file found.  In reality the SPROC is called between 300 and 900 times every time this job executes.  Moving all of the logic to SQL would eliminate the 300 to 900 calls on every execution.  I thought this might improve performance.  That's why I wanted to try it all in SQL.  If SQL can't look for OS files, that's a deal breaker.

Vitor:  When I click the link to the article you reference, nothing happens.
I'm sorry. Looks like I didn't copy the URL and pasted an empty URL.
Here it is.
Can sproc's use FSO (file system object).  That might allow you to loop through a directory.