469,142 Members | 981 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,142 developers. It's quick & easy.

Automation Error

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
1 5260
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.

Similar topics

3 posts views Thread by Robert | last post: by
2 posts views Thread by Andante.in.Blue | last post: by
25 posts views Thread by Neil Ginsberg | last post: by
12 posts views Thread by Cheval | last post: by
1 post views Thread by Jimmer | last post: by
12 posts views Thread by elziko | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.