?
Solved

access error  3011

Posted on 2005-04-11
9
Medium Priority
?
4,346 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:NWILSON4
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:pootle_flump
ID: 13761164
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
 
LVL 1

Author Comment

by:NWILSON4
ID: 13762313
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
 
LVL 1

Author Comment

by:NWILSON4
ID: 13763357
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 11

Expert Comment

by:pootle_flump
ID: 13763518
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
 
LVL 11

Expert Comment

by:pootle_flump
ID: 13763530
Soz - just seen your last post. Thinking :-\
0
 
LVL 11

Accepted Solution

by:
pootle_flump earned 1500 total points
ID: 13763687
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
 
LVL 1

Author Comment

by:NWILSON4
ID: 13765007
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
 
LVL 1

Author Comment

by:NWILSON4
ID: 13765030
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
 
LVL 1

Author Comment

by:NWILSON4
ID: 13765343
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question