You are here:

Using MS Access/MS Access VBA link to SQL Server

Advertisement


Question
QUESTION: Hi Scott, I will try to be as detailed as possible. I have a database that is currently uploading a file and refreshing via a button press. I would like to change this so that it refreshes directly from a data set in SQL server. I am using MS Access 2007 and SQL Server 2008. Can you give an example of the code I should use. Would it be beneficial for me to include the original code? If so I can provide that for you. I appreciate Italy be difficult without knowing what data I'm using, how whatmy original code looks like etc but if you could help that would be great. If I need to provide more information just let me know.

Hope you can help (I'm sure you can)

Thanks

ANSWER: You have two choices here. You can either simply create an ODBC DSN connection and link the table to your Access application. Once linked it can be used like any Access table.

Or you can use a DSNless connection that can be established at the time you want to refresh the table. This thread: http://www.access-programmers.co.uk/forums/showthread.php?t=224121 should help you with that.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi Scott, thanks for the reply earlier, I took a looks t the link you suggested but I'm struggling with the syntax a little. I've put what I believe to be the main body of the code on the database I've inherited. Was wondering if you could see which of the two options is best and if the code could be completely replaced. Not sure if you can having very littl background knowledge interns of the functionality. Thanks


Option Compare Database

Private Sub Command0_Click()

DoCmd.OpenQuery "UpdateEsc"

dteNow = Format(Now, "dd/mm/yyyy")

If txtDate.Value = dteNow Then

MsgBox "Import Can only be ran once a day"

End

Else

DCDate = txtDate.Value

Call DailyImport

Call DCImportDoCmd.OpenQuery ("Update Initiated Loss")

DoCmd.OpenQuery ("Update Intiated Gain")

DoCmd.OpenQuery ("Update DC")

End If

DoCmd.Close acForm, "Menu"

DoCmd.Close acForm, "Import"

DoCmd.OpenForm "Menu"

MsgBox "Done"

DoCmd.Close acForm, "Menu"

DoCmd.Close acForm, "Import"

DoCmd.OpenForm "Menu"

End Sub

Private Sub StatsEmail()

DoCmd.RunMacro "Escalation Output"

'elec

DoCmd.OpenForm "OutStandingIntiatedStat subform"

Intiated = Forms![OutStandingIntiatedStat subform]![CountOfID].Value

DoCmd.Close acForm, "OutStandingIntiatedStat subform"

DoCmd.OpenForm "Outstanding Intiated By Otehr subform"

Outstanding = Forms![Outstanding Intiated By Otehr subform]![CountOfID].Value

DoCmd.Close acForm, "Outstanding Intiated By Otehr subform"

DoCmd.OpenForm "InProgress Count subform"

Total = Forms![InProgress Count subform]![CountOfCountOfID].Value

DoCmd.Close acForm, "InProgress Count subform"

'oldest

DoCmd.OpenForm "Oldest Back subform"

Oldest = Forms![Oldest Back subform]![Date Started].Value

OldNote = Forms![Oldest Back subform]![Notes].Value

OldEsc = Forms![Oldest Back subform]![EscalatedTick].Value

OldMpan = Forms![Oldest Back subform]![MPAN].Value

DoCmd.Close acForm, "Oldest Back subform"

If OldEsc = "-1" Then

OldEsc = "Yes"

Else

OldEsc = "No"

End If

DoCmd.OpenForm "Escalation Count subform"

Exceptions = Forms![Escalation Count subform]![CountOfEscalated].Value

DoCmd.Close acForm, "Escalation Count subform"

DoCmd.OpenForm "Gas FU Outstanding Stat subform"

GasIntiated = Forms![Gas FU Outstanding Stat subform]![CountOfInitiating Organisation ID].Value

DoCmd.Close acForm, "Gas FU Outstanding Stat subform"

DoCmd.OpenForm "Gas Outstanding by Other Stat subform"

GasOutstanding = Forms![Gas Outstanding by Other Stat subform]![CountOfInitiating Organisation ID].Value

DoCmd.Close acForm, "Gas Outstanding by Other Stat subform"

DoCmd.OpenForm "Gas OutStanding subform"

GasTotal = Forms![Gas OutStanding subform]![CountOfresponse status].Value

DoCmd.Close acForm, "Gas OutStanding subform"

DoCmd.OpenForm "Gas Outstanding Back subform"

GasOldest = Forms![Gas Outstanding Back subform]![File Date].Value

DoCmd.Close acForm, "Gas Outstanding Back subform"

HTML2 = "<BR>Gas Stats are Below:<BR><BR><table style='border-collapse: collapse;' border='1' bordercolor='#000000'><tbody><tr><td>FU Initiated Outstanding</td><td> " & GasIntiated & "</td></tr><tr><td>Outstanding Initiated By Other Supplier  </td><td> " & GasOutstanding & "</td></tr><tr><td>Total Outstanding </td><td> " & GasTotal & " </td></tr><tr><td>Oldest Dispute Initiated Date </td><td> " & GasOldest & " </td></tr></tbody></table><Br><Br>These stats and email have been generated by the D300 Database.<BR><BR>Thanks"

HTMLOldest = "<BR>Details of the Oldest D300:<BR><BR><table style='border-collapse: collapse;' border='1' bordercolor='#000000'><tbody><tr><td>MPAN</td><td> " & OldMpan & "</td></tr><tr><td>Note</td><td> " & OldNote & "</td></tr><tr><td>Escalated  </td><td> " & OldEsc & "</td></tr></tbody></table><Br><a href='Z:\Dataflows\Reads\escalations.xls'>Click here for escalations Report</a><Br>"

Dim OutApp As Object

Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.To = "Mark Robinson"

.Subject = "D300 For " & Format(Now, "Long Date")

.Body = Chr(9) & Chr(149) & vbCrLf

.Display

.ReadReceiptRequested = False

.HTMLBody = "Hi,<Br><Br>Here is todays D300 Disputed Read Stats:<BR><BR><table style='border-collapse: collapse;' border='1' bordercolor='#000000'><tbody><tr><td>FU Initiated Outstanding</td><td> " & Intiated & "</td></tr><tr><td>Outstanding Initiated By Other Supplier  </td><td> " & Outstanding & "</td></tr><tr><td>Total Outstanding </td><td> " & Total & " </td></tr><tr><td>Oldest Dispute Initiated Date </td><td> " & Oldest & " </td></tr><tr><td>Escalated </td><td> " & Exceptions & " </td></tr></tbody></table>" & HTMLOldest & HTML2

'.Send

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing

Dim Msg, Style, Title

Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."

Title = "Open Issues List"

End Sub

Private Sub DCImport()

Set dbsCurrent = CurrentDb

Set wbkThis = CreateObject("Excel.Application")

With wbkThis

.Visible = False

.Workbooks.Open ("\\lhm-srv-frm02\OUTUFF\Hold\Reports\D300 Disputed Reads.xls")

End With

'fills in MPANs

IntIndex = 5

Do

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = "" Then

wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex - 1).Copy wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex)

wbkThis.sheets("D300 Disputed Reads").range("AH" & IntIndex).Value = "F"

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

'deletes supplier

IntIndex = 4

Do

If wbkThis.sheets("D300 Disputed Reads").range("AE" & IntIndex).Value = "X" Then

wbkThis.sheets("D300 Disputed Reads").Rows(IntIndex).EntireRow.Delete

IntIndex = 4

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

IntIndex = 5

'deletes anthing before date in text box

IntIndex = 5

delDate = DCDate

Do

If wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value < delDate Then

wbkThis.sheets("D300 Disputed Reads").Rows(IntIndex).EntireRow.Delete

IntIndex = 5

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

IntIndex = 5

strMPAN2 = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

IntIndex = 6

Do

If CStr(wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value) = strMPAN2 Then

wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = "" Else

strMPAN2 = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

OpenRecord = False

dteNow = Format(Now, "dd/mm/yyyy")

Do

IntIndex = 6

Do

'checks for blanks

If Not wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = "" Then

dteFlow = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

If dteFlow = DCDate Then

OpenRecord = False

strMPAN = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

strSQL = "SELECT ID, Status,MPAN FROM Details WHERE MPAN= '" & strMPAN & "'"

Set rsCount = dbsCurrent.OpenRecordset(strSQL)

If rsCount.RecordCount > 0 Then

'MsgBox rsCount.RecordCount

'if there is an exsisiting rescord it checks to see if open, if open it updates,if not it ads new

OpenRecord = False

Do Until rsCount.EOF Or OpenRecord = True

If Not rsCount("Status").Value = "Complete" Then

OpenRecord = True

End If

If OpenRecord = False Then

rsCount.MoveNext

End If

Loop

End If

If OpenRecord = True Then

'D300 add--          

'links ID

D300ID = rsCount("ID")

'Sets status

rsCount.Edit

rsCount("Status").Value = "D300 Received From DC"

rsCount.Update

Set rsD300 = dbsCurrent.OpenRecordset("D300")

rsD300.AddNew

'enter details ID

rsD300("Detail ID").Value = D300ID

'sent by

rsD300("Sent By").Value = wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

'Additional Information

rsD300("Additional Information").Value = wbkThis.sheets("D300 Disputed Reads").range("S" & IntIndex).Value

'Record Rejection Reason Indicator

rsD300("Record Rejection Reason Indicator").Value = wbkThis.sheets("D300 Disputed Reads").range("N" & IntIndex).Value

'Date Sent or Received

rsD300("Date Sent or Received").Value = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

'D300 reads & meter

'read/meter 1

'meter 1

rsD300("Meter Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value

'Reg Id 1

rsD300("Reg Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex).Value

'Read 1

rsD300("Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex).Value

'Read Type 1

rsD300("Read Type 1").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex).Value

'COS Read Rejection Code 1

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 1").Value = Rejection

'Proposed Read 1

rsD300("Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex).Value

'Status of Proposed Read 1

rsD300("Status of Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex).Value

'read/meter 2

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 1).Value = "" Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value Then

'meter 2

rsD300("Meter Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value

End If

'Reg Id 2

rsD300("Reg Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 1).Value

'Read 2

rsD300("Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 1).Value

'Read Type 2

rsD300("Read Type 2").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 1).Value

'COS Read Rejection Code 2

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 1).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 2").Value = Rejection

'Proposed Read 2

rsD300("Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 1).Value

'Status of Proposed Read 2

rsD300("Status of Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 1).Value

Else

D300Read = True

End If

'read/meter 3

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 2).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value Then

'meter 3

rsD300("Meter Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value

End If

'Reg Id 3

rsD300("Reg Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 2).Value

'Read 3

rsD300("Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 2).Value

'Read Type 3

rsD300("Read Type 3").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 2).Value

'COS Read Rejection Code 3

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 2).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 3").Value = Rejection

'Proposed Read 3

rsD300("Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 2).Value

'Status of Proposed Read 3

rsD300("Status of Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 2).Value

Else

D300Read = True

End If

'read/meter 4

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 3).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value Then

'meter 4

rsD300("Meter Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value

End If

'Reg Id 4

rsD300("Reg Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 3).Value

'Read 4

rsD300("Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 3).Value

'Read Type 4

rsD300("Read Type 4").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 3).Value

'COS Read Rejection Code 4

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 3).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 4").Value = Rejection

'Proposed Read 4

rsD300("Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 3).Value

'Status of Proposed Read 4

rsD300("Status of Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 3).Value

Else

D300Read = True

End If

'read/meter 5

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 4).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value Then

'meter 5

rsD300("Meter Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value

End If

'Reg Id 5

rsD300("Reg Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 4).Value

'Read 5

rsD300("Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 4).Value

'Read Type 5

rsD300("Read Type 5").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 4).Value

'COS Read Rejection Code 5

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 4).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 5").Value = Rejection

'Proposed Read 5

rsD300("Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 4).Value

'Status of Proposed Read 5

rsD300("Status of Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 4).Value

Else

D300Read = True

End If

'read/meter 6

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 5).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value Then

'meter 6

rsD300("Meter Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value

End If

'Reg Id 6

rsD300("Reg Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 5).Value

'Read 6

rsD300("Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 5).Value

'Read Type 6

rsD300("Read Type 6").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 5).Value

'COS Read Rejection Code 6

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 5).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 6").Value = Rejection

'Proposed Read 6

rsD300("Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 5).Value

'Status of Proposed Read 6

rsD300("Status of Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 5).Value

Else

D300Read = True

End If

'read/meter 7

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 6).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value Then

'meter 7

rsD300("Meter Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value

End If

'Reg Id 7

rsD300("Reg Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 6).Value

'Read 7

rsD300("Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 6).Value

'Read Type 7

rsD300("Read Type 7").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 6).Value

'COS Read Rejection Code 7

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 6).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 7").Value = Rejection

'Proposed Read 7

rsD300("Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 6).Value

'Status of Proposed Read 7

rsD300("Status of Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 6).Value

Else

D300Read = True

End If

'read/meter 8

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 7).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value Then

'meter 8

rsD300("Meter Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value

End If

'Reg Id 8

rsD300("Reg Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 7).Value

'Read 8

rsD300("Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 7).Value

'Read Type 8

rsD300("Read Type 8").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 7).Value

'COS Read Rejection Code 8

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 7).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 8").Value = Rejection

'Proposed Read 8

rsD300("Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 7).Value

'Status of Proposed Read 8

rsD300("Status of Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 7).Value

Else

D300Read = True

End If

'read/meter 9

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 8).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value Then

'meter 9

rsD300("Meter Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value

End If

'Reg Id 9

rsD300("Reg Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 8).Value

'Read 9

rsD300("Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 8).Value

'Read Type 9

rsD300("Read Type 9").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 8).Value

'COS Read Rejection Code 9

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 8).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 9").Value = Rejection

'Proposed Read 9

rsD300("Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 8).Value

'Status of Proposed Read 9

rsD300("Status of Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 8).Value

Else

D300Read = True

End If

rsD300.Update

End If

End If

End If       

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

DCDate = DCDate + 1

Loop Until DCDate = dteNow

wbkThis.ActiveWorkbook.Saved = True

wbkThis.Quit

End Sub

Private Sub DailyImport()

Set dbsCurrent = CurrentDb

Set wbkThis = CreateObject("Excel.Application")

With wbkThis

.Visible = False

.Workbooks.Open ("\\lhm-srv-frm02\OUTUFF\Hold\Reports\D300 Disputed Reads.xls")

End With

'fills in MPANs

IntIndex = 5

Do

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = "" Then

wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex - 1).Copy wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex)

wbkThis.sheets("D300 Disputed Reads").range("AH" & IntIndex).Value = "F"

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

'deletes DC

IntIndex = 4

Do

If wbkThis.sheets("D300 Disputed Reads").range("AE" & IntIndex).Value = "D" Then

wbkThis.sheets("D300 Disputed Reads").Rows(IntIndex).EntireRow.Delete

IntIndex = 5

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

IntIndex = 5

'deletes anthing before date in text box

IntIndex = 5

delDate = txtDate.Value

Do

If wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value < delDate Then

wbkThis.sheets("D300 Disputed Reads").Rows(IntIndex).EntireRow.Delete

IntIndex = 5

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

IntIndex = 5

strMPAN2 = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

IntIndex = 6

Do

If CStr(wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value) = strMPAN2 Then

wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = ""

Else

strMPAN2 = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

OpenRecord = False

dteNow = Format(Now, "dd/mm/yyyy")

Do

IntIndex = 6

Do

'checks for blank in MPAN feild

If Not wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value = "" Then

dteFlow = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

If dteFlow = txtDate.Value Then

OpenRecord = False

strMPAN = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

strSQL = "SELECT ID, Status, InitiatingFlowIndicator,MPAN FROM Details WHERE MPAN= '" & strMPAN & "'"

Set rsCount = dbsCurrent.OpenRecordset(strSQL)

If rsCount.RecordCount > 0 Then

'if there is an exsisiting rescord it checks to see if open, if open it updates,if not it ads new

OpenRecord = False

Do Until rsCount.EOF Or OpenRecord = True

If Not rsCount("Status").Value = "Complete" Then

OpenRecord = True

End If

If OpenRecord = False Then

rsCount.MoveNext

End If

Loop

End If

If OpenRecord = True Then

'D300 add--

'links ID

D300ID = rsCount("ID")

'adds not of sent flow

Module1.strNote = "Response Received from " & wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

Module1.intDetailID = rsCount("ID")

Call Module1.AddNote

rsCount.Edit

rsCount("Status").Value = "Response Received"

rsCount("InitiatingFlowIndicator").Value = "2- Any other D300"

rsCount.Update

Set rsD300 = dbsCurrent.OpenRecordset("D300")

rsD300.AddNew

'enter details ID

rsD300("Detail ID").Value = D300ID

'sent by

rsD300("Sent By").Value = wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

'Additional Information

rsD300("Additional Information").Value = wbkThis.sheets("D300 Disputed Reads").range("S" & IntIndex).Value

'Record Rejection Reason Indicator

rsD300("Record Rejection Reason Indicator").Value = wbkThis.sheets("D300 Disputed Reads").range("N" & IntIndex).Value

'Date Sent or Received

rsD300("Date Sent or Received").Value = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

'D300 reads & meter

'read/meter 1

'meter 1

rsD300("Meter Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value

'Reg Id 1

rsD300("Reg Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex).Value

'Read 1

rsD300("Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex).Value

'Read Type 1

rsD300("Read Type 1").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex).Value

'COS Read Rejection Code 1

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 1").Value = Rejection

'Proposed Read 1

rsD300("Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex).Value

'Status of Proposed Read 1

rsD300("Status of Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex).Value

'read/meter 2

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 1).Value = "" Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value Then

'meter 2

rsD300("Meter Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value

End If

'Reg Id 2

rsD300("Reg Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 1).Value

'Read 2

rsD300("Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 1).Value

'Read Type 2

rsD300("Read Type 2").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 1).Value

'COS Read Rejection Code 2

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 1).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 2").Value = Rejection

'Proposed Read 2

rsD300("Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 1).Value

'Status of Proposed Read 2

rsD300("Status of Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 1).Value

Else

D300Read = True

End If

'read/meter 3

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 2).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value Then

'meter 3

rsD300("Meter Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value

End If

'Reg Id 3

rsD300("Reg Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 2).Value

'Read 3

rsD300("Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 2).Value

'Read Type 3

rsD300("Read Type 3").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 2).Value

'COS Read Rejection Code 3

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 2).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 3").Value = Rejection

'Proposed Read 3

rsD300("Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 2).Value

'Status of Proposed Read 3

rsD300("Status of Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 2).Value

Else

D300Read = True

End If

'read/meter 4

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 3).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value Then

'meter 4

rsD300("Meter Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value

End If

'Reg Id 4

rsD300("Reg Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 3).Value

'Read 4

rsD300("Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 3).Value

'Read Type 4

rsD300("Read Type 4").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 3).Value

'COS Read Rejection Code 4

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 3).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 4").Value = Rejection

'Proposed Read 4

rsD300("Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 3).Value

'Status of Proposed Read 4

rsD300("Status of Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 3).Value

Else

D300Read = True

End If

'read/meter 5

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 4).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value Then

'meter 5

rsD300("Meter Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value

End If

'Reg Id 5

rsD300("Reg Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 4).Value

'Read 5

rsD300("Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 4).Value

'Read Type 5

rsD300("Read Type 5").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 4).Value

'COS Read Rejection Code 5

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 4).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 5").Value = Rejection

'Proposed Read 5

rsD300("Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 4).Value

'Status of Proposed Read 5

rsD300("Status of Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 4).Value

Else

D300Read = True

End If

'read/meter 6

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 5).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value Then

'meter 6

rsD300("Meter Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value

End If

'Reg Id 6

rsD300("Reg Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 5).Value

'Read 6

rsD300("Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 5).Value

'Read Type 6

rsD300("Read Type 6").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 5).Value

'COS Read Rejection Code 6

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 5).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 6").Value = Rejection

'Proposed Read 6

rsD300("Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 5).Value

'Status of Proposed Read 6

rsD300("Status of Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 5).Value

Else

D300Read = True

End If

'read/meter 7

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 6).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value Then

'meter 7

rsD300("Meter Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value

End If

'Reg Id 7

rsD300("Reg Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 6).Value

'Read 7

rsD300("Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 6).Value

'Read Type 7

rsD300("Read Type 7").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 6).Value

'COS Read Rejection Code 7

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 6).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 7").Value = Rejection

'Proposed Read 7

rsD300("Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 6).Value

'Status of Proposed Read 7

rsD300("Status of Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 6).Value

Else

D300Read = True

End If

'read/meter 8

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 7).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value Then

'meter 8

rsD300("Meter Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value

End If

'Reg Id 8

rsD300("Reg Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 7).Value

'Read 8

rsD300("Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 7).Value

'Read Type 8

rsD300("Read Type 8").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 7).Value

'COS Read Rejection Code 8

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 7).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 8").Value = Rejection

'Proposed Read 8

rsD300("Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 7).Value

'Status of Proposed Read 8

rsD300("Status of Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 7).Value

Else

D300Read = True

End If

'read/meter 9

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 8).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value Then

'meter 9

rsD300("Meter Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value

End If

'Reg Id 9

rsD300("Reg Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 8).Value

'Read 9

rsD300("Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 8).Value

'Read Type 9

rsD300("Read Type 9").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 8).Value

'COS Read Rejection Code 9

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 8).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 9").Value = Rejection

'Proposed Read 9

rsD300("Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 8).Value

'Status of Proposed Read 9

rsD300("Status of Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 8).Value

Else

D300Read = True

End If

rsD300.Update

End If

If OpenRecord = False Then

'new dispute adds New record

Set rsDetails = dbsCurrent.OpenRecordset("Details")

'adds new Record

D300Read = False

'Details add--

rsDetails.AddNew

'MPAN

rsDetails("MPAN").Value = wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex).Value

'Sets Intiated

rsDetails("Intiated by").Value = "Other Supplier"

'Date Started

rsDetails("Date Started").Value = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

'Other supplier MPID

rsDetails("Other supplier MPID").Value = wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

'MPAN Core Process

rsDetails("MPAN Core Process").Value = wbkThis.sheets("D300 Disputed Reads").range("J" & IntIndex).Value

'Sets status

rsDetails("Status").Value = "New D300 Received"

'New suppliers SSD

rsDetails("Effective from Settlement Date {REGI} for New Supplier").Value = wbkThis.sheets("D300 Disputed Reads").range("K" & IntIndex).Value

'address

rsDetails("Metering Point Address Line 1").Value = wbkThis.sheets("D300 Disputed Reads").range("T" & IntIndex).Value

rsDetails("Metering Point Address Line 2").Value = wbkThis.sheets("D300 Disputed Reads").range("U" & IntIndex).Value

rsDetails("Metering Point Address Line 3").Value = wbkThis.sheets("D300 Disputed Reads").range("V" & IntIndex).Value

rsDetails("Metering Point Address Line 4").Value = wbkThis.sheets("D300 Disputed Reads").range("W" & IntIndex).Value

rsDetails("Metering Point Address Line 5").Value = wbkThis.sheets("D300 Disputed Reads").range("X" & IntIndex).Value

rsDetails("Metering Point Address Line 6").Value = wbkThis.sheets("D300 Disputed Reads").range("Y" & IntIndex).Value

rsDetails("Metering Point Address Line 7").Value = wbkThis.sheets("D300 Disputed Reads").range("Z" & IntIndex).Value

rsDetails("Metering Point Address Line 8").Value = wbkThis.sheets("D300 Disputed Reads").range("AA" & IntIndex).Value

rsDetails("Metering Point Address Line 9").Value = wbkThis.sheets("D300 Disputed Reads").range("AB" & IntIndex).Value

'Post Code

rsDetails("Metering Point Postcode").Value = wbkThis.sheets("D300 Disputed Reads").range("AD" & IntIndex).Value

'Initiating Flow Indicator

rsDetails("InitiatingFlowIndicator").Value = wbkThis.sheets("D300 Disputed Reads").range("F" & IntIndex).Value

D300ID = rsDetails("ID")

rsDetails.Update

rsDetails.Close

'D300 add--

Set rsD300 = dbsCurrent.OpenRecordset("D300")

rsD300.AddNew

'enter details ID

rsD300("Detail ID").Value = D300ID

'sent by

rsD300("Sent By").Value = wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

'Additional Information

rsD300("Additional Information").Value = wbkThis.sheets("D300 Disputed Reads").range("S" & IntIndex).Value

'Record Rejection Reason Indicator

rsD300("Record Rejection Reason Indicator").Value = wbkThis.sheets("D300 Disputed Reads").range("N" & IntIndex).Value

'Date Sent or Received

rsD300("Date Sent or Received").Value = Format(wbkThis.sheets("D300 Disputed Reads").range("C" & IntIndex).Value, "dd/mm/yyyy")

'D300 reads & meter

'read/meter 1

'meter 1

rsD300("Meter Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value

'Reg Id 1

rsD300("Reg Id 1").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex).Value

'Read 1

rsD300("Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex).Value

'Read Type 1

rsD300("Read Type 1").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex).Value

'COS Read Rejection Code 1

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 1").Value = Rejection

'Proposed Read 1

rsD300("Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex).Value

'Status of Proposed Read 1

rsD300("Status of Proposed Read 1").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex).Value

'read/meter 2

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 1).Value = "" Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value Then

'meter 2

rsD300("Meter Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value

End If

'Reg Id 2

rsD300("Reg Id 2").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 1).Value

'Read 2

rsD300("Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 1).Value

'Read Type 2

rsD300("Read Type 2").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 1).Value

'COS Read Rejection Code 2

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 1).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 2").Value = Rejection

'Proposed Read 2

rsD300("Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 1).Value

'Status of Proposed Read 2

rsD300("Status of Proposed Read 2").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 1).Value

Else

D300Read = True

End If

'read/meter

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 2).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 1).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value Then

'meter 3

rsD300("Meter Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value

End If

'Reg Id 3

rsD300("Reg Id 3").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 2).Value

'Read 3

rsD300("Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 2).Value

'Read Type 3

rsD300("Read Type 3").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 2).Value

'COS Read Rejection Code 3

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 2).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 3").Value = Rejection

'Proposed Read 3

rsD300("Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 2).Value

'Status of Proposed Read 3

rsD300("Status of Proposed Read 3").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 2).Value

Else

D300Read = True

End If

'read/meter 4

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 3).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 2).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value Then

'meter 4

rsD300("Meter Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value

End If

'Reg Id 4

rsD300("Reg Id 4").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 3).Value

'Read 4

rsD300("Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 3).Value

'Read Type 4

rsD300("Read Type 4").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 3).Value

'COS Read Rejection Code 4

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 3).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 4").Value = Rejection

'Proposed Read 4

rsD300("Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 3).Value

'Status of Proposed Read 4

rsD300("Status of Proposed Read 4").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 3).Value

Else

D300Read = True

End If

'read/meter 5

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 4).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 3).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value Then

'meter 5

rsD300("Meter Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value

End If

'Reg Id 5

rsD300("Reg Id 5").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 4).Value

'Read 5

rsD300("Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 4).Value

'Read Type 5

rsD300("Read Type 5").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 4).Value

'COS Read Rejection Code 5

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 4).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 5").Value = Rejection

'Proposed Read 5

rsD300("Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 4).Value

'Status of Proposed Read 5

rsD300("Status of Proposed Read 5").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 4).Value

Else

D300Read = True

End If

'read/meter 6

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 5).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 4).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value Then

'meter 6

rsD300("Meter Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value

End If

'Reg Id 6

rsD300("Reg Id 6").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 5).Value

'Read 6

rsD300("Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 5).Value

'Read Type 6

rsD300("Read Type 6").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 5).Value

'COS Read Rejection Code 6

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 5).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 6").Value = Rejection

'Proposed Read 6

rsD300("Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 5).Value

'Status of Proposed Read 6

rsD300("Status of Proposed Read 6").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 5).Value

Else

D300Read = True

End If

'read/meter 7

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 6).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 5).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value Then

'meter 7

rsD300("Meter Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value

End If

'Reg Id 7

rsD300("Reg Id 7").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 6).Value

'Read 7

rsD300("Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 6).Value

'Read Type 7

rsD300("Read Type 7").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 6).Value

'COS Read Rejection Code 7

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 6).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 7").Value = Rejection

'Proposed Read 7

rsD300("Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 6).Value

'Status of Proposed Read 7

rsD300("Status of Proposed Read 7").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 6).Value

Else

D300Read = True

End If

'read/meter 8

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 7).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 6).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value Then

'meter 8

rsD300("Meter Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value

End If

'Reg Id 8

rsD300("Reg Id 8").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 7).Value

'Read 8

rsD300("Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 7).Value

'Read Type 8

rsD300("Read Type 8").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 7).Value

'COS Read Rejection Code 8

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 7).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 8").Value = Rejection

'Proposed Read 8

rsD300("Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 7).Value

'Status of Proposed Read 8

rsD300("Status of Proposed Read 8").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 7).Value

Else

D300Read = True

End If

'read/meter 9

'mpan check

If wbkThis.sheets("D300 Disputed Reads").range("A" & IntIndex + 8).Value = "" And D300Read = False Then

If Not wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 7).Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value Then

'meter 9

rsD300("Meter Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("H" & IntIndex + 8).Value

End If

'Reg Id 9

rsD300("Reg Id 9").Value = wbkThis.sheets("D300 Disputed Reads").range("I" & IntIndex + 8).Value

'Read 9

rsD300("Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("L" & IntIndex + 8).Value

'Read Type 9

rsD300("Read Type 9").Value = wbkThis.sheets("D300 Disputed Reads").range("M" & IntIndex + 8).Value

'COS Read Rejection Code 9

Rejection = Right(wbkThis.sheets("D300 Disputed Reads").range("O" & IntIndex + 8).Value, 4)

Rejection = Mid(Rejection, 2, 2)

rsD300("COS Read Rejection Code 9").Value = Rejection

'Proposed Read 9

rsD300("Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("P" & IntIndex + 8).Value

'Status of Proposed Read 9

rsD300("Status of Proposed Read 9").Value = wbkThis.sheets("D300 Disputed Reads").range("Q" & IntIndex + 8).Value

Else

D300Read = True

End If

'adds note of added flow

Module1.strNote = "New D300 received from " & wbkThis.sheets("D300 Disputed Reads").range("D" & IntIndex).Value

Module1.intDetailID = D300ID

Call Module1.AddNote

rsD300.Update

End If

End If

End If

IntIndex = IntIndex + 1

Loop Until wbkThis.sheets("D300 Disputed Reads").range("B" & IntIndex).Value = ""

txtDate.Value = txtDate.Value + 1

Loop Until txtDate.Value = dteNow

rsCount.Close

wbkThis.ActiveWorkbook.Saved = True

wbkThis.Quit

End Sub

Private Sub Command6_Click()

DCDate = txtDate.Value

Call DCImport

MsgBox "Done"

End Sub

Private Sub Command9_Click()

Call StatsEmail

End Sub

Private Sub Command10_Click()

Call StatsEmail

End Sub

Private Sub Command11_Click()

Call StatsEmail

End Sub

Private Sub Command12_Click()

Set wbkThis = CreateObject("Excel.Application")

With wbkThis

.Visible = True

.Workbooks.Open ("I:\Reads\D300DB\Files\BulkImport.xlsm")

End With

End Sub

Private Sub OpenCheck()

Set dbsCurrent = CurrentDb

strMPAN = txtMPAN.Value

strSQL = "SELECT ID, Status,MPAN FROM Details WHERE MPAN= '" & strMPAN & "'"

Set rsCount = dbsCurrent.OpenRecordset(strSQL)

If rsCount.RecordCount > 0 Then

'if there is an exsisiting rescord it checks to see if open, if open it updates,if not it ads new

OpenRecord = False

Do Until rsCount.EOF Or OpenRecord = True

If Not rsCount("Status").Value = "Complete" Then

OpenRecord = True

MsgBox "Open Recored for this MPAN already Exsists"

End

End If

If OpenRecord = False Then

rsCount.MoveNext

End If

Loop

End If

End Sub

Private Sub createIDs()

'takes values from settings

DoCmd.OpenForm "Settings"

strSuppID = Forms![Settings]![txtSupp].Value

StrFirstPath = Forms![Settings]![txtFirst].Value

StrSecondPath = Forms![Settings]![txtSecond].Value

DoCmd.RunMacro "CloseSettings"

'puts them togeter

strSuppID = strSuppID & Format(intD300ID, "0000000")

End Sub

Private Sub AddData()

Dim intID As Long

'add to table

Set dbTest = CurrentDb

'adds details

Set rsDetails = dbTest.OpenRecordset("Details")

'adds new Record

'Details add--

rsDetails.AddNew

'MPAN

rsDetails("MPAN").Value = wbkThis.sheets("Data").range("A" & intIntiate).Value

'Sets Intiated

rsDetails("Intiated by").Value = "First Utility/" & wbkThis.sheets("Data").range("C" & intIntiate).Value & " D86"

'Date Started

rsDetails("Date Started").Value = Format(Now, "dd/mm/yyyy")

'JIRA

rsDetails("JIRA").Value = wbkThis.sheets("Data").range("J" & intIntiate).Value

'DC MPID

rsDetails("DC MPID").Value = wbkThis.sheets("Data").range("E" & intIntiate).Value

'large EAC/AA

If wbkThis.sheets("Data").range("BR" & intIntiate).Value = "Yes" Then

rsDetails("Large EAC/AA").Value = True

Else

rsDetails("Large EAC/AA").Value = False

End If

'adds User

rsDetails("Assigned To").Value = wbkThis.sheets("Data").range("BQ" & intIntiate).Value

'date of Last action

rsDetails("Date of Last action").Value = Format(Now, "dd/mm/yyyy")

'Other supplier MPID

rsDetails("Other supplier MPID").Value = wbkThis.sheets("Data").range("F" & intIntiate).Value

'MPAN Core Process

rsDetails("MPAN Core Process").Value = wbkThis.sheets("Data").range("B" & intIntiate).Value

'Sets status

rsDetails("Status").Value = "Reading proposed"

'New suppliers SSD

rsDetails("Effective from Settlement Date {REGI} for New Supplier").Value = wbkThis.sheets("Data").range("D" & intIntiate).Value

'address

rsDetails("Metering Point Address Line 1").Value = wbkThis.sheets("Data").range("BG" & intIntiate).Value

rsDetails("Metering Point Address Line 2").Value = wbkThis.sheets("Data").range("BH" & intIntiate).Value

rsDetails("Metering Point Address Line 3").Value = wbkThis.sheets("Data").range("BI" & intIntiate).Value

rsDetails("Metering Point Address Line 4").Value = wbkThis.sheets("Data").range("BJ" & intIntiate).Value

rsDetails("Metering Point Address Line 5").Value = wbkThis.sheets("Data").range("BK" & intIntiate).Value

rsDetails("Metering Point Address Line 6").Value = wbkThis.sheets("Data").range("BL" & intIntiate).Value

rsDetails("Metering Point Address Line 7").Value = wbkThis.sheets("Data").range("BM" & intIntiate).Value

rsDetails("Metering Point Address Line 8").Value = wbkThis.sheets("Data").range("BN" & intIntiate).Value

rsDetails("Metering Point Address Line 9").Value = wbkThis.sheets("Data").range("BO" & intIntiate).Value

'Post Code

rsDetails("Metering Point Postcode").Value = wbkThis.sheets("Data").range("BP" & intIntiate).Value

'Initiating Flow Indicator

rsDetails("InitiatingFlowIndicator").Value = wbkThis.sheets("Data").range("I" & intIntiate).Value

intD300ID = rsDetails("ID")

intID = rsDetails("ID")

rsDetails.Update

rsDetails.Close

Set rstD300 = dbTest.OpenRecordset("D300")

'adds D300 to table

rstD300.AddNew

rstD300("Detail ID").Value = intD300ID

rstD300("Sent By").Value = "FRST"

rstD300("Additional Information").Value = wbkThis.sheets("Data").range("G" & intIntiate).Value

rstD300("Meter Id 1").Value = wbkThis.sheets("Data").range("H" & intIntiate).Value

rstD300("Reg Id 1").Value = wbkThis.sheets("Data").range("K" & intIntiate).Value

rstD300("Read 1").Value = wbkThis.sheets("Data").range("L" & intIntiate).Value

rstD300("Read Type 1").Value = wbkThis.sheets("Data").range("M" & intIntiate).Value

rstD300("COS Read Rejection Code 1").Value = wbkThis.sheets("Data").range("N" & intIntiate).Value

rstD300("Proposed Read 1").Value = wbkThis.sheets("Data").range("

" & intIntiate).Value

rstD300("Status of Proposed Read 1").Value = wbkThis.sheets("Data").range("P" & intIntiate).Value

'rstD300("Meter Id 2").Value = wbkThis.sheets("Data").range("Q" & intIntiate).Value

rstD300("Reg Id 2").Value = wbkThis.sheets("Data").range("R" & intIntiate).Value

rstD300("Read 2").Value = wbkThis.sheets("Data").range("S" & intIntiate).Value

rstD300("Read Type 2").Value = wbkThis.sheets("Data").range("T" & intIntiate).Value

rstD300("COS Read Rejection Code 2").Value = wbkThis.sheets("Data").range("U" & intIntiate).Value

rstD300("Proposed Read 2").Value = wbkThis.sheets("Data").range("V" & intIntiate).Value

rstD300("Status of Proposed Read 2").Value = wbkThis.sheets("Data").range("W" & intIntiate).Value

'rstD300("Meter Id 3").Value = wbkThis.sheets("Data").range("X" & intIntiate).Value

rstD300("Reg Id 3").Value = wbkThis.sheets("Data").range("Y" & intIntiate).Value

rstD300("Read 3").Value = wbkThis.sheets("Data").range("Z" & intIntiate).Value

rstD300("Read Type 3").Value = wbkThis.sheets("Data").range("AA" & intIntiate).Value

rstD300("COS Read Rejection Code 3").Value = wbkThis.sheets("Data").range("AB" & intIntiate).Value

rstD300("Proposed Read 3").Value = wbkThis.sheets("Data").range("AC" & intIntiate).Value

rstD300("Status of Proposed Read 3").Value = wbkThis.sheets("Data").range("AD" & intIntiate).Value

'rstD300("Meter Id 4").Value = wbkThis.sheets("Data").range("AE" & intIntiate).Value

rstD300("Reg Id 4").Value = wbkThis.sheets("Data").range("AF" & intIntiate).Value

rstD300("Read 4").Value = wbkThis.sheets("Data").range("AG" & intIntiate).Value

rstD300("Read Type 4").Value = wbkThis.sheets("Data").range("AH" & intIntiate).Value

rstD300("COS Read Rejection Code 4").Value = wbkThis.sheets("Data").range("AI" & intIntiate).Value

rstD300("Proposed Read 4").Value = wbkThis.sheets("Data").range("AJ" & intIntiate).Value

rstD300("Status of Proposed Read 4").Value = wbkThis.sheets("Data").range("AK" & intIntiate).Value

'rstD300("Meter Id 5").Value = wbkThis.sheets("Data").range("AL" & intIntiate).Value

rstD300("Reg Id 5").Value = wbkThis.sheets("Data").range("AM" & intIntiate).Value

rstD300("Read 5").Value = wbkThis.sheets("Data").range("AN" & intIntiate).Value

intD300ID = rstD300("ID").Value

rstD300("Read Type 5").Value = wbkThis.sheets("Data").range("AO" & intIntiate).Value

rstD300("COS Read Rejection Code 5").Value = wbkThis.sheets("Data").range("AP" & intIntiate).Value

rstD300("Proposed Read 5").Value = wbkThis.sheets("Data").range("AQ" & intIntiate).Value

rstD300("Status of Proposed Read 5").Value = wbkThis.sheets("Data").range("AR" & intIntiate).Value

'rstD300("Meter Id 6").Value = wbkThis.sheets("Data").range("AS" & intIntiate).Value

rstD300("Reg Id 6").Value = wbkThis.sheets("Data").range("AT" & intIntiate).Value

rstD300("Read 6").Value = wbkThis.sheets("Data").range("AU" & intIntiate).Value

rstD300("Read Type 6").Value = wbkThis.sheets("Data").range("AV" & intIntiate).Value

rstD300("COS Read Rejection Code 6").Value = wbkThis.sheets("Data").range("AW" & intIntiate).Value

rstD300("Proposed Read 6").Value = wbkThis.sheets("Data").range("AX" & intIntiate).Value

rstD300("Status of Proposed Read 6").Value = wbkThis.sheets("Data").range("AY" & intIntiate).Value

'rstD300("Meter Id 7").Value = wbkThis.sheets("Data").range("AZ" & intIntiate).Value

rstD300("Reg Id 7").Value = wbkThis.sheets("Data").range("BA" & intIntiate).Value

rstD300("Read 7").Value = wbkThis.sheets("Data").range("BB" & intIntiate).Value

rstD300("Read Type 7").Value = wbkThis.sheets("Data").range("BC" & intIntiate).Value

rstD300("COS Read Rejection Code 7").Value = wbkThis.sheets("Data").range("BD" & intIntiate).Value

rstD300("Proposed Read 7").Value = wbkThis.sheets("Data").range("BE" & intIntiate).Value

rstD300("Status of Proposed Read 7").Value = wbkThis.sheets("Data").range("BF" & intIntiate).Value

rstD300("Date Sent or Received").Value = Format(Now, "dd/mm/yyyy")

rstD300.Update

'adds not of sent flow

Module1.intDetailID = intID

Module1.strNote = "Initial D300 sent to " & wbkThis.sheets("Data").range("F" & intIntiate).Value & " by " & strUser

Call Module1.AddNote

End Sub

Private Sub createD300X()

'This collects all the data and puts it on a file names test.uff

linecount = 0

'this will create and open the file to be written to

Open "\\lhm-srv-frm02\OUTUFF\Hold\" & strSuppID & ".uff" For Append As #1

'stores the NOW date and time

strDateTime = CStr(Format(Now, "yyyymmddhhmmss"))

'compiles the header

Print #1, "ZHV|" & strSuppID & "|D0300001|X|FRST|X|" & wbkThis.sheets("Data").range("F" & intIntiate).Value & "|" & strDateTime & "|FORMF|||OPER|"

'stores the data for the current MPAN

strData1 = "58C|" & wbkThis.sheets("Data").range("A" & intIntiate).Value & "|" & Left(wbkThis.sheets("Data").range("B" & intIntiate).Value, 1) & "|" & Format(wbkThis.sheets("Data").range("D" & intIntiate).Value, "yyyymmdd") & "|" & wbkThis.sheets("Data").range("BG" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BH" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BI" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BJ" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BK" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BL" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BM" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BN" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BO" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("BP" & intIntiate).Value & "|" & wbkThis.sheets("Data").range("G" & intIntiate).Value & "||"

linecount = linecount + 1

Print #1, strData1

'meter 1

strData1 = "59C|" & wbkThis.sheets("Data").range("H" & intIntiate).Value & "|" & Left(wbkThis.sheets("Data").range("I" & intIntiate).Value, 1) & "|"

Print #1, strData1

linecount = linecount + 1

'Will print enter for reg & read 1

If Not wbkThis.sheets("Data").range("K" & intIntiate).Value = "" Then

strData2 = "60C|" & wbkThis.sheets("Data").range("K" & intIntiate).Value & "|" & Format(wbkThis.sheets("Data").range("L" & intIntiate).Value, "0.0") & "|" & wbkThis.sheets("Data").range("M" & intIntiate).Value & "|" & Left(wbkThis.sheets("Data").range("N" & intIntiate).Value, 2) & "|" & Format(wbkThis.sheets("Data").range("O" & intIntiate).Value, "0.0") & "|" & Left(wbkThis.sheets("Data").range("P" & intIntiate).Value, 1) & "|"

linecount = linecount + 1

Print #1, strData2

End If

'check for meter 2

If Not wbkThis.sheets("Data").range("Q" & intIntiate).Value = "" Then

strData1 = "59C|" & wbkThis.sheets("Data").range("Q" & intIntiate).Value & "|" & Left(wbkThis.sheets("Data").range("I" & intIntiate).Value, 1) & "|"

Print #1, strData1

linecount = linecount + 1

End If

'Will print enter for reg & read 2

If Not wbkThis.sheets("Data").range("R" & intIntiate).Value = "" Then

strData2 = "60C|" & wbkThis.sheets("Data").range("R" & intIntiate).Value & "|" & Format(wbkThis.sheets("Data").range("S" & intIntiate).Value, "0.0") & "|" & wbkThis.sheets("Data").range("T" & intIntiate).Value & "|" & Left(wbkThis.sheets("Data").range("U" & intIntiate).Value, 2) & "|" & Format(wbkThis.sheets("Data").range("V" & intIntiate).Value, "0.0") & "|" & Left(wbkThis.sheets("Data").range("W" & intIntiate).Value, 1) & "|"

linecount = linecount + 1

Print #1, strData2

End If

'compiles the footer

Print #1, "ZPT|" & strSuppID & "|" & linecount & "||1|" & strDateTime & "|"

Close #1

End Sub

Private Sub ConvertX()

' calls convert function and ouputs twice deleting the orginal file after the last output

'ouputs the file twice deleting test.uff after the second try

DeleteOriginal = False

Call ConvertFile("\\lhm-srv-frm02\OUTUFF\Hold\" & strSuppID & ".uff", StrFirstPath & strSuppID & ".uff", "dos2unix")

DeleteOriginal = True

Call ConvertFile("\\lhm-srv-frm02\OUTUFF\Hold\" & strSuppID & ".uff", StrSecondPath & strSuppID & ".uff", "dos2unix")

End Sub

Private Sub Command13_Click()

'rejection validation

Set wbkThis = CreateObject("Excel.Application")

With wbkThis

.Visible = True

.Workbooks.Open ("I:\Reads\D

Answer
OK, It looks like this code reads data from an Excel Workbook. It reads the workbook line by line and adds a record to the table. Not sure why this is necessary. But you would need to replace this code entirely. I don't know what is in the SQL Server table. Whether you need all the data or just some of it.

But the initial thing you need to do is connect to the table. Again you have 2 choices, an ODBC connection or a DSNless connection. Whichever you choose, the table becomes a linked table that you can either loop through or (preferably) use SQL queries to get at the data you want.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.