Avatar of mlcktmguy
Flag 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"

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

Next wkMuniNum


Open in new window

Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
Vitor Montalvão

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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?

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ão

I'm sorry. Looks like I didn't copy the URL and pasted an empty URL.
Here it is.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Can sproc's use FSO (file system object).  That might allow you to loop through a directory.