March 1, 2010 at 1:30 pm
Hello,
I am currently trying to learn how to convert the following query from Access SQL / VB to t-SQL so I can create a package and automate the exporting process we currently do manually. Below is what we currently do in MSACCESS.
Dim dlgSaveAs As FileDialog
Dim strRawFileInfo As String
Dim strMailerExportPath As String
Dim strMailerExportFile As String
Dim qd As QueryDef
Dim sSQL As String
Dim sSQL2 As String
Dim sSQL3 As String
Dim sSQL4 As String
Dim sSQL5 As String
Dim sSQL6 As String
Dim sSQL7 As String
Dim sSQL8 As String
Dim sSQL9 As String
Dim sSQL10 As String
Dim sWhere As String
Dim fso As Object
Dim logfile As Object
On Error GoTo Proc_Err
'***************************************************************************
''Build the SQL First
''Set up the query definition
'UPDATE dbo_FileInfo
'SET dbo_FileInfo.SumOut = [dbo_FileInfo.SumNotes] & "[1]" & nz([dbo_FileInfo.ParcelNum],"") & "[2]" & nz([dbo_FileInfo.PriorBRT1],"") & "[3]" & nz([dbo_FileInfo.PriorBRT1From],"") & " " & nz([dbo_FileInfo.PriorBRT1To],"") & "[4]" & nz([dbo_FileInfo.PriorBRT1Open],"") & "[5]" & nz([dbo_FileInfo.AssessedBeg],"") & " " & nz([dbo_FileInfo.AssessedDim],"");
'dbo_FileInfo.LineDef, dbo_FileInfo.CaseNumber, dbo_FileInfo.BRTNumber, dbo_FileInfo.LegalDescription, dbo_FileInfo.coverDate, dbo_FileInfo.SumOut FROM dbo_FileInfo
'Format([dbo_FileInfo.CoverDate],'mmddyyyy')
'A|title order nbr|BRT nbr|actual legal description|cover dt|analyst comments[1]parcel number[2]prior BRT number [3]years due for prior BRT number in YYYY-YYYY format[4]amount due for prior BRT number in monetary format[5]assessed legal description[6]number of judgments[7]tax lien sale years[8]tax lien sale amount|
sSQL = "SELECT dbo_FileInfo.LineDef, dbo_FileInfo.CaseNumber, dbo_FileInfo.BRTNumber, dbo_FileInfo.LegalDescription, Format(dbo_FileInfo.CoverDate,'mmddyyyy') AS coverDate, dbo_FileInfo.SumOut FROM dbo_FileInfo WHERE "
'A1|lien instrument|lien type|lien number|lien amount|from year - to_prop_interest.from_year|to year|description| -- also includes property related documents easements etc.
sSQL4 = "SELECT dbo_municipalLiens.LineDef, dbo_municipalLiens.LienInstrument, dbo_municipalLiens.LienNumber, dbo_municipalLiens.LienAmount, Format(dbo_municipalLiens.LienDate,'mmddyyyy') AS LienDate, dbo_municipalLiens.LienReason FROM dbo_municipalLiens WHERE "
'B|line def|defendant type|defendant_nbr|first name||middle name|last name|aliases|capacity|company or estate name|add_1|add_2|city|state|zipcode|country|defendant instrument|executed on dt|recorded on dt|book|page|comments|
sSQL2 = "SELECT dbo_eInterest.LineDef, dbo_eInterest.defTypeCode, dbo_eInterest.eInterestFirst, dbo_eInterest.eInterestMiddle, dbo_eInterest.eInterestLast, dbo_eInterest.eInterestAlias, dbo_eInterest.eInterestCom, dbo_eInterest.first2, dbo_eInterest.mid2, dbo_eInterest.last2, dbo_eInterest.alias2, dbo_eInterest.capacity2, dbo_eInterest.com2, dbo_eInterest.eInterestAdd, dbo_eInterest.Add2, dbo_eInterest.eInterestCity, dbo_eInterest.eInterestState, dbo_eInterest.eInterestZip, dbo_eInterest.country, dbo_eInterest.eInterestDoc, dbo_eInterest.interestID FROM dbo_eInterest WHERE "
'B1|add_1|add_2|city|state|zipcode|country|
sSQL5 = "SELECT dbo_addresses.LineDef, dbo_addresses.address, dbo_addresses.add2, dbo_addresses.city, dbo_addresses.state, dbo_addresses.zip, dbo_addresses.country From dbo_addresses WHERE "
'B2|suiteNumber|date|chapter|vs|
sSQL6 = "SELECT dbo_banks.lineDef, dbo_banks.suiteNumber, Format(dbo_banks.bdate,'mmddyyyy') AS bdate, dbo_banks.chapter, dbo_banks.vs FROM dbo_banks WHERE "
'B3|docType|docAmount|docDate|recDate|bkpage|
sSQL7 = "SELECT dbo_documents.lineDef, dbo_documents.docType, dbo_documents.docAmount, Format(dbo_documents.docDate,'mmddyyyy') AS docDate, Format(dbo_documents.recDate,'mmddyyyy') As recDate, dbo_documents.bkpage, dbo_documents.grantee, dbo_documents.documentID FROM dbo_documents WHERE "
'C|grantordef|grantor
sSQL8 = "SELECT dbo_documents.CLineDef, dbo_documents.grantorDef, dbo_documents.grantor FROM dbo_documents WHERE "
'C|Tax Account parties
sSQL9 = "SELECT dbo_FileInfo.cLineDef, dbo_FileInfo.TaDef, dbo_FileInfo.TaxAccts FROM dbo_FileInfo WHERE "
'C|Registry parties
sSQL10 = "SELECT dbo_FileInfo.cLineDef, dbo_FileInfo.RegDef, dbo_FileInfo.Registry FROM dbo_FileInfo WHERE "
'C|type|ip_name|ip_address_1|ip_address_2|ip_city|ip_state|ip_zip_code|ip_country|judgment defendant name|judg def address 1|judg def address 2|judg def city|judg def state|judg def zip_code|judg def country|court_type|suit number|judgment_amt|judgment_date|revived_date|
sSQL3 = "SELECT dbo_selected_compiled_clean.LineDef, dbo_selected_compiled_clean.CType, dbo_selected_compiled_clean.plaintiffName, dbo_selected_compiled_clean.plaintiffAdd1, dbo_selected_compiled_clean.plaintiffCity, dbo_selected_compiled_clean.plaintiffState, dbo_selected_compiled_clean.defzip, dbo_selected_compiled_clean.plaintiffCountry, dbo_selected_compiled_clean.defendantName, dbo_selected_compiled_clean.defendantAdd1, dbo_selected_compiled_clean.defcity, dbo_selected_compiled_clean.defState, dbo_selected_compiled_clean.defZip,dbo_selected_compiled_clean.defCountry, dbo_selected_compiled_clean.Court, dbo_selected_compiled_clean.CaseID, dbo_selected_compiled_clean.JAmt, Format(dbo_selected_compiled_clean.JDate,'mmddyyyy') AS JDate, Format(dbo_selected_compiled_clean.revivedDate,'mmddyyyy') AS revivedDate FROM dbo_selected_compiled_clean WHERE "
'CASE RANGE
If Nz(Me.cboCaseBegin.Value, "") <> "" And Nz(Me.cboCaseEnd.Value, "") <> "" Then
sWhere = "[CaseNumber] BETWEEN '" & cboCaseBegin.Value & "' and '" & cboCaseEnd.Value & "' "
End If
'TARGET LIST
If Nz(Me.cboTargetList.Value, "") <> "" Then
If Len(sWhere) > 0 Then
sWhere = sWhere & " OR "
End If
sWhere = sWhere & "[TargetList] = " & cboTargetList.Value & " "
End If
'INDIVIDUAL CASES
If Nz(Me.txtIndCases.Value, "") <> "" Then
If Len(sWhere) > 0 Then
sWhere = sWhere & " OR "
End If
txtIndCases.SetFocus
sWhere = sWhere & "[CaseNumber] IN (" & txtIndCases.Text & ")"
End If
If Trim(sWhere) = "" Then
Err.Raise vbObjectError + 1000, csSource, "Must Choose at least one criteria"
End If
'''End Build SQL
'*******************************************************************
'''Choose the location of the file
' Create Save AS Dialog Box
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
'Use a With...End With block to reference the FileDialog object.
With dlgSaveAs
.Title = "Save Export File"
'The user pressed the action button.
If .Show = -1 Then
strRawFileInfo = .SelectedItems(1)
'The user pressed Cancel.
Else
GoTo Proc_Exit
End If
End With
' Extract Seperate Path and File Names from SaveAs Dialog Box
If strRawFileInfo <> "" Then
strMailerExportFile = Right(strRawFileInfo, Len(strRawFileInfo) - InStrRev(strRawFileInfo, "\"))
strMailerExportPath = Left(strRawFileInfo, Len(strRawFileInfo) - Len(strMailerExportFile))
End If
' Check file name for correct extension and if not present add .txt
If Right(strMailerExportFile, 4) <> ".txt" Then
' Check for wrong extension and strip it
If Mid((Right(strMailerExportFile, 4)), 1, 1) = "." Then
strMailerExportFile = Left(strMailerExportFile, Len(strMailerExportFile) - 4)
Else
End If
' Add Correct Extension
strMailerExportFile = strMailerExportFile & ".txt"
Else
End If
'End Get File Name
'*********************************************************************
''Lets build the file
sSQL = sSQL & sWhere
' sSQL2 = sSQL2 & sWhere
''Build the query def
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim rs4 As Recordset
Dim rs5 As Recordset
Dim rs6 As Recordset
Dim rs7 As Recordset
Dim rs8 As Recordset
Dim rs9 As Recordset
Dim rs20 As Recordset
Dim strSQL As String
Dim strCrit As String
Dim strCrit1 As String
Dim strCrit2 As String
Dim strCrit3 As String
Dim strCrit4 As String
Dim intCrit As Integer
intCrit = -1
Set rs1 = CurrentDb.OpenRecordset(sSQL)
If rs1.EOF Then
Err.Raise vbObjectError, csSource, "No Records Match Criteria"
Else
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strMailerExportPath & "\" & strMailerExportFile) Then
Kill (strMailerExportPath & "\" & strMailerExportFile)
End If
'Create the text file
fso.CreateTextFile (strMailerExportPath & "\" & strMailerExportFile) ' check this
'Write the fieldNames
Set logfile = fso.OpenTextFile(strMailerExportPath & "\" & strMailerExportFile, 8)
sTemp = ""
rs1.MoveFirst
'loop through each record in the first recordset
Do Until rs1.EOF
'set rs1 CaseNumber to strCrit for use throughout loop
strCrit = rs1![CaseNumber]
'open recordsets referencing current CaseNumber
Set rs2 = CurrentDb.OpenRecordset(sSQL2 & "CaseNumber = " & Chr(34) & strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])
Set rs3 = CurrentDb.OpenRecordset(sSQL3 & "CaseNumber = " & Chr(34) & strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])
Set rs4 = CurrentDb.OpenRecordset(sSQL4 & "CaseNumber = " & Chr(34) & strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])
' If matching record(s)are found for Line A then write record to text file
For Each fld In rs1.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs4.MoveFirst
Do Until rs4.EOF
For Each fld In rs4.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs4.MoveNext
Loop
rs4.Close
rs2.MoveFirst
' 'If matching record(s)are found for Line B then write record to text file and then the coorsponding B1, B2 and B3 records
Do Until rs2.EOF
sTemp = ""
For Each fld In rs2.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
'get interestID to pull the correct addresses for the B1 lines
strCrit1 = rs2![interestID]
strCrit2 = rs2![eInterestDoc]
Set rs5 = CurrentDb.OpenRecordset(sSQL5 & "interestID = " & Chr(34) & strCrit1 & Chr(34), dbOpenDynaset, [dbSeeChanges])
Set rs6 = CurrentDb.OpenRecordset(sSQL6 & "interestID = " & Chr(34) & strCrit1 & Chr(34), dbOpenDynaset, [dbSeeChanges])
If Not rs5.BOF And Not rs5.EOF Then
rs5.MoveFirst
Do Until rs5.EOF
sTemp = ""
For Each fld In rs5.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs5.MoveNext
Loop
rs5.Close
End If
'pull and write B2 lines -- bankruptcies
If Not rs6.BOF And Not rs6.EOF Then
rs6.MoveFirst
Do Until rs6.EOF
sTemp = ""
For Each fld In rs6.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs6.MoveNext
Loop
rs6.Close
End If
'write the first B3 line -- primary document for defendant
Set rs7 = CurrentDb.OpenRecordset(sSQL7 & "CaseNumber = " & Chr(34) & strCrit & Chr(34) & " And bkpage = " & Chr(34) & strCrit2 & Chr(34), dbOpenDynaset, [dbSeeChanges])
strCrit4 = rs7![documentID]
If strCrit4 = Null Then
strCrit4 = 10000000
End If
If Not rs7.BOF And Not rs7.EOF Then
rs7.MoveFirst
Do Until rs7.EOF
sTemp = ""
For Each fld In rs7.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
Set rs9 = CurrentDb.OpenRecordset(sSQL7 & "relatedDocID = " & Chr(34) & strCrit4 & Chr(34), dbOpenDynaset, [dbSeeChanges])
rs7.MoveNext
'pull and write the next B3 lines which are other related documents
If Not rs9.BOF And Not rs9.EOF Then
Do Until rs9.EOF
sTemp = ""
For Each fld In rs9.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs9.MoveNext
Loop
rs9.Close
End If
Loop
rs7.Close
End If
rs2.MoveNext
Loop
Set rs10 = CurrentDb.OpenRecordset(sSQL8 & "CaseNumber = " & Chr(34) & strCrit & Chr(34) & " And primaryDoc = -1", dbOpenDynaset, [dbSeeChanges])
rs10.MoveFirst
' write grantor of controlling "Deed" to the first "C" line
If Not rs10.BOF And Not rs10.EOF Then
Do Until rs10.EOF
sTemp = ""
For Each fld In rs10.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs10.MoveNext
Loop
End If
Set rs11 = CurrentDb.OpenRecordset(sSQL10 & "CaseNumber = " & Chr(34) & strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])
rs11.MoveFirst
' write registry bureau party to the second "C" line
If Not rs11.BOF And Not rs11.EOF Then
Do Until rs11.EOF
sTemp = ""
For Each fld In rs11.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs11.MoveNext
Loop
End If
Set rs12 = CurrentDb.OpenRecordset(sSQL9 & "CaseNumber = " & Chr(34) & strCrit & Chr(34), dbOpenDynaset, [dbSeeChanges])
rs12.MoveFirst
' write tax account party to the third "C" line
If Not rs12.BOF And Not rs12.EOF Then
Do Until rs12.EOF
sTemp = ""
For Each fld In rs12.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs12.MoveNext
Loop
End If
' write all judgments to file
If Not rs3.BOF And Not rs3.EOF Then
rs3.MoveFirst
Do Until rs3.EOF
sTemp = ""
For Each fld In rs3.Fields
sTemp = fld.Value & "|"
logfile.Write sTemp
Next
logfile.writeline
rs3.MoveNext
Loop
End If
rs1.MoveNext
Loop
rs1.Close
rs2.Close
rs3.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set rs4 = Nothing
Set rs5 = Nothing
Set rs6 = Nothing
Set rs7 = Nothing
'Set rs8 = Nothing
Set rs9 = Nothing
Set rs10 = Nothing
Set rs11 = Nothing
Set rs12 = Nothing
MsgBox strMailerExportPath & "\" & strMailerExportFile & " Created Successfully.", vbInformation
End If
Proc_Exit:
Set rs = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, vbExclamation, "Export File Error"
Resume Proc_Exit
I understand that this is quite long but what our client wanted us to do was export our data in a very specific format and this was the only way I could figure out how to do it.
I can write some t sql but have not dealt with data export and ONLY data manipulation which uses statements that I am guessing are quite different than the ones necessary for this function. Also we will not be needing to select a records to be exported as in the past but rather just export them based on time.
I just kind of need to be prodded in the right direction and answering these questions would be very helpful.
1. how does one create recordsets using t sql and if you do not create recordsets in t sql how do you do this type of output file?
1a. If it is possible to create recordsets what is the looping mechanism in t sql?
2. I know how to export files in SSIS but since this is pipe delimited using lettered lines (each line is a different table as this output has many one to many relationships) how do I go about the actual code to create the file and export it since I am assuming that SSIS cannot do that for me because this is not a type of file that is currently handled?
Any help would be great.
Thanks in advance,
George
March 1, 2010 at 1:46 pm
That is not Access SQL, that is Access Visual Basic.
March 1, 2010 at 1:51 pm
Michael Valentine Jones (3/1/2010)
That is not Access SQL, that is Access Visual Basic.
As with most Access it is a hybrid of Access SQL and VB... You write the queries in the editor and then port them into the VB structure. Either way not sure how that is entirely important.
March 1, 2010 at 2:01 pm
george.greiner (3/1/2010)
Michael Valentine Jones (3/1/2010)
That is not Access SQL, that is Access Visual Basic.As with most Access it is a hybrid of Access SQL and VB... You write the queries in the editor and then port them into the VB structure. Either way not sure how that is entirely important.
The point is that it is probably more direct to convert it to a SQL Server scripting environment, like DTS or SSIS, instead of to TSQL.
March 1, 2010 at 2:03 pm
push your access project to a test sqlinstance using Microsofts Migration assistant for Access to SQL2008 and see how it handles it !
This migration assistant is free !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 1, 2010 at 2:03 pm
Michael Valentine Jones (3/1/2010)
george.greiner (3/1/2010)
Michael Valentine Jones (3/1/2010)
That is not Access SQL, that is Access Visual Basic.As with most Access it is a hybrid of Access SQL and VB... You write the queries in the editor and then port them into the VB structure. Either way not sure how that is entirely important.
The point is that it is probably more direct to convert it to a SQL Server scripting environment, like DTS or SSIS, instead of to TSQL.
Okay if SSIS is the correct way to do this than I will venture that direction. Thanks.
March 1, 2010 at 2:06 pm
ALZDBA (3/1/2010)
push your access project to a test sqlinstance using Microsofts Migration assistant for Access to SQL2008 and see how it handles it !This migration assistant is free !
I will check that out thank you very much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply