NWILSON4
asked on
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("TotalBudgetDol
'td.Fields("Event_StartMon
'td.Fields("Event_Complete
'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_
Set db = DBEngine(0).OpenDatabase(p
'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").na
td.Fields("EmrgcyContactPh
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"
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("TotalBudgetDoll
td.Fields("Event_StartMont
td.Fields("Event_CompleteM
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
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("TotalBudgetDol
'td.Fields("Event_StartMon
'td.Fields("Event_Complete
'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_
Set db = DBEngine(0).OpenDatabase(p
'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").na
td.Fields("EmrgcyContactPh
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"
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("TotalBudgetDoll
td.Fields("Event_StartMont
td.Fields("Event_CompleteM
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
ASKER
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.
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.
ASKER
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.
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.
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.
Soz - just seen your last post. Thinking :-\
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
DoCmd.TransferDatabase acExport, "dBase IV", path, acTable, source, dest all have the correct values, there are no empty strings here or nulls
ASKER
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.
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.
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)?