Why can't it find my objects?

[Stapes]

Hi

In the code given below, strDatabase is set to "C:\Documents and
Settings\stephen\Desktop
\Pocketbooks\Frontend\21Nov2007\Management Pocketbooks FE.mdb"

The program keeps crashing at the line marked ** in my code (below)
with the error : 3011 - The Microsoft Jet database could not find the
object 'Book Titles'.

OK - here is my code:


Public Sub ExportDatabaseObjects(strProject As String, strFilePath As
String)
On Error GoTo Err_ExportDatabaseObjects


Dim db As Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath


Set db = OpenDatabase(strDatabase)


sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do
not
forget the closing back slash! ie: C:\Temp\


For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation &
"Table_" & td.Name & ".txt", True **
End If
Next td


Set c = db.Containers("Forms")
For Each d In c.Documents
Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" &
d.Name & ".txt"
Next d


Set c = db.Containers("Reports")
For Each d In c.Documents
Application.SaveAsText acReport, d.Name, sExportLocation & "Report_"
&
d.Name & ".txt"
Next d


Set c = db.Containers("Scripts")
For Each d In c.Documents
Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" &
d.Name & ".txt"
Next d


Set c = db.Containers("Modules")
For Each d In c.Documents
Application.SaveAsText acModule, d.Name, sExportLocation & "Module_"
&
d.Name & ".txt"
Next d


For i = 0 To db.QueryDefs.Count - 1
Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation
& "Query_" & db.QueryDefs(i).Name & ".txt"
Next i


Set db = Nothing
Set c = Nothing


MsgBox "All database objects have been exported as a text file to " &
sExportLocation, vbInformation


Exit_ExportDatabaseObjects:
Exit Sub


Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description


Resume Exit_ExportDatabaseObjects

End Sub

 

[Arvin Meyer [MVP]]

Sometimes the error handling engine in VB/VBA gets confused and pops up an
error which doesn't make sense. In your section of code:

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation &
"Table_" & td.Name & ".txt", True **
End If
Next td

I've never seen anything like that. Instead, try writing the table names to
a text file:

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
Open "C:\Project\Tables.txt" For Append As #1

Print #1, td.Name

Close #1
End If
Next td

 

[Stapes]

Hi

In the code given below, strDatabase is set to "C:\Documents and
Settings\stephen\Desktop
\Pocketbooks\Frontend\21Nov2007\Management Pocketbooks FE.mdb"

The program keeps crashing at the line marked ** in my code (below)
with the error : 3011 - The Microsoft Jet database could not find the
object 'Book Titles'.

OK - here is my code:

Public Sub ExportDatabaseObjects(strProject As String, strFilePath As
String)
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath

Set db = OpenDatabase(strDatabase)

sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do
not
forget the closing back slash! ie: C:\Temp\

For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation &
"Table_" & td.Name & ".txt", True **
End If
Next td

Set c = db.Containers("Forms")
For Each d In c.Documents
Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" &
d.Name & ".txt"
Next d

Set c = db.Containers("Reports")
For Each d In c.Documents
Application.SaveAsText acReport, d.Name, sExportLocation & "Report_"
&
d.Name & ".txt"
Next d

Set c = db.Containers("Scripts")
For Each d In c.Documents
Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" &
d.Name & ".txt"
Next d

Set c = db.Containers("Modules")
For Each d In c.Documents
Application.SaveAsText acModule, d.Name, sExportLocation & "Module_"
&
d.Name & ".txt"
Next d

For i = 0 To db.QueryDefs.Count - 1
Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation
& "Query_" & db.QueryDefs(i).Name & ".txt"
Next i

Set db = Nothing
Set c = Nothing

MsgBox "All database objects have been exported as a text file to " &
sExportLocation, vbInformation

Exit_ExportDatabaseObjects:
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description

Resume Exit_ExportDatabaseObjects

End Sub

Thank you - my question has been answered elsewhere.

 

[Dirk Goldgar]

Hi

In the code given below, strDatabase is set to "C:\Documents and
Settings\stephen\Desktop
\Pocketbooks\Frontend\21Nov2007\Management Pocketbooks FE.mdb"

The program keeps crashing at the line marked ** in my code (below)
with the error : 3011 - The Microsoft Jet database could not find the
object 'Book Titles'.

OK - here is my code:


Public Sub ExportDatabaseObjects(strProject As String, strFilePath As
String)
On Error GoTo Err_ExportDatabaseObjects


Dim db As Database
Dim td As TableDef
Dim d As Document
Dim c As Container
Dim i As Integer
Dim sExportLocation As String
Dim strDatabase As String
strDatabase = strFilePath


Set db = OpenDatabase(strDatabase)


sExportLocation = "C:\AccessSourceControl\" & strProject & "\" 'Do
not
forget the closing back slash! ie: C:\Temp\


For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , td.Name, sExportLocation &
"Table_" & td.Name & ".txt", True **
End If
Next td

Am I right in concluding that you're trying to export objects from the
database "C:\Documents and
Settings\stephen\Desktop\Pocketbooks\Frontend\21Nov2007\Management
Pocketbooks FE.mdb", but that's not the database this code is running in?

DoCmd.TransferDatabase is going to operate on the current database of Access
application the code is running in, not on the database you've opened in the
"db" object. So unless the current database has the same objects in it as
(db) does, you're going to get an error -- and even if it does have the same
object names, they won't be the *same* objects.

You're probably going to have to open a second instance of Access and
automate that instance to export the objects from that other database.
Something like (air code):

'------ start of example air code ------
Dim appAccess As Access.Application
Dim ao As AccessObject

Set appAccess = New Access.Application

With appAccess
.OpenCurrentDatabase "C:\Documents and
Settings\stephen\Desktop\Pocketbooks\Frontend\21Nov2007\Management
Pocketbooks FE.mdb"

' Export tables from CurrentData
For Each ao In .CurrentData.AllTables
If Left(ao.Name, 4) <> "MSys" Then
.DoCmd.TransferText acExportDelim, , ao.Name, _
sExportLocation & "Table_" & ao.Name & ".txt", _
True
End If
Next ao

' Export Forms from CurrentProject
For Each ao In .CurrentProject.AllForms
.SaveAsText acForm, ao.Name, _
sExportLocation & "Form_" & ao.Name & ".txt"
Next ao

' et cetera ...

End With
'------ end of example air code ------

Note that the DoCmd and TransferText method calls above are prefixed with a
dot (.), qualifying them with the Access application object referred to by
appAccess, *not* the application in which this code is running.