I have 2 computers, both windows 7 & both Access2010.

On the slower machine the code below works and creates an AccDe correctly,
but it doesn't work on the faster machine.

I have tried to introduce delays to slow things down, but to no avail.

Any ideas please

Option Compare Database
Option Explicit

Function GenerateMDEFile()

Dim MailMergePath As String, MailMergeMDEPath As String
Dim appAccess As Access.Application

MailMergePath Path = Elookup("MailMergeDb", "QCoInfoPaths") ' with AccDb file
extension MailMergeMDEPath = Left(MailMergePath, Len(MailMergePath) - 1) &
"e" ' with AccDe file extension

Set appAccess = New Access.Application
appAccess.Visible = True
appAccess.SysCmd 603, MailMergePath, MailMergeMDEPath
appAccess.Quit acQuitSaveNone
Set appAccess = Nothing
DoEvents ' Might be a delay

End Function

Private Sub CreateDatabase_Click()

Dim MyDb As Database

Dim TDF As TableDef
Dim MailMergePath As String, Msg As String
Dim MailMergeMDEPath As String
Dim FS As Object
Dim i As Integer
Dim FrmName As String

On Error GoTo CreateDatabase_Err

Set MyDb = CurrentDb

MailMergePath = FindCoInfo("MailMergeDB", True)

Set FS = CreateObject("Scripting.FileSystemObject")

Call SysCmd(acSysCmdSetStatus, "Deleting Old MailMerge Database")
If If Dir(MailMergePath) <> "" Then ' Make sure that the data file exists
FS.DeleteFile leteFile MailMergePath, True ' Delete the old MailMerge
database End If

Call SysCmd(acSysCmdSetStatus, "Creating the MailMerge Database")
FS.copyfile CurrentDb.Name, MailMergePath

MailMergeMDEPath Path = Left(MailMergePath, Len(MailMergePath) - 1) & "e" '
with AccDe fileextension

If IsItMDE = True Then
GoTo AllOK
End If

Call GenerateMDEFile ' Generate AccDe from AccDb
DoEvents

CheckCreated:
If If Dir(MailMergeMDEPath) = "" Then ' Has the MDE been created?
Call apWait(0.5, True) ' Wait 1/2 second
i = i + 1
If i < 3 Then GoTo CheckCreated
If If MsgBox(MailMergeMDEPath & " file not yet created", vbCritical +
vbRetryCancel) = vbCancel Then GoTo CreateDatabase_Exit
Else
GoTo CheckCreated
End If
End If

Call SysCmd(acSysCmdSetStatus, "Deleting Old MailMerge Database")
If If Dir(MailMergePath) <> "" Then ' Make sure that the data file exists
FS.DeleteFile leteFile MailMergePath, True ' Delete the old MailMerge
database End If

FS.MoveFile File MailMergeMDEPath, MailMergePath ' Rename AccDe file as AccDb

AllOK:
DoEvents
Call SysCmd(acSysCmdClearStatus)

Msg = "MailMerge Database Created Succesfully as" & vbCrLf
Msg = Msg & MailMergePath
MsgBox Msg, vbInformation

For i = 0 To (Application.CurrentProject.AllForms.count - 1)
FrmName = Application.CurrentProject.AllForms(i).Name
If If FrmName = Elookup("FormMenu", "QCoInfoPaths") Then ' Menu found
FrmName = Application.CurrentProject.AllForms(i).Name
Exit For
End If
Next i

DoCmd.OpenForm FrmName
DoCmd.Close acForm, Me.Name

CreateDatabase_Exit:
Call SysCmd(acSysCmdClearStatus)
Exit Sub

CreateDatabase_Err:
MsgBox Err.Description
Resume CreateDatabase_Exit

End Sub

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:OsidnXWAbKvb6rHRnZ2dnUVZ7rOdnZ2d (AT) brightview (DOT) co.uk:

Does it report an error? Does it just stop? Does it complete but not
create the ACCDE?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 01/07/2010 20:31:03, "David W. Fenton" wrote:
Hi David

CheckCreated:
If Dir(MailMergeMDEPath) = "" Then ' Has the MDE been created?
Call apWait(0.5, True) ' Wait 1/2 second
i = i + 1
If i < 3 Then GoTo CheckCreated
If MsgBox(MailMergeMDEPath & " file not yet created", vbCritical +
vbRetryCancel) bRetryCancel) = vbCancel Then GoTo CreateDatabase_Exit
Else
GoTo CheckCreated
End If
End If

No error message but the line If Dir(MailMergeMDEPath) = "" returns a blank,
so I wait a bit & have another look. Obviously it drops thtogh to giving me
the message that the AccDe file has not been created.

For a moment I wondered if the SysCmd 603 was version specific in any way,
but as I said, identical Windows 7 and Identical Access 2020

As I say it works on the slower machine, but not the faster machine.

Thanks

Phil

On Thu, 1 Jul 2010 12:08:54 +0100, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Why do you need to create an ACCDE to do a mail merge?

For a possible alternative see
Albert Kallal's Super Easy Word Merge - Scroll down a ways
http://www.members.shaw.ca/AlbertKal.../msaccess.html

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

On 01/07/2010 22:23:31, Tony Toews wrote:
Hi Tony, its all a bit of a fiddle. I don't want the operator having access
(lower case) to the Mdb / AccDb file in case anything gets corrupted. So I
create an MDE / AccDE file and rename it either Mdb or AccDe, but at least
the code is not accessable and the forms can't be altered. Mailmerge won't
work with and MDE file (at least it didn't in AK2 and Word 2000) but works
perfectly OK if the MDE file is renamed as MDB. I also use the same file to
interface with Excel to create a labelled scatter chart (where boats are
located and a label to show the name and owner of each boat). Again that was
not available in the AK2 chart facilities so Excel extracts the data from the
fake AccDb file. Finally I have another DB that interrogates any Db to build
a list of Outlook contacts, and again I use the protected MailMerge.AccDb.
Probably making a mountain out of a molehill, but it's a pretty complicated
Db - over 17M for the front end, and I want to try to avoid it being messed
around with.

Have played around in the past with Albert's mailmerge.
Thanks

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:V_qdnaIxIfsQYrHRnZ2dnUVZ8gKdnZ2d (AT) brightview (DOT) co.uk:

You can try it interactively and see.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:z-OdndMT0prNvbDRnZ2dnUVZ8h2dnZ2d (AT) brightview (DOT) co.uk:

Whose mailmerge won't work with an MDE?

Sounds to me like you could get round this problem of renaming by
splitting your app, have the code part an MDE, and the data tables
an MDB, and Word, say, could then get to the tables in the MDB.

I am, of course, assuming that when you say MDB extension works and
MDE doesn't, that you're referring to an external program like Word.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 02/07/2010 23:28:36, "David W. Fenton" wrote:
Sorry for the delay in coming back - Sailing in Scotland.
The problem was with Office 2000 that Word would not find the queries in an
MDE file, but it appears to be solved in Office 2010. Needless to say in the
AK2 version I had the tables in the BE and the FE was the MDE file with the
queries. Anyway, thanks for your help, problem solved

Phil