Ideas about how to make 'Application.ConvertAccessProject' behave?

[Guest]

Howdy, all1

I'm pretty darn good with Access and VB, but have a problem that's got me
stumped. It seems not all "msaccess.exe" pigs are not created equal.

Got an application that spits out summary table data to an external
database. The catch is that the new (external) database must in Access 2000
format (Feds, go figure.) To help automate what otherwise was a
time-consuming mess to convert, I added the
'application.convertaccessproject' method in my code. Problem is this: It
works pefect on my machine, but pops an error message on other machines that
"blah blah MS Acccess couldn't convert the database..." Stupid program does
the conversion nonetheless, but stops the code (goes into error mode) and
doesn't give the final dialog box.

I used the recommended code right from MS, and like I say, it works perfect
on my PC. All the references and libs are identical on both machines.
(Standard Office install.)

Ideas? Thanks to anybody who might have an insight to this oddity. BTW:
The code here may appear "Rube Goldberg" but that it has worked for years
without trouble, and to meet government reporting rules. Long story.

Thanks.

---------------------------------------------
Private Sub btnCreatePDR_Click()
Dim db As Database
Dim rs As Recordset
Dim sDate As String
Dim filename As String
Dim altfilename As String
Dim PDRFileName As String
Dim reportname As String
Dim altreportname As String
Dim Month As String
Dim Year As String
Dim PDRDir As String

Set db = CurrentDb
Set rs = db.OpenRecordset("INIADM")
'Set PDRDir = rs!PDRDir

Month = cboMonth
Year = Right(cboYear, 2)

'Next assign a provisional file name as DHLAmmyyA3 or S3 to denote a
2002-2003 file format. further code will convert it to a 2000 format
'Save the 2002-2003 file; Assign alternate file names for the 2000 format
file name

If Me.Program = "AQP" Then
reportname = "DHLA" & Month & Year & "a3" 'for 2002-2003 format (Temp
use for code only)
altreportname = "DHLA" & Month & Year & "a" 'for 2000 format (Permanent
copy for AFS-230)
ElseIf Me.Program = "SVT" Then 'for SVT data file only
reportname = "DHLA" & Month & Year & "s3" 'for 2002-2003 format (Temp
use for code only)
altreportname = "DHLA" & Month & Year & "s" 'for 2000 format (Permanent
copy for AFS-230)
End If
filename = rs!PDRDir & "\" & reportname & ".mdb"
altfilename = rs!PDRDir & "\" & altreportname & ".mdb"
If Dir(filename, 0) <> "" Then Kill filename
If Dir(altfilename, 0) <> "" Then Kill altfilename

CreateDatabase filename, dbLangGeneral 'create shell database DHLAmmyyA3 or S3

If Me.Program = "AQP" Then 'all three AQP tables
DoCmd.CopyObject filename, "Claim", acTable, "Claim" 'added 17 Jul 06
per Eric; confidentiality claim
DoCmd.CopyObject filename, "PDRT", acTable, "FAASVTPDR"
DoCmd.CopyObject filename, "SKLRSN", acTable, "FAASVTSKLRSN"
DoCmd.CopyObject filename, "TORT", acTable, "FAATORT"
ElseIf Me.Program = "SVT" Then 'SVT tables only
DoCmd.CopyObject filename, "Claim", acTable, "Claim" 'added 17 Jul 06
per Eric; confidentiality claim
DoCmd.CopyObject filename, "PDRT", acTable, "FAASVTPDR"
DoCmd.CopyObject filename, "SKLRSN", acTable, "FAASVTSKLRSN"
End If

Me.PDRFileName = altfilename 'Final name on the form (lower right box)

'2002-2003 file format has been created and saved. Now convert the file to
Access 2000 for AFS-230 'Added 12 Jun 2006
'Now take the provisional file name (2003 format) and convert it Access 2000
format

Application.ConvertAccessProject _
SourceFilename:=filename, _
DestinationFilename:=altfilename, _
destinationfileformat:=acFileFormatAccess2000

'If Dir(filename) <> "" Then Kill filename 'delete the 2002-2003 formatted
file, not needed.

If IsNull(rs!PDRDir) And Dir(altfilename) <> "" Then
Select Case MsgBox("The selected month was successfully created as PDR
file: " & altfilename & "." _
& Chr(10) & "" _
& Chr(10) & "The file was saved in the root directory because
you did not set a default directory." _
& Chr(10) & "Please set a default directory to stop seeing this
error message!" _
& Chr(10) & "" _
& Chr(10) & "Would you like to open the e-mail shell now and
send the file?", vbQuestion + vbOKCancel + vbDefaultButton2, "ASOK PDR
Builder")

Case vbOK
DoCmd.RunMacro ("PDR Email Shell")
Case vbCancel
End Select
ElseIf Not IsNull(rs!PDRDir) And Dir(altfilename) <> "" Then
Select Case MsgBox("The selected month was successfully created as PDR
file: " & altfilename & "." _
& Chr(10) & "This is the file to send to AFS-230. Good Job!" _
& Chr(10) & "" _
& Chr(10) & "Would you like to open the e-mail shell now and
send the file?", vbQuestion + vbOKCancel + vbDefaultButton2, "ASOK PDR Send")

Case vbOK
DoCmd.RunMacro ("PDR Email Shell")
Case vbCancel
End Select
End If

Exit_btnCreatePDR_Click:
Exit Sub
Err_btnCreatePDR_Click:
LogEvent Err, "frmDataCollection,btnCreatePDR_Click: " & Err.Description
' MsgBox Err.Description
Resume Exit_btnCreatePDR_Click
End Sub

 

[Marshall Barton]

I'm pretty darn good with Access and VB, but have a problem that's got me
stumped. It seems not all "msaccess.exe" pigs are not created equal.

Got an application that spits out summary table data to an external
database. The catch is that the new (external) database must in Access 2000
format (Feds, go figure.) To help automate what otherwise was a
time-consuming mess to convert, I added the
'application.convertaccessproject' method in my code. Problem is this: It
works pefect on my machine, but pops an error message on other machines that
"blah blah MS Acccess couldn't convert the database..." Stupid program does
the conversion nonetheless, but stops the code (goes into error mode) and
doesn't give the final dialog box.

I used the recommended code right from MS, and like I say, it works perfect
on my PC. All the references and libs are identical on both machines.
(Standard Office install.)

Ideas? Thanks to anybody who might have an insight to this oddity. BTW:
The code here may appear "Rube Goldberg" but that it has worked for years
without trouble, and to meet government reporting rules. Long story.

[snip code]

Maybe someone else can provide some insight into your
problem, but I think there's an alternative approach.

How about precreating an A2K database and, at runtime, using
FileCopy to make a working copy instead of using
CreateDatabase and converting it.

 

[Guest]

Marsh,

Many thanks for the quick response, and input. Sorry to say, the FileCopy
method is not workable, because of the intent of "automation". I would have
created up to 48 pre-exisisting databases to support a simple FileCopy
method. This is simply not workable.

The essential problem is the conversion of the newly created db to a 2000
file format without any error boxes from msaccesses.exe.

The example code sets a 'dbs.workspace' but firmly believe this to be a
red-herring, mainly since I don't need or access data on the converted db. I
may be wrong here. But still doesn't explain why it works perfectly on my
machine, but not on another? (Ref first post.)

Thanks for your continued interest in this problem. Any references or
suggestions are greatly appreciated.
--
(e-mail address removed)

I'm pretty darn good with Access and VB, but have a problem that's got me
stumped. It seems not all "msaccess.exe" pigs are not created equal.

Got an application that spits out summary table data to an external
database. The catch is that the new (external) database must in Access 2000
format (Feds, go figure.) To help automate what otherwise was a
time-consuming mess to convert, I added the
'application.convertaccessproject' method in my code. Problem is this: It
works pefect on my machine, but pops an error message on other machines that
"blah blah MS Acccess couldn't convert the database..." Stupid program does
the conversion nonetheless, but stops the code (goes into error mode) and
doesn't give the final dialog box.

I used the recommended code right from MS, and like I say, it works perfect
on my PC. All the references and libs are identical on both machines.
(Standard Office install.)

Ideas? Thanks to anybody who might have an insight to this oddity. BTW:
The code here may appear "Rube Goldberg" but that it has worked for years
without trouble, and to meet government reporting rules. Long story.

[snip code]

Maybe someone else can provide some insight into your
problem, but I think there's an alternative approach.

How about precreating an A2K database and, at runtime, using
FileCopy to make a working copy instead of using
CreateDatabase and converting it.

 

[Marshall Barton]

Marsh,

Many thanks for the quick response, and input. Sorry to say, the FileCopy
method is not workable, because of the intent of "automation". I would have
created up to 48 pre-exisisting databases to support a simple FileCopy
method. This is simply not workable.

The essential problem is the conversion of the newly created db to a 2000
file format without any error boxes from msaccesses.exe.

The example code sets a 'dbs.workspace' but firmly believe this to be a
red-herring, mainly since I don't need or access data on the converted db. I
may be wrong here. But still doesn't explain why it works perfectly on my
machine, but not on another? (Ref first post.)

Thanks for your continued interest in this problem. Any references or
suggestions are greatly appreciated.

Sorry, but that was the extent of my thoughts on how to
approach your situation.

Have you checked the other machine for installation
differences? A different version of a library or Jet are
the usual culprits when two machines exhibit different
behavior?

 

[RD]

Marsh,

Many thanks for the quick response, and input. Sorry to say, the FileCopy
method is not workable, because of the intent of "automation". I would have
created up to 48 pre-exisisting databases to support a simple FileCopy
method. This is simply not workable.

The essential problem is the conversion of the newly created db to a 2000
file format without any error boxes from msaccesses.exe.

The example code sets a 'dbs.workspace' but firmly believe this to be a
red-herring, mainly since I don't need or access data on the converted db. I
may be wrong here. But still doesn't explain why it works perfectly on my
machine, but not on another? (Ref first post.)

Thanks for your continued interest in this problem. Any references or
suggestions are greatly appreciated.

PMFJI.

Just a thought: I see you're getting the file path from your recordset (I'm
assuming based on a table). Is the path held as a mapped drive or a UNC path?
If it's to a mapped drive and your machine's drive mappings are different, that
could explain why it works on yours but not on others.

I recently had to provide a UNC path to a guy who had hard coded a mapped drive
in a save path for a Word document which had caused a similar problem.

HTH,
RD

 

[Guest]

Interesting idea, but the main user creates and keeps these new files in a
local directory. He could refer to a UNC, but just doesn't as matter of
practice.

The field it references in the recordset "INIADM" is the full path name on
the local drive. The only thing that really changes is the directory.

Thanks for you help.