Hello
I am new to Excel and VBA so excuse me if this makes no sense!
I am trying to import data - Invoices from a MYOB DB (Accounting Software) into Excel then export the Invoice data to an existing Access DB.
I have the import from MYOB sorted and can export the data to the Acces database ok, but are having a problem performing a check for duplicate records in Access before the Insert (The invoice Number from MYOB is going to be the MachineServiceID (PK) in the Access DB. I have read some posts on various forums and it seemed the best way to go was perform a DCOUNT and check if the cell(s) containing the MachineServiceID match the MachineServiceID's already in Access.
After adding the IF statement that includes the DCOUNT I now receive a: Compile Error: Sub or function not defined when trying to run the macro and DCOUNT is highlighted in blue after the error.
I can't work out what is causing the issue, below is my code, if someone could point me in the right direction where I am going wrong or let me know another option other than a DCOUNT would be great.
Thanks
Antony
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, i As Long, j As Long, t As String
Dim LTotal As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Users\Ant\Desktop\Database\SMData.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblMachineService", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 13 ' the start row in the worksheet
j = 0
t = "MYOB INV: "
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("MachineServiceID") = Range("A" &r).Value
.Fields("Comments") = t & Range("A" & r).Value
.Fields("DateCompleted") = Range("B" & r).Value
.Fields("ServiceCost") = Range("E" & r).Value
.Fields("Description") = Range("F" & r).Value
.Fields("MachineID") = Range("G" & r).Value
.Fields("CustomerID") = Range("H" & r).Value
If (DCount(.Fields("MachineServiceID"), "tblMachineService") = 0) Then
.Update ' stores the new record
Else
MsgBox "Invoice Number already entered into database"
'error handling to come
End If
End With
r = r + 1 ' next row
j = j + 1 'count total records added
Loop
MsgBox "Number of Invoices Added to Database: " & j & vbCrLf, vbInformation
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I am new to Excel and VBA so excuse me if this makes no sense!
I am trying to import data - Invoices from a MYOB DB (Accounting Software) into Excel then export the Invoice data to an existing Access DB.
I have the import from MYOB sorted and can export the data to the Acces database ok, but are having a problem performing a check for duplicate records in Access before the Insert (The invoice Number from MYOB is going to be the MachineServiceID (PK) in the Access DB. I have read some posts on various forums and it seemed the best way to go was perform a DCOUNT and check if the cell(s) containing the MachineServiceID match the MachineServiceID's already in Access.
After adding the IF statement that includes the DCOUNT I now receive a: Compile Error: Sub or function not defined when trying to run the macro and DCOUNT is highlighted in blue after the error.
I can't work out what is causing the issue, below is my code, if someone could point me in the right direction where I am going wrong or let me know another option other than a DCOUNT would be great.
Thanks
Antony
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, i As Long, j As Long, t As String
Dim LTotal As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Users\Ant\Desktop\Database\SMData.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblMachineService", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 13 ' the start row in the worksheet
j = 0
t = "MYOB INV: "
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("MachineServiceID") = Range("A" &r).Value
.Fields("Comments") = t & Range("A" & r).Value
.Fields("DateCompleted") = Range("B" & r).Value
.Fields("ServiceCost") = Range("E" & r).Value
.Fields("Description") = Range("F" & r).Value
.Fields("MachineID") = Range("G" & r).Value
.Fields("CustomerID") = Range("H" & r).Value
If (DCount(.Fields("MachineServiceID"), "tblMachineService") = 0) Then
.Update ' stores the new record
Else
MsgBox "Invoice Number already entered into database"
'error handling to come
End If
End With
r = r + 1 ' next row
j = j + 1 'count total records added
Loop
MsgBox "Number of Invoices Added to Database: " & j & vbCrLf, vbInformation
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
