There seems to be a lot of code missing.
But, from what is posted, this is what I see:
You have two variables, "Year" and "Table" that are reserved words.
"Year" is a function in VBA.
"Table" is a property: it indicates the name of a Relation object's primary table.
It is a really bad idea to use reserved words as object names.
Maybe use "arrYear" and "tblTable" or "tblName" or "TableName"
----------------------------------------------------
You say:
in Excel...open a file from 2013 and delete every row that that number is in, then open 2012, ... , through 2005, then take the next number in the list and open 2013, then 2012, ..., 2005 etc through the list.
But the code is deleting all of the numbers from one database before moving to the next database.
Look at this snippet of code for the Delete routine (I've done some formatting):
Code:
For n = 1 To UBound(Year)
For k = 1 To UBound(arrDelete)
strDBName = Year(n) & "databasefile.accdb"
appAccess.OpenCurrentDatabase (strPathDB & strDBName)
appAccess.CloseCurrentDatabase
Next k
Next n
What you say (want) and what the code does is different.
If the array "arrDelete" has 100 elements, how many times is one database opened and closed before it moves to the next database?
(A: 100 times. That can really slow down the code)
This is how I would have the loops:
Code:
For n = 1 To UBound(Year)
strDBName = Year(n) & "databasefile.accdb"
appAccess.OpenCurrentDatabase (strPathDB & strDBName)
For k = 1 To UBound(arrDelete)
Next k
appAccess.CloseCurrentDatabase
Next n
This is the string I would use:
Code:
DeleteStatement = "DELETE * FROM " & Table & " WHERE numberfromlist = " & arrDelete(k)
I don't know where the variable "Table" is declared or filled.
If the same table is in every database, why not use the table name instead of a variable?
-------------------------------------------------------
In the update code, the same logic would/should apply.
For the change string, I would try:
Code:
ChangeStatement = "UPDATE " & Table & " SET Pnumberfromlist = " & arrChangeTo(k) & " WHERE NumberFromList = " & arrChangeFrom(k)
Found an error in the update code
You have
For k = 1 To UBound(arrDelete)
I think it should be
For k = 1 To UBound(arrChangeFrom)
-------------------------------------------------------
All this line
Code:
appAccess.DoCmd.OpenModule "DoCmdSQL", "PerformSQL"
does is open the IDE, opens the module in design view and locate the subroutine. The line can be removed/commented out.
-------------------------------------------------------
I would try changing
Code:
appAccess.Run "PerformSQL", DeleteStatement
to
Code:
appAccess.Execute DeleteStatement, dbFailOnError
(Don't have to have another sub......)
-------------------------------------------------------
My $0.02 .....