Hi,

I am currently working with Access 20003. I've created a vbscript to import 2 queries and a macro from one db (access 2000) into another also access 2000. The queries get imported correctly but when it goes to do the macro import it fails, giving me a generic message indicating some process requires more memory but it does not state what process. The macro itself only has one line --- it calls one of the imported queries to output an xls file. Attached is the error I get.

Thanks for any help that can be given

Code: Non Relevant portions have been left out.



Option Explicit
Dim fso, oLogFile
Dim oAccess
Dim oConn
Dim bErr
Dim txtBody
Const acImport = 0
Const acQuery = 1
Const acMacro = 4
Const acQuitSaveAll = 1
Const NotifyEmail = "fredG@yahoo.com"
Const SupportEmail = "bevans@yahoo.com"
Const gMailServer = "smtp-server"
Const sAccessPath ="C:\Users\Tomas\Desktop\AIPREZ\BGH_998.mdb"
Const sLinkDB = "C:\Users\Tomas\Desktop\AIPREZ\BGH_custom.mdb"
Const sImportQuery1 = "Query1"
Const sImportQuery2 = "Query2"
Const sImportMacro = "BGHMacro"
Const sFile = "C:\Users\Tomas\Desktop\AIPREZ\BGHReq.xls"

Set fso = Createobject("Scripting.FileSystemObject")

'Main program
Main
Set fso = Nothing
bErr = False

Sub Main

Log Now() & vbTab & "BGH Main" & vbTab & "Script Start"

'GenerateReport and Email it
GenerateReport
Log Now() & vbTab & "BGH Main" & vbTab & "Script End"

End Sub

Sub GenerateReport()
Dim sErrMessage
Dim bLocalErr
bLocalErr = False
Set oAccess = CreateObject("Access.Application")
If Err.Number <> 0 Then
Log Now(), "BGH Main", "Creating MS Access Object: " & Err.Description, False
bErr = True

'Send email
txtBody = "BGH report failed"
'SendEmail NotifyEmail, "do_not_reply@yahoo.com", "BGH Report Export Failed", txtBody, ""
Exit Sub
End If

If bErr = False Then

Set oConn = CreateObject("ADODB.Connection")
oAccess.OpenCurrentDatabase sAccessPath,False
oAccess.Visible = False
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAccessPath &";Persist Security Info=False"

If Err.Number <> 0 Then
Log Now(), "BGH Main", "Connecting to MS Access Object: " & Err.Description, False
bErr = True

'clean up
oAccess.CloseCurrentDatabase
oAccess.Quit
Set oAccess = Nothing
'Send email
txtBody = "BGH report failed"
'SendEmail NotifyEmail, "do_not_reply@yahoo.com", "BGH report Export Failed", txtBody, ""
Exit Sub
End If

'when the db opens the switchboard auto launches so we need to close it first
'oAccess.DoCmd.RunMacro "CloseSwitchboard"

'import 1st query
sErrMessage = "Unable to import BGH query 1"
DoLink acImport, "Microsoft Access", sLinkDB, acQuery, sImportQuery1, False, sErrMessage
bLocalErr = bErr

'import 2nd query
If bLocalErr = False Then
sErrMessage = "Unable to import BGH query 2"
DoLink acImport, "Microsoft Access", sLinkDB, acQuery, sImportQuery2, False, sErrMessage
bLocalErr = bErr
End If

'import macro
If bLocalErr = False Then
sErrMessage = "Unable to import BGH macro"
DoLink acImport, "Microsoft Access", sLinkDB, acMacro, sImportMacro, True, sErrMessage
bLocalErr = bErr
End If

'Create Report
If bLocalErr = False Then
'CreateReport sImportMacro
'bLocalErr = bErr
MsgBox "Database has been closed", vbOK
End If 'end Create Report

'Send emails
If bLocalErr = False Then
txtBody = "BGH report was successfully exported"
'SendEmail NotifyEmail, "do_not_reply@yahoo.com", "BGH Report Export Successful", txtBody, sFile
Else
txtBody = "BGH report failed"
'SendEmail NotifyEmail, "do_not_reply@yahoo.com", "BGH Report Export Failed", txtBody, ""
End If

'clean up
oAccess.CloseCurrentDatabase
oAccess.Quit acQuitSaveAll
Set oAccess = Nothing
oConn.Close
Set oConn = Nothing

End If
End Sub

Sub OpenLog()
Set fso = Createobject("Scripting.FileSystemObject")
Set oLogFile = fso.OpenTextFile(Wscript.ScriptName & "_results.txt",8,true)
End Sub

Sub CloseLog()
oLogFile.close
Set oLogFile = Nothing
End Sub

Sub Log(byVal s)
Set oLogFile = fso.OpenTextFile(Wscript.ScriptName & "_results.txt",8,true)
oLogFile.WriteLine s
oLogFile.Close
Set oLogFile = Nothing
End Sub

Sub DoLink(sActionType,sDbName,sSourceDB,sDataType,sNa me,sStructureOnly,sErrMsg)
oAccess.DoCmd.TransferDatabase sActionType, sDbName, sSourceDB, sDataType, sName, sName, sStructureOnly
If Err.Number <> 0 Then
Log Now(), sErrMsg, Err.Description, False
bErr = True
End If
End Sub