• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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).Name, "_ImportErrors") > 0 Then
            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!
0
dwsdes
Asked:
dwsdes
  • 9
  • 7
  • 4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The import table is in the back end?

mx
0
 
Rey Obrero (Capricorn1)Commented:
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).Name, "_ImportErrors") > 0 Then
   '         DoCmd.DeleteObject acTable, db.TableDefs(intPos).Name
            db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
        End If
    Next intPos
    Set db = Nothing
   
End Function
0
 
dwsdesAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
dwsdesAuthor Commented:
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.mdb". Possible I need to assign a drive letter to the server?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Dim db As Database   'typo

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
dwsdesAuthor Commented:
Sorry, I'll have to continue this tomorrow, business is closing for the day.
0
 
Rey Obrero (Capricorn1)Commented:
<I did the path to the backend as "\\sbsserver\splitmdb\test.mdb". Possible I need to assign a drive letter to the server? > 
No, it is better using the UNC  "\\sbsserver\splitmdb\test.mdb"

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.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).Name, "_ImportErrors") > 0 Then

            db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
        End If
    Next intPos
    Set db = Nothing
   
End Function


0
 
dwsdesAuthor Commented:
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.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).Name, "_ImportErrors") > 0 Then
        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..
0
 
Rey Obrero (Capricorn1)Commented:
from an Access application, can you open the db  "\\Sbsserver\SplitMDB\Test.mdb" ?
0
 
dwsdesAuthor Commented:
Yes, the db opens with no problems.
0
 
Rey Obrero (Capricorn1)Commented:
what version of access are you using?
0
 
dwsdesAuthor Commented:
We have Access 2000 Vers. 9.0.8950 SP-3.
0
 
Rey Obrero (Capricorn1)Commented:
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).Name, "_ImportErrors") > 0 Then
        db.Execute "drop table [" & db.TableDefs(intPos).Name & "]"
        End If
    Next intPos
    Set db = Nothing
   
End Function
0
 
dwsdesAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
make sure you have microsoft dao x.x object library in your references
0
 
dwsdesAuthor Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
<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?>

ok. but, so you don't have to keep opening and closing the main form, do the testing in the click of a command button.
0
 
dwsdesAuthor Commented:
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!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now