Access Text ETL Macro

 

 

Function LoadCDMth()

Dim strsql1 As String
Dim strsql2 As String
Dim strsql3 As String
Dim strsql4 As String
Dim datestr As String

datestr = CStr(Year(Date - Day(Date))) & IIf(Month(Date - Day(Date)) < 10, "0" & CStr(Month(Date - Day(Date))), _
CStr(Month(Date - Day(Date))))

DoCmd.SetWarnings False

DoCmd.TransferText acImportDelim, "Time Deposit Import Specification", _
"CDs" + datestr, "C:\Users\noswald\Documents\CDS" + datestr + ".txt", 0

strsql1 = "DELETE * FROM [CDs" & datestr & "]WHERE [accountno] is null;"
strsql2 = "Alter Table [CDs" & datestr & "] add column YearMonth int;"
strsql3 = "UPDATE [CDs" & datestr & "] SET YearMonth = " & datestr & ";"
strsql4 = "INSERT INTO dbo_CDS" + _
"( YearMonth, AccountNo, CertificateNo, Current_Balance, Dividend_Rate, Maturity_Date, Share_Type, Open_Date, Term, Frequency, Branch, Average_Balance, GL_Account, Share_ID )" + _
"SELECT YearMonth, AccountNo, CertificateNo, Current_Balance, Dividend_Rate, Maturity_Date, Share_Type, Open_Date, Term, Frequency, Branch, Average_Balance, GL_Account, Share_ID " + _
"FROM [CDs" & datestr & "];"

DoCmd.RunSQL strsql1
DoCmd.RunSQL strsql2
DoCmd.RunSQL strsql3
DoCmd.RunSQL strsql4

DoCmd.SetWarnings True

End Function


Back to Macros

Back to Resume

Back to About Me