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

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
The logic will be something like the following:
DECLARE @wkMuniNum INT = 1
DECLARE @wkMuniStr VARCHAR(MAX)

DECLARE @SQHD_Prefix CHAR(4) = 'SQHD'
DECLARE @wkFileExtension CHAR(4) = '.txt'
DECLARE @wkSearchFile VARCHAR(MAX)

WHILE @wkMuniNum <= 1000
BEGIN
	--Any files for this muni?  Pick one of the six to see if it's there.
    
    SET @wkMuniStr = Trim(Str(@wkMuniNum))
	SET @wkMuniStr = REPLICATE('0',4-LEN(@wkMuniNum)) + @wkMuniStr
    
    --Look for the first file
    SET @wkSearchFile = wkTaxDataPath + @SQHD_Prefix + @wkMuniStr + wkFileExtension
    
    If Len(Dir(@wkSearchFile)) > 0 Then
        --Logic if the file is found
    End If

	SET @wkMuniNum = @wkMuniNum+1
END

Open in new window

Mind that SQL Server doesn't have a command to access OS files, meaning that you need to find a way to create your own Dir function. For security reasons, I don't really recommend you to use SQL Server to access OS files but if you want to go forward with this, then you can try to adapt the code from this article.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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?
mlcktmguyAuthor Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry. Looks like I didn't copy the URL and pasted an empty URL.
Here it is.
PatHartmanCommented:
Can sproc's use FSO (file system object).  That might allow you to loop through a directory.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.