operation must use an updatable query 2

[ClydeData]

I am building a database viewer for customer, Customers will have their own cut down version of the database with their a copy of the data emailed to them

I want to email the data daily as a multi tab xls spreadsheet, to do this I wish to

1. use FileCopy to get blank version of spreadsheet
2. Use append queries to add data to each of the tabs in spreadsheet

With the append queries I get the error message “operation must use an updatable query”

Access will not allow you to append data to an xls spreadsheet Linked table.


Is their anyway round this

Thanks


Jimmy

 

[dhookom]

You will need to find another solution. Check Ken Snell's Working With EXCEL Workbook Files

Duane
Hook'D on Access
MS Access MVP

 

[ClydeData]

Duane
Thanks for that link
I'll check it out

One thing I think ill look at is sending an mdb file with all tables forms & queries as an email attachment.

I'm sure I can find the code somewhere on Tek Tips to zip an mdb ready for attachment to an outlook email

Thanks

Jimmy

 

[Golom]

You can use the Windows Shell to ZIP files

Private Sub cmdZipIt_Click()
    Dim ZipFile                     As String
    ZipFile = "c:\testzip\myMDB.zip"
    CreateEmptyZip ZipFile 

    With CreateObject("Shell.Application")

        ' Copy a file into the compressed folder.
        .NameSpace(ZipFile).CopyHere "c:\myFolder\myMDB.mdb"

    End With
    
End Sub

Public Sub CreateEmptyZip(sPath)
    Dim strZIPHeader                As String
    
    ' header required to convince Windows shell that this is really a zip file
    strZIPHeader = Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    
    With CreateObject("Scripting.FileSystemObject")
        .CreateTextFile(sPath).Write strZIPHeader
    End With

End Sub

 

[ClydeData]

Thanks Golom

That should work fine,

I can now attach the zipped mdb file to an outlook mail message in the usual way

Thanks

Jimmy