Tips for ACCDE and ACCDR

[Sacapuce]

Here are a few problems I have come accross while trying to produce a ACCDR
(runtime) version of an ACCDE database with some solutions:

1- if you use the docmd.rename command to change a table name, it works in
the ACCDE version but somehow NOT in the ACCCDR runtime version. It just
seems to ignore it. Instead, I had to use a piece of code to do the rename:

Public Function RenameTable(ByVal TableName As String, ByVal ToName As
String) As Boolean

On Error GoTo Error_Handler
Dim db As Database
Dim tbldef As TableDefs
Dim tblTable As TableDef

'checks whether a table exists
Set db = CurrentDb()
Set tbldefs = db.TableDefs

RenameTable = False
For Each tblTable In tbldefs
If tblTable.Name = TableName Then
tblTable.Name = ToName
RenameTable = True
End If
Next
tbldefs.Refresh

db.Close
Set tbldef = Nothing
Set db = Nothing

Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume
End Function

2- Before producing an ACCDE from and ACCDB, make sure to use the
Debug/Compile action in the VBE menu bar and correct all errors in the code
as Access will not create an ACCDE with errors in the code.

3- in the same vein as point 1, the command IsObject() for checking the
existence of a table (or other) object will return an error message when no
object can be found (it should return 'false' according to the help file). A
way around it is to account for the error message in a public function (in
this case to check for the existence of a particular table):

Public Function CheckTable(ByVal TableName As String) As Boolean
On Error GoTo Error_Handler
Dim db As Database
Dim tbldef As TableDefs

'checks whether a table exists
Set db = CurrentDb()

If IsObject(db.TableDefs(TableName)) = True Then CheckTable = True

db.Close
Set db = Nothing

Exit_Procedure:
On Error Resume Next
DoCmd.Hourglass False
DoCmd.SetWarnings True
Exit Function
Error_Handler:
If Err.Number = 3265 Then 'it is not there
CheckTable = False
Else
DisplayUnexpectedError Err.Number, Err.Description
End If
Resume Exit_Procedure
Resume
End Function

It's all fun!

 

[Douglas J. Steele]

Why on earth would you need the ability to rename a table in a production
application?

 

[Sacapuce]

to create a backup for example when updating data

 

[Douglas J. Steele]

In that case, you should be making a copy of the back-end, or else backing
the table up to another database.

Your backup should not be in the same database: it's of far less use in that
case, since if the database gets corrupted, your backup is gone too.

 

[Sacapuce]

good point, thanks. I was backing up the data so that a data update could be
downloaded from a FTP site and imported into the database (requirement of the
client). The imported data could easily have errors or become corrupted
during the download process so it was important to back the existing data
table up, just in case.

 

[John W. Vinson]

good point, thanks. I was backing up the data so that a data update could be
downloaded from a FTP site and imported into the database (requirement of the
client). The imported data could easily have errors or become corrupted
during the download process so it was important to back the existing data
table up, just in case.

I'd suggest turning the logic around: use the CreateDatabase() method to
create a new "scratchpad" database; download the FTP data into *that*
database, not your production database; clean it up there; and after it's
correct, migrate it into the production database.

None of this should involve the .accde or .accdr database at all, however;
don't you have a split application with the .accde as a code-only frontend to
a separate backend database?

 

[Sacapuce]

many thanks for your advice, I will keep that in mind for my next job. I had
to produce 2 versions of the app. The first was a network one where I had
indeed a front end containing the code and forms etc and the back end with
user data. I also had a linked Excel spreadsheet with product data that was
updated regularly by the client (easy process). The second version was a
individual PC version with internet updates. In the end, they were not happy
with having the product data outside so I implemented a quick and easy import
procedure into the frontend. Not ideal but there was little time to do more.