我支持一个VBA-Access程序(Office 365),它包含表、查询、表单、报告、模块和类模块,用于管理客户、患者和会计。每年年底,我以编程方式创建一个数据库accounts of <current year>
,其中只存储显示帐户所需的表、查询、报告、模块和类模块。
今年我不得不放弃我那台安装了Windows 7的好旧电脑,而换了一台安装了Windows 11的新电脑。唉!accounts of <current year>
数据库不再工作:它似乎缺少一些参考文献,但还有别的东西,我不知道是什么。
我使用以下代码创建数据库并从主数据库填充它:
fileName = CHEMIN_SAUVEGARDE_COMPTE & "FFcompte " & year & ".accdb"
Set appAccess = CreateObject("Access.Application")
appAccess.NewCurrentDatabase fileName
以前有人遇到过这个问题吗?或者是否有一种方法可以复制数据库并从中删除一些数据?
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àrlFF Sàrl donnéesFF Sàrl DataFF 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àrlFF Sàrl donnéesFF Sàrl DataFF 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"