Create AccDE from AccDb
in [Help]
|
From: Phil on 1 Jul 2010 07:08 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
From: David W. Fenton on 1 Jul 2010 15:31 "Phil" <phil(a)stantonfamily.co.uk> wrote in news:OsidnXWAbKvb6rHRnZ2dnUVZ7rOdnZ2d(a)brightview.co.uk: > On the slower machine the code below works and creates an AccDe > correctly, but it doesn't work on the faster machine. 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/
From: Phil on 1 Jul 2010 16:50 On 01/07/2010 20:31:03, "David W. Fenton" wrote: > "Phil" <phil(a)stantonfamily.co.uk> wrote in > news:OsidnXWAbKvb6rHRnZ2dnUVZ7rOdnZ2d(a)brightview.co.uk: > >> On the slower machine the code below works and creates an AccDe >> correctly, but it doesn't work on the faster machine. > > Does it report an error? Does it just stop? Does it complete but not > create the ACCDE? > 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
From: Tony Toews on 1 Jul 2010 17:23 On Thu, 1 Jul 2010 12:08:54 +0100, "Phil" <phil(a)stantonfamily.co.uk> wrote: >On the slower machine the code below works and creates an AccDe correctly, >but it doesn't work on the faster machine. 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/AlbertKallal/msaccess/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/
From: Phil on 1 Jul 2010 19:10
On 01/07/2010 22:23:31, Tony Toews wrote: > On Thu, 1 Jul 2010 12:08:54 +0100, "Phil" <phil(a)stantonfamily.co.uk> > wrote: > >>On the slower machine the code below works and creates an AccDe correctly, >>but it doesn't work on the faster machine. > > 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/AlbertKallal/msaccess/msaccess.html > > Tony 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 |