chanman525
IS-IT--Management
Hey all...I'm getting 2 errors when running my code.. Basically, I have a database that you enter a date into the form. It then calls multiple databases to import information for that certain date. The code is listed below.
'Database where you enter the date....
Function transfer_input_table()
On Error GoTo transfer_input_table_Err
Dim appAccess As New Access.Application
Dim trans As Database
Dim rec As Recordset
Dim frm As Form
Dim tb As TextBox
Dim usr As String
DoCmd.SetWarnings False
'update Status
Forms!input_date!lblStatus.ForeColor = 65535
Forms!input_date!lblStatus.Caption = "Importing Receiving History"
Forms!input_date.Refresh
Forms!input_date.Repaint
DoCmd.TransferDatabase acExport, "Microsoft Access", "H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb", acTable, "input_date", "input_date", False
appAccess.OpenCurrentDatabase ("H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb")
appAccess.Run "import"
DoCmd.TransferDatabase acExport, "Microsoft Access", "H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb", acTable, "input_date", "tblimportdate", False
appAccess.DoCmd.RunCommand acCmdCompactDatabase
appAccess.CloseCurrentDatabase
This does the same thing for about 8 other databases.
Code to run the import Receive Database....
Function import()
On Error GoTo import_Err
Dim receive As Database
Dim add_date As Field
Dim input_date As TableDef
Dim rec As Recordset
Dim path As String
Dim file_name As String
Dim import_filename As String
Dim month As String
Dim strFileExists As String
Dim year As String
Dim currentmonth As String
Dim Imported As Field
Dim strdate As String
Dim size As Long
Dim td As TableDef
size = FileLen(CurrentProject.FullName)
If size > 190000000 Then
MsgBox "Receive.mdb is too large, please contact DCO."
Else
For Each td In CurrentDb.TableDefs
'Don't delete any system tables
If Left(td.Name, 4) <> "MSys" Then
If td.DateCreated > DateAdd("d", 120, Now()) Then
If MsgBox("Are you sure you want to remove table td.name?", vbYesNo = vbTrue) Then
CurrentDb.TableDefs.Delete td.Name
End If
End If
End If
Next
Set receive = CurrentDb()
Set rec = receive.OpenRecordset("input_date")
path = "g:\prorep\tranhist\Receive\"
file_name = (rec!add_date & ".txt")
import_filename = path & file_name
strFileExists = dir(import_filename)
If strFileExists <> "" Then
year = Left$(file_name, 4)
month = Mid$(file_name, 5, 2)
currentmonth = year & month
If Nz(DLookup("Imported", "importdate", "Imported= '" & rec!add_date & "'")) Then
Quit acQuitSaveAll
Else
import_filename = path & file_name
DoCmd.TransferText acImportDelim, "receive spec", "tbl" & currentmonth, import_filename, False, ""
End If
' Delete input_date table
Set rec = receive.OpenRecordset("input_date")
Do
rec.Delete
rec.MoveNext
Loop Until rec.EOF
DoCmd.Quit acQuitSaveAll
import_Exit:
Exit Function
import_Err:
MsgBox Error$
Resume import_Exit
End If
End If
End Function
Somewhere, when the Transaction History Database is calling the function "import" to run in the receive database, I'm getting 2 errors....Automation Error and Method 'Run' of object '_application' failed.
I checked my references and none are "MISSING". Can anyone shed some light on what I am doing wrong?
'Database where you enter the date....
Function transfer_input_table()
On Error GoTo transfer_input_table_Err
Dim appAccess As New Access.Application
Dim trans As Database
Dim rec As Recordset
Dim frm As Form
Dim tb As TextBox
Dim usr As String
DoCmd.SetWarnings False
'update Status
Forms!input_date!lblStatus.ForeColor = 65535
Forms!input_date!lblStatus.Caption = "Importing Receiving History"
Forms!input_date.Refresh
Forms!input_date.Repaint
DoCmd.TransferDatabase acExport, "Microsoft Access", "H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb", acTable, "input_date", "input_date", False
appAccess.OpenCurrentDatabase ("H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb")
appAccess.Run "import"
DoCmd.TransferDatabase acExport, "Microsoft Access", "H:\Planning\Administration\Accts Payable\Transaction Histories1\Receive.mdb", acTable, "input_date", "tblimportdate", False
appAccess.DoCmd.RunCommand acCmdCompactDatabase
appAccess.CloseCurrentDatabase
This does the same thing for about 8 other databases.
Code to run the import Receive Database....
Function import()
On Error GoTo import_Err
Dim receive As Database
Dim add_date As Field
Dim input_date As TableDef
Dim rec As Recordset
Dim path As String
Dim file_name As String
Dim import_filename As String
Dim month As String
Dim strFileExists As String
Dim year As String
Dim currentmonth As String
Dim Imported As Field
Dim strdate As String
Dim size As Long
Dim td As TableDef
size = FileLen(CurrentProject.FullName)
If size > 190000000 Then
MsgBox "Receive.mdb is too large, please contact DCO."
Else
For Each td In CurrentDb.TableDefs
'Don't delete any system tables
If Left(td.Name, 4) <> "MSys" Then
If td.DateCreated > DateAdd("d", 120, Now()) Then
If MsgBox("Are you sure you want to remove table td.name?", vbYesNo = vbTrue) Then
CurrentDb.TableDefs.Delete td.Name
End If
End If
End If
Next
Set receive = CurrentDb()
Set rec = receive.OpenRecordset("input_date")
path = "g:\prorep\tranhist\Receive\"
file_name = (rec!add_date & ".txt")
import_filename = path & file_name
strFileExists = dir(import_filename)
If strFileExists <> "" Then
year = Left$(file_name, 4)
month = Mid$(file_name, 5, 2)
currentmonth = year & month
If Nz(DLookup("Imported", "importdate", "Imported= '" & rec!add_date & "'")) Then
Quit acQuitSaveAll
Else
import_filename = path & file_name
DoCmd.TransferText acImportDelim, "receive spec", "tbl" & currentmonth, import_filename, False, ""
End If
' Delete input_date table
Set rec = receive.OpenRecordset("input_date")
Do
rec.Delete
rec.MoveNext
Loop Until rec.EOF
DoCmd.Quit acQuitSaveAll
import_Exit:
Exit Function
import_Err:
MsgBox Error$
Resume import_Exit
End If
End If
End Function
Somewhere, when the Transaction History Database is calling the function "import" to run in the receive database, I'm getting 2 errors....Automation Error and Method 'Run' of object '_application' failed.
I checked my references and none are "MISSING". Can anyone shed some light on what I am doing wrong?
