By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Automation Error

P: n/a
JC
I am getting a "Compile Error: Automation Error" with this function
and I can't figure out why. Can anyone help? The error hits as soon
as the function is recognized.

Function FileAppend(InTable As String, OutTable As String)
On Error GoTo Err_FileAppend

Dim MyDB As Database
Dim MyInTable As Recordset
Dim MyOutTable As Recordset
Dim MatchKey As String
Dim MatchOut As String
Dim X As Boolean
'Open table.
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyInTable = MyDB.OpenRecordset(InTable, DB_OPEN_DYNASET)
Set MyOutTable = MyDB.OpenRecordset(OutTable, DB_OPEN_DYNASET)
If MyOutTable.EOF Then
Else
MyOutTable.MoveFirst
End If
X = False

'Read first record.
MyInTable.MoveFirst
DoCmd.Hourglass True

'Check to see if record exists in outtable
Do Until MyInTable.EOF

MatchKey = "[symbol] = '" & MyInTable("symbol") & "' and "
MatchKey = MatchKey & "[Policy] = '" & MyInTable("Policy") & "'
and "
MatchKey = MatchKey & "[Mod] = '" & MyInTable("Mod") & "'"
If Not MyOutTable.EOF Then 'Makes sure that the OutTable has
records in it
MyOutTable.FindFirst MatchKey
If MyOutTable.NoMatch = False Then
MyOutTable.Edit
MyOutTable("ImportCounter") = MyOutTable("ImportCounter")
+ 1
MyOutTable.Update
X = True
End If

End If

If X = True Then 'Skip this record
X = False
Else 'Append this record
MyOutTable.AddNew
MyOutTable("AgntCde") = MyInTable("AgntCde")
MyOutTable("Sym") = MyInTable("Sym")
MyOutTable("PolNum") = MyInTable("PolNum")
MyOutTable("Mod") = MyInTable("Mod")
MyOutTable("DueDte") = MyInTable("DueDate")
MyOutTable("Amt") = MyInTable("Amt")
MyOutTable("UWCde") = MyInTable("UWCde")
MyOutTable("AcctTrx") = MyInTable("AcctTrx")
MyOutTable("PayCde") = MyInTable("PayCde")
MyOutTable("CloseDte") = MyInTable("CloseDte")
MyOutTable.Update
MyOutTable.MoveFirst
End If
MyInTable.MoveNext
Loop
DoCmd.Hourglass False

MyInTable.Close
MyOutTable.Close
Set MyInTable = Nothing
Set MyOutTable = Nothing

Exit_FileAppend:
Exit Function
Err_FileAppend:
MsgBox err.Number & " - " & err.Description, , "Function -
FileAppend"
Resume Exit_FileAppend

End Function
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Do you have a reference to DAO?

If you don't create one if you do try declaring the variables at the top as

Dim MyDB As DAO.Database
Dim MyInTable As DAO.Recordset
Dim MyOutTable As DAO.Recordset
Terry
"JC" <cl****@excite.com> wrote in message
news:36**************************@posting.google.c om...
I am getting a "Compile Error: Automation Error" with this function
and I can't figure out why. Can anyone help? The error hits as soon
as the function is recognized.

Function FileAppend(InTable As String, OutTable As String)
On Error GoTo Err_FileAppend

Dim MyDB As Database
Dim MyInTable As Recordset
Dim MyOutTable As Recordset
Dim MatchKey As String
Dim MatchOut As String
Dim X As Boolean
'Open table.
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyInTable = MyDB.OpenRecordset(InTable, DB_OPEN_DYNASET)
Set MyOutTable = MyDB.OpenRecordset(OutTable, DB_OPEN_DYNASET)
If MyOutTable.EOF Then
Else
MyOutTable.MoveFirst
End If
X = False

'Read first record.
MyInTable.MoveFirst
DoCmd.Hourglass True

'Check to see if record exists in outtable
Do Until MyInTable.EOF

MatchKey = "[symbol] = '" & MyInTable("symbol") & "' and "
MatchKey = MatchKey & "[Policy] = '" & MyInTable("Policy") & "'
and "
MatchKey = MatchKey & "[Mod] = '" & MyInTable("Mod") & "'"
If Not MyOutTable.EOF Then 'Makes sure that the OutTable has
records in it
MyOutTable.FindFirst MatchKey
If MyOutTable.NoMatch = False Then
MyOutTable.Edit
MyOutTable("ImportCounter") = MyOutTable("ImportCounter")
+ 1
MyOutTable.Update
X = True
End If

End If

If X = True Then 'Skip this record
X = False
Else 'Append this record
MyOutTable.AddNew
MyOutTable("AgntCde") = MyInTable("AgntCde")
MyOutTable("Sym") = MyInTable("Sym")
MyOutTable("PolNum") = MyInTable("PolNum")
MyOutTable("Mod") = MyInTable("Mod")
MyOutTable("DueDte") = MyInTable("DueDate")
MyOutTable("Amt") = MyInTable("Amt")
MyOutTable("UWCde") = MyInTable("UWCde")
MyOutTable("AcctTrx") = MyInTable("AcctTrx")
MyOutTable("PayCde") = MyInTable("PayCde")
MyOutTable("CloseDte") = MyInTable("CloseDte")
MyOutTable.Update
MyOutTable.MoveFirst
End If
MyInTable.MoveNext
Loop
DoCmd.Hourglass False

MyInTable.Close
MyOutTable.Close
Set MyInTable = Nothing
Set MyOutTable = Nothing

Exit_FileAppend:
Exit Function
Err_FileAppend:
MsgBox err.Number & " - " & err.Description, , "Function -
FileAppend"
Resume Exit_FileAppend

End Function

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.