dwsdes
asked on
Split db code error
I have the following code in an A2K db that deletes import error tables:
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As Database
Dim intCount As Integer, intPos As Integer
Set db = CurrentDb
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
DoCmd.DeleteObject acTable, db.TableDefs(intPos).Name
End If
Next intPos
Set db = Nothing
End Function
This worked fine until I split the database. I'm assuming the problem lies with the "Set db = CurrentDb" line, but I don't know the syntax to point the code to the backend db on the server.
Any ideas would be appreciated!
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As Database
Dim intCount As Integer, intPos As Integer
Set db = CurrentDb
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
DoCmd.DeleteObject acTable, db.TableDefs(intPos).Name
End If
Next intPos
Set db = Nothing
End Function
This worked fine until I split the database. I'm assuming the problem lies with the "Set db = CurrentDb" line, but I don't know the syntax to point the code to the backend db on the server.
Any ideas would be appreciated!
try this
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As Database, dbPath as string
dbPath= "c:\myfolder\db1.mdb" ' change to path to the db>
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
' DoCmd.DeleteObject acTable, db.TableDefs(intPos).Name
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As Database, dbPath as string
dbPath= "c:\myfolder\db1.mdb" ' change to path to the db>
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
' DoCmd.DeleteObject acTable, db.TableDefs(intPos).Name
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
ASKER
Yes, all tables are in the back end. After the import is complete, there are always a few error tables created. This code keeps them from adding up day after day.
Well, you cannot delete a linked table (in the BE) ... so, Cap's code should(?) do it.
Maybe the Import table could be in the Front end ?
mx
Maybe the Import table could be in the Front end ?
mx
ASKER
Thanks for the response. Tried the code change, returns "User-defined type not defined" and highlights the "Dim db As Dabase". I did the path to the backend as "\\sbsserver\splitmdb\test
Dim db As Database 'typo
mx
mx
In fact for good measure ...
Dim db as dao.database
Be sure you have a reference set to Microsoft DAO.nn in Tools>>References
mx
Dim db as dao.database
Be sure you have a reference set to Microsoft DAO.nn in Tools>>References
mx
ASKER
Sorry, I'll have to continue this tomorrow, business is closing for the day.
<I did the path to the backend as "\\sbsserver\splitmdb\test
No, it is better using the UNC "\\sbsserver\splitmdb\test
also, you have to add to your references Microsoft DAO x.x Object Library
x.x is the version number
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As DAO.Database, dbPath as string
dbPath= "\\sbsserver\splitmdb\test
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
No, it is better using the UNC "\\sbsserver\splitmdb\test
also, you have to add to your references Microsoft DAO x.x Object Library
x.x is the version number
Public Function DeleteImportErrors()
'Deletes import error tables created by import, leaves 1 table only.
Dim db As DAO.Database, dbPath as string
dbPath= "\\sbsserver\splitmdb\test
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
ASKER
Thanks for the input. Made the DAO changes & updated the references to include M$DAO3.51 Object Library. Now I get a 3343 run time error, unrecognized database format & hightlights the Set db = OpenDatabase(dbPath) line. I checked the UNC and it is correct. Here's the code as I have it now:
Public Function DeleteImportErrors()
'Deletes import error tables created by Stewarts import, leaves 1 table only.
Dim db As DAO.Database, dbPath As String
dbPath = "\\Sbsserver\SplitMDB\Test
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table[" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
I "inherited" this db, I'm beginning to think I should tear it apart and see if I can alter the import so it doesn't create emport error tables..
Public Function DeleteImportErrors()
'Deletes import error tables created by Stewarts import, leaves 1 table only.
Dim db As DAO.Database, dbPath As String
dbPath = "\\Sbsserver\SplitMDB\Test
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table[" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
I "inherited" this db, I'm beginning to think I should tear it apart and see if I can alter the import so it doesn't create emport error tables..
from an Access application, can you open the db "\\Sbsserver\SplitMDB\Test
ASKER
Yes, the db opens with no problems.
what version of access are you using?
ASKER
We have Access 2000 Vers. 9.0.8950 SP-3.
can you try this, see if it will run properly
create a copy of test.mdb in c:\
then try
Public Function DeleteImportErrors()
'Deletes import error tables created by Stewarts import, leaves 1 table only.
Dim db As DAO.Database, dbPath As String
dbPath = "c:\Test.mdb"
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
create a copy of test.mdb in c:\
then try
Public Function DeleteImportErrors()
'Deletes import error tables created by Stewarts import, leaves 1 table only.
Dim db As DAO.Database, dbPath As String
dbPath = "c:\Test.mdb"
Dim intCount As Integer, intPos As Integer
Set db = OpenDatabase(dbPath)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 1 Step -1
If InStr(db.TableDefs(intPos)
db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
End If
Next intPos
Set db = Nothing
End Function
ASKER
Tried it, now we're back to the compile failure "User-defined type not specified", highlighting the Dim db as DAO.Database, dbPath As String.
make sure you have microsoft dao x.x object library in your references
ASKER
Sorry, forgot to do that when I created the new db, but it still errors out on the path. Lets get back to basics to make sure I have this module set correctly. When I created the last "test".mdb , I created a TestFE.mdb and the test.mdb backend with just the tables, both on C:\. The module is in the front-end & runs from the OnDeactivate property of the main form so the error tables get deleted when people close out the main form. Make sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Don't know if my last message got thru. I have to get off this problem and deal with the stuff piling up behind me. I'll take the db home with me and see if I can eliminate the error table from being created in the first place. If not, I'll post a new question next week. Thank you very much for your time and effort!
mx