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

Hi Max --

Thank you for your remark.

As I noted earlier the first version of add-in will be just a Proof Of
Concept (POC) of using one .NET Add-in with all MS Access versions (2000 -
2010) IOW it doesn't matter that much would it use XML or text delimited
(CSV) or ... format for data backup...
I selected XML because it does save table schema while saving data...

As you can find I also used .SaveAsText for queries to keep saved query
design properties...

When POC is working OK (including "One-Click" Setup) then next versions can
change back-up format for data - and that could be even JSON or whatever
else portable but not that compact or very compact but not portable - Binary
-  representation one will find more convenient - there are many options in
.NET Framework...

--Shamil

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

Shamil,
That sound good but I should mention that XML is extremely slow for large
table as well as occupying large disk space, but the time taken is
exponentially longer that xls and for what purpose?  After all, if it is for
backup, xml offers not advantages but lots of dis-advantages.

Up to you though.



Max


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

Hi Max --

Thank you for code samples - I plan to make it a bit different for first
version - something like the code in P.S. of this message (Export), and
Import will be posted in the other message.

Thank you.

--
Shamil

P.S. MS Access DB Export code (watch line wraps):

Option Compare Text
Option Explicit

Public Sub ExportObjects()
'
' 1. Exports Queries, Forms, Reports, Macros, Modules as
'     text files into \Objects subfolder
'
' 2. Exports Tables as
'     xml files with schema into \Objects subfolder
'
' 3. Exports MSys* tables as
'     xml files with schema into \Objects\MSysTables subfolder
'

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




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

Section 3 - Import

That's the lot now.  No form or user interface. I hope it works ok, seem ok
on my test system but caveat emptor as  always.

<<< snip >>>