troubles in Access database when switching from Windows 7 to Windows 11

Question

I support a VBA-Access program (Office 365) which contains tables, queries, forms, reports, modules and class modules to manage customers, patients, as well as accounting. At the end of each year I programmatically create a database accounts of <current year> in which I store only the tables, queries, reports, modules and class modules necessary to display the accounts.

This year I had to abandon my good old computer on Windows 7 for a new one on Windows 11. Alas! The accounts of <current year> database no longer works: it seems to be missing some references, but there is something else, I don't know what.

I use this code to create the database and populate it from the main database:

fileName = CHEMIN_SAUVEGARDE_COMPTE & "FFcompte " & year & ".accdb"
Set appAccess = CreateObject("Access.Application")
appAccess.NewCurrentDatabase fileName

Has anyone come across this problem before? or is there a method to copy a database and delete some data from it?

I tried different possibilities: I manually added to the new database the references of the main database, it does not work I used the application.CompactRepair method to copy the database but 2 databases remain linked — Sylvia Yenni, Dec 28 '22 at 20:56
Are you saying when you try and open an old database you get an error? You'll need to post that error. — Nick.Mc, Dec 28 '22 at 23:46
The easiest way to copy a database is just to copy the file. Then, you can delete data afterwards. Note that the database does need to be offline. For "online" copies, I've shared some code [here](https://stackoverflow.com/a/45718616/7296893) — Erik A, Dec 29 '22 at 10:00
How large does file get in one year? Do you really need to separate years? Is there a "method" - only whatever code you write. What do you mean by "2 databases remain linked"? Is this a split design? — June7, Jan 01 '23 at 01:16
The code shown doesn't work without changes. assuming CHEMIN... is not a variable then it needs to be delimited as a string: "CHEMIN_SAUVEGARDE_COMPTE" also, just in case, double check that year can be cast to string . then the code works and saves the new copy of the current database to the default folder — mazoula, Jan 01 '23 at 09:21

score 0 · Answer 1 · answered Jan 23 '23 at 10:57

with your answers, I have find a solution, thanks. I open a copy of the database that contains the basic tables (the program works on linked tables), I delete the unnecessary tables and data from the new accounting year, I add to it the necessary queries, reports and modules and I save this DB under the name CHEMIN_SAUVEGARDE_COMPTE FF Sàrl compte<exercice>.accdb

    ' create the db for  accounts of current year  
retCar = MsgBox("Create the db FF Sàrl compte" & exercice & " ?", vbOKCancel, _
            "Clôture de l'exercice comptable")
If retCar = vbOK Then

    gNomFichier = CHEMIN_SAUVEGARDE_COMPTE & "FF Sàrl compte " & exercice & ".accdb"
    
    ' Create Microsoft Access Workspace object.
    Set wrkAcc = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbCurrent = wrkAcc.OpenDatabase("C:\FF Sàrl\FF Sàrl données\FF Sàrl Data\FF Sàrl data - copie.accdb", True)
    ' delete useless tables
    With dbCurrent
        .TableDefs.Delete "Table des erreurs"
        .TableDefs.Delete "tblConversionApexDbf45"
        .TableDefs.Delete "tblDetailNoteHandylife"
        .TableDefs.Delete "tblExportPaiementPraxo"
        .TableDefs.Delete "tblExportPaiementPraxoCopy"
        .TableDefs.Delete "TblLocalite"
        .TableDefs.Delete "tblNoteHandylife"
        .TableDefs.Delete "tblPaiementOrphelin"
        .TableDefs.Delete "tblClientService"
        .TableDefs.Delete "tblEmployeEnfant"
        .TableDefs.Delete "tblNotePaiement"
        .TableDefs.Delete "tblOldNotePaiement"
        .TableDefs.Delete "tblPatientAdresseFact"
        .TableDefs.Delete "tblNotePoursuite"
        .TableDefs.Delete "tblPoursuite"
        .TableDefs.Delete "tblClientMiseEnCompte"
        .TableDefs.Delete "tblCompteBancaire"
        .TableDefs.Delete "tblDecompteSalaire"
        .TableDefs.Delete "tblDecompteVacances"
        .TableDefs.Delete "tblEnfant"
        .TableDefs.Delete "tblOfficeJudiciaire"
        .TableDefs.Delete "tblPaiement"
        .TableDefs.Delete "tblService"
        .TableDefs.Delete "tblNote"
        .TableDefs.Delete "tblOldNote"
        .TableDefs.Delete "tblClientOldPC"
        .TableDefs.Delete "tblClientPieceComptable"
        .TableDefs.Delete "tblOldPaiement"
        .TableDefs.Delete "tblAdresseFact"
        .TableDefs.Delete "tblPatient"
        .TableDefs.Delete "tblEmploye"
        
        ' in tblEcriture and tblDetailEcriture, delete dta of the new year 
        qryStr = "SELECT distinct tblEcriture.detailRef FROM tblEcriture where tblEcriture.[date Ecriture] > " & FF_dateSQL(dateCloture)
        Set qryDef = .CreateQueryDef("", qryStr)
        Set recTemp = qryDef.OpenRecordset()
        If Not recTemp.EOF Then
            recTemp.MoveLast
            recTemp.MoveFirst
            Set tbldef = .TableDefs("tblDetailEcriture")
            qryStr = "SELECT tblDetailEcriture.* FROM tblDetailEcriture"
            Set qdfTemp = .CreateQueryDef("", qryStr)
            Set recCompte = qdfTemp.OpenRecordset()
            recCompte.MoveLast
            recCompte.MoveFirst
            
            Do While Not recTemp.EOF
                recCompte.FindFirst "[no Detail Ecriture] = " & recTemp!detailref
                If Not recCompte.NoMatch Then
                    recCompte.Delete
                    recCompte.Requery
                End If
                recTemp.MoveNext
            Loop
        End If
        
        ' in tbldecompte, delete data of the new year
        qryStr = "SELECT tbldecompte.* FROM tbldecompte where tblDecompte.[date conf] > " & FF_dateSQL(dateCloture)
        Set qryDef = .CreateQueryDef("", qryStr)
        Set recTemp = qryDef.OpenRecordset()
        If Not recTemp.EOF Then
            recTemp.MoveLast
            recTemp.MoveFirst
            Do While Not recTemp.EOF
                recTemp.Delete
                recTemp.MoveNext
            Loop
        End If
                    
    End With
    
    dbCurrent.Close
    wrkAcc.Close
    
    FileCopy "C:\FF Sàrl\FF Sàrl données\FF Sàrl Data\FF Sàrl data - copie.accdb", gNomFichier

    ' copy account queries in the new db
    For Each qryDef In CurrentDb.QueryDefs
        Select Case qryDef.Name
            Case "reqActif", "reqPassif", "reqActifPassif", _
                    "reqBeneficePerteReporte", "reqBilan", "reqC51", _
                    "reqC51Stat", "reqC51TVA", "reqCategorie", "reqCompte", _
                    "reqCompteComplet", "reqCompteDA", "reqCompteEcriture", _
                    "reqCompteSommeDA", "reqCompteVide", "reqDecompte", "reqDivision", _
                    "reqEcriture", "reqFactureExt", "reqGroupe", "reqLstComptes", _
                    "reqNouveauCapital", "reqResultatAvantImpot", _
                    "reqResultatAvantImpotOld", "reqResultatComptable", _
                    "reqResultatComptableOld", "reqResultatExp", _
                    "reqResultatExploitation", "reqResultatExploitationOld", _
                    "reqResultatHorsExp", "reqResultatOrdinaire", _
                    "reqResultatOrdinaireOld", "reqStatistiquesAnnuelles", _
                    "reqStatistiquesMensuelles", "reqStatistiquesParClient", _
                    "reqStatistiquesSemestrielles", "reqStatTotales"
                DoCmd.CopyObject gNomFichier, , acQuery, qryDef.Name
        End Select
    Next qryDef
    
    ' copy account reports in the new db
    For Each obj In Application.CurrentProject.AllReports
        Select Case obj.Name
            Case "rptBilanAnnuel", "rptBilanProvisoire", "rptCloture", _
                    "rptComptes", "rptCompteVide", "rptPlanComptable", "rptResultat", _
                    "rptResultatHorsExploitation", _
                    "rptStatistiquesAnnuelles", "rptStatistiquesMensuelles", _
                    "rptStatistiquesParClient", "rptStatistiquesSemestrielles", _
                    "subRptStatistiquesMensuelles", "subRptStatistiquesParClient", _
                    "subRptStatistiquesSemestrielles"
                DoCmd.CopyObject gNomFichier, , acReport, obj.Name
        End Select
    Next obj
    
    ' copy classes 'ValeursFF', 'Compte' et 'client' and module 'UtilitaireCompte' in the new db
    DoCmd.CopyObject gNomFichier, , acModule, "ValeursFF"
    DoCmd.CopyObject gNomFichier, , acModule, "Compte"
    DoCmd.CopyObject gNomFichier, , acModule, "Client"
    DoCmd.CopyObject gNomFichier, , acModule, "UtilitaireCompte"

troubles in Access database when switching from Windows 7 to Windows 11

1 Answers1