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.
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
Microsoft AccessMicrosoft SQL Server
Last Comment
PatHartman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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?
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.
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.
Here it is.
Can sproc's use FSO (file system object). That might allow you to loop through a directory.