[AccessD] Add-In Express 2009 for MS Office and .NET

Hi Max --

Thank you, I know about this "suffixing feature".

Thank you, I do not drink vodka as often as you tip off <g>

Once again, the first version of add-in is mainly a proof of concept of
Add-In Express feature of using of one custom MS Access Add-In setup for MS
Access 2000 - 2010 versions, as well as setting the structure of the VS
solution to make custom Add-In pluggable with new functionality plug-ins in
the new versions etc.
Therefore I do not take into that much account Access db objects
export/import features' implementations, and quality of coding of runtime
errors capturing and exceptions workarounds. Of course there will be some
bugs in this code but not vodka consumption related :)

Good prompt friendly humorous code review - what better attitude to their
coding work a developer might wish?

Have nice weekend.

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Saturday, February 13, 2010 6:19 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET

Hi Shamil,
Yes you are correct with the auto-replace for text objects,

BUT when you bring back tables it doesn't replace just creates  a new one
with an auto-incrementing suffix ie,  tblContacts, tblContacts1,
tblContacts2, etc.  needs to be  tested but  I think that is  right - will
test later when the  vodka has worn off <g>

Thanks

Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: 13 February 2010 15:12
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET

Hi Max --

Yes, but for add-in that check isn't needed.

The version of import code I posted also doesn't check existence of target
db objects - AFAIHF saved as text queries, forms, reports, macros and
modules do get replaced by .load from text.

Existence of target tables should be checked before importing their data
from saved xml - I will add that code to the add-in.

Thank you.

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max (MGA)
Sent: Saturday, February 13, 2010 5:08 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET

Hi Shamil,
I will do some testing later,  but on  the import code you need to ensure
that it doesn't try to import the same code module that it is actually
running, ie assuming the code is saved with the name as show here,  then

If getObjectNameFromFileName(strSourceFileName) <> "modObjectsImport" Then
' run code
Else
'  skip code	
Endif


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: 13 February 2010 13:35
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET

Hi Max --

Thank you for your note/code review.

OK, I will add error 2001 handling. Anything else to consider?

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Saturday, February 13, 2010 1:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Add-In Express 2009 for MS Office and .NET

Shamil,

In your Export Code below.

Error 2001 needs to be trapped and handled.  This will occur because your
code is parsing the MSys objects and if the object is  no  longer "LIVE" but
still shown in the MSys tables etc.  

.ALLFORMS will over come this I think.

Max


Option Compare Text
Option Explicit
On Error GoTo errhandler

Dim app As Access.Application
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTargetFolder As String
Dim strTargetFileName As String
Dim strObjectFileFullPath As String
Dim strObjectName As String

    Set app = Access.Application
    
    strTargetFolder = Access.Application.CurrentProject.path + "\Objects\"
        
    Set dbs = app.CurrentDb
    Set rst = dbs.OpenRecordset(exportedObjectsSql, dbOpenForwardOnly)
    While Not (rst.EOF)
        Select Case rst![AcObjectType].Value
        Case acTable:
             strTargetFileName = _
                    rst![ObjectTypeName].Value + "_" + _
                    rst![ObjectName].Value
             If (Left(rst![ObjectName].Value, 4) <> "MSys") Then
                app.ExportXML acTable, _
                 rst![ObjectName].Value, _
                 strTargetFileName + ".xml", _
                 strTargetFileName + "Schema.xml"
             Else
                On Error Resume Next
                app.ExportXML acTable, _
                 rst![ObjectName].Value, _
                 strTargetFolder + "MSysTables\" + strTargetFileName +
".xml", _
                 strTargetFolder + "MSysTables\" + strTargetFileName +
"Schema.xml"
                On Error GoTo errhandler
             End If
        Case acQuery, _
             acForm, _
             acReport, _
             acMacro, _
             acModule:
               strTargetFileName = rst![ObjectTypeName].Value + _
                    "_" + rst![ObjectName].Value + ".txt"
               app.SaveAsText _
                 rst![AcObjectType].Value, _
                 rst![ObjectName].Value, _
                 strTargetFolder + strTargetFileName
        Case Else
        End Select
        rst.MoveNext
    Wend
    rst.Close
    
    MsgBox ("Export DONE!")
exithere:
    Exit Sub
errhandler:
    MsgBox "Unhandled Error in sExportAllObjects(): " & Err.Number & vbCrLf
& Err.Description
    Resume exithere
    
End Sub

Private Property Get exportedObjectsSql()
Dim strSql As String
    strSql = _
 "SELECT " + _
 "  Switch( " + _
 "    [Type]=1,0, " + _
 "    [Type]=5,1, " + _
 "    [Type]=-32768,2, " + _
 "    [Type]=-32764,3, " + _
 "    [Type]=-32766,4, " + _
 "    [Type]=-32761,5) AS acObjectType, " + _
 "  Choose( " + _
 "   [acObjectType]+1,'Table','Query','Form','Report','Macro','Module') AS
ObjectTypeName, " + _
 "   MSysObjects.Name as ObjectName " + _
 " FROM MSysObjects " + _
 " WHERE " + _
 "   ((Not (Switch( " + _
 "
[Type]=1,0,[Type]=5,1,[Type]=-32768,2,[Type]=-32764,3,[Type]=-32766,4,[Type]
=-32761,5)) Is Null) AND ((MSysObjects.Name) Not Like '~*')) " + _
 " ORDER BY
Switch([Type]=1,0,[Type]=5,1,[Type]=-32768,2,[Type]=-32764,3,[Type]=-32766,4
,[Type]=-32761,5), MSysObjects.Name;"
    exportedObjectsSql = strSql
End Property

<<< snip >>