access error 3011

The exact error is Run-Time error '3011':
with on error goto err_handler commented out:
The Microsoft Jet database engine could not find the object ''.  Make sure the object .......

err.description is
3011 The Microsoft Jet database engine could not find the object ''.  Make sure the object exists and that you spell its name and the path name correctly. an error has occured in main  ( IT LOOKS LIKE TWO SINGLE QUOTES NOT ONE DOUBLE)

I am exporting an access table as a dbase IV table to update an existing dos application (I have lots to convert).  I have a case select statement exporting several tables.  I get this error on two of them.  I have stripped the code (I commented out most everthing I don't need and left basic functionality trying to find the null variable to no avail)  I left just the docd.transferdatabase line and set source, dest variables and it still dies on Case "Staff" and Case "Ticklers"  all other Case statements work fine.


I have a space in here somewhere I cannot find.

  Case "Ticklers"
                                Debug.Print " entering ticklers"
                'EXPORT TO G:\GO_SYS\TICKLERS.DBF
            source = "Ticklers"
            dest = "TICKLERS.DBF"
            Set td = db.TableDefs(source)
            'td.Fields("TotalBudgetDollars").name = "BUDGET"
            'td.Fields("Event_StartMonth").name = "START_MO"
            'td.Fields("Event_CompleteMonth").name = "COMP_MO"
            'str = "ALTER TABLE Ticklers DROP COLUMN [MEMO]"
             '  db.Execute str
            DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                    Debug.Print " Hit Ticklers"
                                Debug.Print "leaving ticklers"
                               

here is the entire sub proceedure

Public Sub main()
On Error GoTo err_handler
Dim td As TableDef
Dim str As String
Dim db As Database
            'make the vb code wait for the .bat file to complete file transfers
'Call ShellWait("G:\GO_SYS\BKUP_DBF.BAT", vbNormalNoFocus)
Set db = DBEngine(0).OpenDatabase(path & dbName)
'Debug.Print path & "dhhm.mdb"
Dim source, dest
Call removeRelationship(db)     'delete relationships from database
  For Each td In db.TableDefs
    Select Case td.name
        Case "Staff"
                    'CHANGE COLUMNS EmergcyContactPhone to PHONE, Delete NOTE:
                    'CHANGE WheresHeAt to WHERE
                source = "Staff"
                dest = "STAFF.DBF"
                Set td = db.TableDefs(source)
                Debug.Print td.name
                                                'drop the column
                str = "ALTER TABLE Staff DROP COLUMN [Note]"
                db.Execute str
                                                'rename columns
                td.Fields("WheresHeAt").name = "WHERE"
                td.Fields("EmrgcyContactPhone").name = "PHONE"
                  DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
               
                                    Debug.Print " leaving staff"
            Case "Clients1"
                                Debug.Print " entering clients"
                source = "Clients"
                dest = "CLIENTS.DBF"
                Set td = db.TableDefs("Clients")
                    'DELETE NOTE AND CHANGE LastUpdateDate to UPDATED
                    'EXPORT  TO G:\GO_SYS\CLIENTS.DBF
                                                    'drop the column
                    str = "ALTER TABLE Clients DROP COLUMN [Note]"
                    db.Execute str
                                                    'change column name
                    td.Fields("LastUpdateDate").name = "UPDATED"
                    str = ""
                    db.TableDefs.Refresh
                                                    'transfer the table to .dbf file
                    DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                Set td = Nothing
                                    Debug.Print "leaving clients"
               
            Case "Client Alternate Names1"
                                Debug.Print "entering client alt names"
                                'EXPORT TO G:\GO_SYS\CLNT_ALT.DBF
                source = "Client Alternate Names"
                dest = "CLNT_ALT.DBF"
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print "leaving client alt names"
             
            Case "Engagements1"
            Debug.Print "entering Engagem"
                    'EXPORT TO G:\GO_SYS\ENGMENT.DBF
                source = "Engagements"
                dest = "ENGMENT.DBF"
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print "leaving engagem"
               
              Case "Invoice Line Items1"
                                Debug.Print "entering inv line"
                    'EXPORT TO G:\GO_SYS\INV_LINE.DBF
                source = "Invoice Line Items"
                dest = "INV_LINE.DBF"
                Set td = db.TableDefs("Invoice Line Items")
                str = "ALTER TABLE [Invoice Line Items] DROP COLUMN [Memo]"
                db.Execute str
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print "leaving inv line"
               
            Case "Invoices1"
                                Debug.Print "entering Invoices"
                    'EXPORT TO INVOICES.DBF
                source = "Invoices"
                dest = "INVOICES.DBF"
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print " leaving Invoices"
           
           
            Case "Ticklers"
                                    Debug.Print " entering ticklers"
                    'EXPORT TO G:\GO_SYS\TICKLERS.DBF
                source = "Ticklers"
                dest = "TICKLERS.DBF"
                Set td = db.TableDefs(source)
                td.Fields("TotalBudgetDollars").name = "BUDGET"
                td.Fields("Event_StartMonth").name = "START_MO"
                td.Fields("Event_CompleteMonth").name = "COMP_MO"
                str = "ALTER TABLE Ticklers DROP COLUMN [MEMO]"
                   db.Execute str
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                        Debug.Print " Hit Ticklers"
                                    Debug.Print "leaving ticklers"
                                   
            Case "TimeSheets1"
                                    Debug.Print " entering timesheets"
                    'DELETE COLUMNS COMMENTS:
                    'EXPORT TO TIME_SHT.DBF
                source = "TimeSheets"
                dest = "TIME_SHT.DBF"
                Set td = db.TableDefs("Time Sheets")
                str = "ALTER TABLE Time Sheets DROP COLUMN [Comments]"
                db.Execute str
                str = ""
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print "leaving timesheets"
                               
            Case "Work Codes1"
                                Debug.Print "Entering work codes"
                    'DELETE LONG DESCRIPTION EXPORT TO WRK_CDS.DBF
                source = "Work Codes"
                dest = "WRK_CDS.DBF"
                Set td = db.TableDefs("Work Codes")
                 str = "ALTER TABLE [Work Codes] DROP COLUMN [Long Description]"
                db.Execute str
                DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest
                                Debug.Print " leaving workcodes"
    End Select
Next
exit_main:
   Exit Sub
err_handler:
        Debug.Print (Err.Number & " " & Err.Description & " an error has occured in main")
    Resume exit_main
End Sub
LVL 1
NWILSON4Asked:
Who is Participating?
 
pootle_flumpConnect With a Mentor Commented:
I am off home I'm afraid - the obvious question is:

Are you certain your it is working for the other tables?
Where does path & dbName come from? Unless they are public variables your code would defo fail here, p[robably with the error message you are receiving.
Although maybe unrelated, how come all the other case statements refer to TableName1 in the case statement and then TableName (without the 1) when instantiating the tabledef object?

I think the STOP method (or inserting a breakpoint ideally) is the best next step - we need to know the precise line that fails.
0
 
pootle_flumpCommented:
Hi

I never use variants unless I have to but I doubt it is this but could you try....

The one thing Staff and Ticklers (great table name BTW) have in common is that they are the only Case statements to use the source variable when setting the TableDef.

Try:
Dim Source as string

Also, does the line Debug.Print td.name execute for staff? Does Staff get printed to the Immediate Window? I suppose I am saying - do you know the precise line that fails? Do you know how to use the debugging tools (breakpoints, stepping through code etc)?
0
 
NWILSON4Author Commented:
Not my tables, lol.  I set the source variable to try to get around this error and yes  the debug.print td.name outputs Staff.  I checked and debugged every variable in this code and cannot find a null or an empty string anywhere.  It is coming from the database as a null, hence the jet database engine cannot find the object.

This one is tough.  If I cannot find a null anywhere where is the null coming from?  Though its probably an empty string not a null.  I had it as a string to begin with and changed to variant to see if data type was causing the error.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NWILSON4Author Commented:
Ok, When I run the code with db set to currentdb, no problem.  When I point it to the database from which I actually want to export tables, the error occurs.  
0
 
pootle_flumpCommented:
OK:

Try changing your error trap to:

exit_main:
   Exit Sub
err_handler:
        Debug.Print (Err.Number & " " & Err.Description & " an error has occured in main")
'Make sure you delete this next line once you have finished debugging. If you know how to set break points, set a breakpoint instead
STOP

Resume Next

'    Resume exit_main
End Sub

When the code errors it will break at STOP. Press F8 and it will move on a line. Executing Resume Next will resume on the line AFTER the error. So - press F8 a second time and make a note of which line it lands on.

Please post the line that has errored (i.e. the line above the line it resumes on). This is some really simple debugging. You could do a lot worse than googling VB Debugger and doing a bit of reading, in particular about setting breakpoints and stepping through and out of code.
0
 
pootle_flumpCommented:
Soz - just seen your last post. Thinking :-\
0
 
NWILSON4Author Commented:
done that....it dies on
 DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest

the value of source is "Staff" and the value of dest is "Staff.dbf" and yes I have confirmed that it does infact export all other tables properly.  

The tablename1, killed the select value (there is no xxx1 table) that way I would not tie up the computer for a long time exporting tables that I know export fine
(good question)

.I commented out the error handling to get the code to break on the error as a double check before I posted here.  This is the first time I have had to post a new question.  Usually, I can find similar problems and work from there...but keep em coming.  
FYI, my recent tests show if I copy this code into the database from which I want to export, the export is fine, only when calling another database and exporting its tables does this error occur.  I am thinking that the method I am using is not supported when the database is not the current database.  I may have to use ado and delete records from the target and write a sql append but that has its own problems in this environment.
0
 
NWILSON4Author Commented:
the path and dbname are constants set at the module level.  If it were simple debugging, I would not be here.  I have set the breakpoints and such, where it is dying does not make sense.  The values contained in the statement
DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest  all have the correct values, there are no empty strings here or nulls
0
 
NWILSON4Author Commented:
Ok, I have done some dumb things, but this one is about the best yet.  It was a dumbass error that had nothing to do to the code.  I was in the wrong database.   There are too many copies here that are named the same, but in slightly different locations.  I am charged with converting their dos systems using dbase IV to modern database system, eventually using sql, I can't wait to get rid of all the copies and link them all (which is what I am doing here.)

 I am giving everyone who participated in this thread equal points for the help.  

Note to pootle_flump

It doesnt matter how hard the question is, the person posting it is giving points, you'll do better if you do not insult that person's intelligence by suggesting they do things that they may have already done, as was the case with the debugger,  In this case it was a simple mistake on my part but talking it out does help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.